How to get the last value in a column in Excel
Over a certain period of time, a record is kept of the quantity of goods sold in a store. It's necessary to regularly monitor the last item issued from the store. To simply view the last value in a column, it's enough to move the cursor to any cell in the column and press the keyboard shortcut CTRL + down arrow (↓). However, more often than not, users need to perform various calculations in Excel with the last value in a column. Therefore, it's better to retrieve it as a value for a separate cell.
Finding the Last Value in an Excel Column
A schematic record of goods issued from the store:
To be able to constantly observe the latest registered item, enter the formula below into cell E2:
=INDEX(B:B, COUNTA(B:B))
Result of the formula for getting the last value:
Explanation of how the formula works to find the last value in the column:
The main role is played by the =INDEX() function, which should return the contents of a cell where a specific row and column intersect. The first argument of the INDEX function is an immutable constant, namely a reference to an entire column (B:B). The second argument contains the row number with the last filled value in column B. To find this row number, the COUNTA function is used, which returns the number of non-empty cells in a range. Correspondingly, this number is equal to the row number of the last non-empty row in column B and is used as the second argument for the INDEX function, which immediately returns the last value in column B to cell E2.
If you're trying to get the last value in a cell range returned by Excel formulas, then you should use a slightly modified formula:
=INDEX(A:C,COUNT(C:C)+1,2)
Now, instead of the COUNTA() function, we use the regular COUNT() function to get the number of numbers in the range of the third column. Then, based on this data, we return the last value from the second column using the INDEX function.
DownloadAttention! All entries in column B should be contiguous (without empty cells before the last value).
It's worth noting that this formula is dynamic. When adding new entries to column B, the result in cell E2 will be automatically updated.
