Sometimes when working in a large spreadsheet you may want to know the last value in a column without having to scroll all the way to the bottom of the sheet.
With a little know-how, there are a couple of formulas you can use to do this in Google Sheets.
In this tutorial, I will show you how to get the last value in a column in Google Sheets.
Get Last Value with INDEX and COUNTA
One way that we can get the last value in a column is with a combination of the INDEX and COUNTA functions.
The INDEX function returns the contents of a cell at a specified location.
The COUNTA function counts the number of values in a dataset.
So when you combine them, you can count the number of cells with contents, and then use that count inside the index function to return the last cell in a column with a value.
The syntax to combine INDEX and COUNTA to return the last value is:
=INDEX(range,COUNTA(range))
- range – this is the column or range of data that you want to return the last value for
Here is an example of how this can be used in a spreadsheet:
The drawback of this method is that it only works if your column has no blank entries in it. If there is a cell that is blank, the COUNTA will not count that value, so your index will be off.
You can see in the example above, the last value pulled is not correct anymore because of the blank cell.
We can solve this by making a couple of changes to our formula and using the FILTER function.
We can create a filter to ignore blank cells in our formula.
Here is the syntax of how this would work:
=INDEX(FILTER(range,range<>””), COUNTA(FILTER(range,range<>””)))
In this formula, we have set a FILTER function inside our INDEX and COUNTA functions that will remove blank cells. The formula does this by only returning values that are not equal to blank.
That is what the section of the formula range<>”” does. It uses the does not equal comparison operator so the data used in both the INDEX and COUNTA formulas will only be cells not equal to blank.
If you want to learn more about this comparison operator you can read our other post on using does not equal in Google Sheets.
Here is how the final formula would look in our spreadsheet:
You can see now that the blank cell is ignored and the function works properly to return the last value.
This formula also works to get the last value in a row by switching out the range to a row instead of a column.
Here is an example of this:
Closing Thoughts
There are definitely other methods to return the last value from different columns and rows in your spreadsheet, but this is our favorite option.
With other options, you can create a formula to return the last text or numeric value while ignoring empty cells.
We like this option because it works on any type of value and you have the option to ignore blank cells by using the FILTER function in your formula as well.
More Google Sheets Tutorials:
How to Use INDEX and MATCH Together
How to Use the FILTER Function
How to Get the Column Letter