Skip to main content

How to Return the Last Cell Value of a Column Using Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

Returning the last cell value from a column or list can assist in applications that require the last know entered value in a series. For instance, with this data, I can use a formula to find the row number of the last entered value.

Returning the last cell value from a column or list can assist in applications that require the last know entered value in a series. For instance, with this data, I can use a formula to find the row number of the last entered value.

I recently created a document that calculates a head count delta for each quarter of a workday in manufacturing. I needed to know the most recent delta for another calculation.

The example shows how I collect the most recent delta figure from the bottom of a column where information populates from the top down from quarter to quarter. Using the COUNT function within the INDEX function causes the index function to report the last value entered.

The formula used to generate the last delta in the cell that it resides in would be:

=INDEX(H4:H7,COUNT(H4:H7))

After the formula is entered, the last cell within the range will be the result displayed. As you can clearly see in the result, the formula displays the last value (-2) of the range H4:H7 representing the delta for the latest quarter.

how-to-return-the-last-cell-value-of-column-using-excel
Scroll to Continue

The formula will work in the same manner with a horizontal range displaying the last entered value from left to right. See the next illustration below for an example.

how-to-return-the-last-cell-value-of-column-using-excel

If there are not any values in the range that the formula is checking, an error will occur. Like what is shown below in the last illustration. If you would like to prevent errors from occurring you can utilize the IFERROR function.

=IFERROR(INDEX(G23:J23,COUNT(G23:J23)),"")

If the above formula is used the IFERROR function will display no data represented by the quotes that do not display any text in between them. You also may elect to add a text string in the quotes that says "No Data".

how-to-return-the-last-cell-value-of-column-using-excel

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder

Related Articles