How to find the maximum value in a dynamic Excel column?

To find the maximum value in a range is easy for most Excel users, but do you know how to find the maximum value in a dynamic column? In other words, the maximum value will change as the values changed or deleted or added in the column. Now I introduce some formulas to handle this job in Excel.

Find maximum value in a dynamic column

Find maximum value in a dynamic column

Here are two formulas that can help you to find the maximum value in a dynamic column in two different cases.

If you want to place the maximum value in another column, you can do as this:

Select a blank cell you will place the maximum value in, says Cell M1, and type this formula =MAX(INDEX(L:L,3):INDEX(L:L,MATCH(99^99,L:L,1))), L is the Column letter you use, press Enter key to get the result.

If you want to place the maximum under a column header and above the data value as below screenshot shown, you can do as this:

Select the cell you want to put the maximum value, type this formula =MAX(OFFSET(B1,2,0,1000)), and press Enter key. See screenshot:

Note: B1 is the cell above the formula cell, 2 means to find maximum value from the second cell below header cell to the end if you want to find maximum value from the third cell below to end, change 2 to 3. See screenshot:

Relative Articles:

• How to find the max length in an Excel column?