Microsoft Excel Functions & Formulas

Use the MAX, IF, and COLUMN functions to

determine the last used column in a range

Now let’s determine the last used column in a defined range by

using an array formula. All columns in the range A1:X10 have to be

checked and the last used column is then shaded automatically.

Here we use the MAX, IF, and COLUMN functions in an array for-

mula and combine them with conditional formatting.

4

To determine the last used column in a range:

1. Select cells A1:D10 and enter any numbers.

2. Select cell B12 and type the following array formula:

=MAX(IF(A1:X10<>"",COLUMN(A1:X10))).

3. Press <Ctrl+Shift+Enter>.

4. Select cells A1:X10.

5. On the Format menu, click Conditional ...