Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm not very good with Excel at all and I haven't found a specific answer for this. I'm looking for a simple formula to retrieve the max value in a particular column. I need it to begin searching from row 8, so for example =MAX(A8:"?"). I thought it would be * or something like that but it doesn't appear to be. I don't know what the last row is going to be because it will vary. How do I specify the 2nd variable in the formula to be the last row in the column without knowing beforehand?

share|improve this question

migrated from Dec 7 '12 at 14:28

This question came from our site for professional and enthusiast programmers.

This solution locates the value of the last cell in the column which is partly what I need but I'm not quite sure how to dissect it to get just the last non-empty cell location within the column. –  user1066133 Dec 7 '12 at 12:35

4 Answers 4

Try creating a "Dynamic named range". If you have continuous numeric data from A8 downwards then that can be defined as


[Excel will add in sheet names]

If you name that range Data then you can simply use


The DNR may also come in useful for other calculations

In Excel 2007 or later versions You could also convert your data in to a table and then you can refer to a specific column of the table which will increase in size as the size of the table increases.

Some information on Dynamic named ranges here:

share|improve this answer

Similar to Barry's solution, but a little cleaner (in my opinion).

share|improve this answer

It's ugly, but I'd use: =MAX(A8:A1048576)

share|improve this answer
Haha yeah I'm looking for ways to avoid this. I didn't think it'd be such a hassle to do something like this with such and advanced application. If I can't find a better solution I'll have to go with this. –  user1066133 Dec 7 '12 at 12:32

Simple way in Excel:


Creating UDF in VBA. This allows for a dynamic offset and will sum the column it is placed in:

Function SumColumn(offset As Double)

    Dim Data As Range

    Set Data = Application.Caller.EntireColumn.Resize(Application.Caller.EntireColumn.Rows.Count - offset, 1).offset(offset, 0)

    SumColumn = Application.WorksheetFunction.Sum(Data)

End Function
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.