Microsoft

Return the last item in an Excel column

Use this Excel formula to easily return the last item from any list.

If you work with chronological lists or daily updates, you might also track the last item in a constantly changing list of values. Fortunately, you can combine Excel's INDEX() and COUNTA() functions to return the last value in a list by using the following expression:

=INDEX(column, COUNTA(column), 1)

For instance, to return the last value in column A, you'd use the expression

=INDEX(A:A, COUNTA(A:A), 1)

As you update the list, the formula automatically compensates.

october2008officeblog7fig1r.jpg

The one thing this formula can't handle is an empty cell. For instance, the formula for column B (cell G3) returns 0 when it encounters the empty cell (at cell C9).

october2008officeblog7fig2r.jpg

However, if you enter 0, instead of leaving the cell blank, the formula returns the correct value, 0.073.

october2008officeblog7fig3r.jpg

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks