Get the Last Nonblank Cell in An Excel Range

By Neil J. Rubenking

I have an Excel spreadsheet containing several instances of information for each month in a two-year period. In each case I need a formula to display the contents of the last nonblank cell in the two-year range. I tried using the IF function, but you can only nest seven IF functions. I tried other formulas without success. What formula can I use to display the most recent entry—that is, the last nonblank cell?

First, let’s work up a formula that will yield the row number of the last nonblank cell. We’ll assume labels in cells A2:A25 and values in B2:B25, with the formula for the most recent entry in B26. In that cell, type

=MAX(IF(NOT(ISBLANK (B2:B25)), ROW(B2:B25),0))

Instead of pressing Enter, press Ctrl-Shift-Enter to create an array formula. Excel processes each element of the array B2:B25 in turn, returning the row number for nonblank elements and a zero for blank elements. The MAX() function returns the maximum of these results, which is the last nonblank row.

To get the contents of that cell, we’ll use the OFFSET function. OFFSET returns the contents of a cell that’s a specific number of rows and columns away from the starting cell. To convert the row number found with the first formula into an offset, simply subtract the row number of the starting cell. Don’t delete the first formula; insert OFFSET(B2, at the beginning and add -ROW(B2),0) at the end. That yields this new formula, which you must again finish using Ctrl-Shift-Enter.

=OFFSET(B2, MAX(IF(NOT (ISBLANK(B2:B25)), ROW(B2:B25),0))-ROW(B2),0)

You can copy this formula to the cell just below each of your other two-year ranges. Note that the same technique works even if the data has some gaps.