Microsoft Office Tutorials and References

In Depth Information

**Returning the Last Nonblank Cell in a Column or Row**

=INDEX(C1:C500,MAX(ROW(C1:C500)*(C1:C500<>””)))

Press Ctrl+Shift+Enter (not just Enter) to enter an array formula.

You can, of course, modify the formula to work with a column other than column C. To use a

different column, change the four column references from C to whatever column you need. If the

last nonempty cell occurs in a row beyond row 500, you need to change the two instances of

500 to a larger number. The fewer rows referenced in the formula, the faster the calculation

speed.

The following array formula is similar to the previous formula, but it returns the last nonempty

cell in a row (in this case, row 1):

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>””)))

To use this formula for a different row, change the three 1:1 row references to correspond to the

correct row number.