Formula to Get Value of Last Non-Empty Cell in Excel

Add to Favorites
Author: don | Edits: don

Formulas that you can use to get the value of the last non-empty cell in a range in Excel. These examples include simple formulas and more robust complex formulas that allow you to do things like account for errors, blank cells, get the last number, and return values that begin with certain text.

c9a9d607f8aa2bbb80bafdb8cbfd0fa8.png

Sections:

Simple Formula to Get Value of Last Non-Empty Cell

Most Robust Formula to Get Value of Last Non-Empty Cell

Notes

Simple Formula to Get Value of Last Non-Empty Cell

For this to work, there must be no blanks in the list of values; if that's an issue for you, look to the next section for the robust version of the formula.

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

A:A represents column A, the column with the data. If you have a large data set, just use the first column of data as the reference.

e1ca0bf5a0156188fc5c6ce482265149.png

Result:

466f7fce91cdfcd66e1c7596bbf3248f.png

When to Use This Formula

When there are no blank cells or rows in the data set. This formula would not work correctly if we used it for the data in Column C.

The reason that this formula is included is because it is easier to remember and input and it will work for many data sets, especially data sets that are imported and that will never have blanks.

However, the next formula is more robust and will work even when your data set isn't perfect and contains blanks.

Most Robust Formula to Get Value of Last Non-Empty Cell

This works even if there are empty rows in the data set; however, it can be a bit confusing.

=LOOKUP(2,1/(C1:C50<>""),C1:C50)

46ec07561e3f61908a69c64729ff3f07.png

This formula gets the value of the last non-empty cell from column C within the range of C1 to C50.

C1:C50 is the range in which your data set will appear. You can only use one column for reference in this formula, so do not include the reference of the entire range for a large table, just a single column. Also, both range references in the formula must be exactly the same.

When using this for your data, do not include the entire column reference like C:C because it will make the formula run slowly as it will have to cycle through every cell in the column, regardless of it it contains data or not; simply use a range that is large enough to always encompass your data set.

Formula Explanation

This is a confusing formula and, honestly, you don't need to read this section, just copy/paste the above formula to your worksheet, change the range references and use the result.

  1. C1:C50<>"" checks the range C1:C50 for empty cell or not and returns a list of TRUE/FALSE values for the cells in range C1:C50. TRUE if the cell is not empty and FALSE if it is.
  2. 1/ does the task of dividing 1 by the TRUE/FALSE values from step 1. TRUE is the same as 1 and FALSE is the same as 0, so it returns either 1 or an error, #DIV/0!, which is caused because you can't divide by zero. The entire list of 1's and errors is kept in the LOOKUP function, where it is evaluated in step 3.
  3. 2 is the value that the LOOKUP function tries to find in the list of values created in step 2. Since it can't find the number 2, it looks for the next highest value, which is 1, and it looks for this value starting from the end of the list and going to the beginning of this list. It stops when it hits the first result, which will be the last cell in the range that has a value in it, which was turned into a 1 in step 2.
  4. C1:C50 is the last argument of the LOOKUP function and it causes the value of the cell to be returned instead of the value gotten from step 2.

Account for Errors

If the last cell in the range outputs an error and you want that to appear when it happens, use this version of the above formula:

=LOOKUP(2,1/(NOT(ISBLANK(C1:C50))),C1:C50)

C1:C50<>"" was replaced with NOT(ISBLANK(C1:C50))

bd37f8a1cd1d1e7488c57d6bf3fe6647.png

Result:

cc7e9b89c16037b1a688eba97baf3a9f.png

Last Numeric Value

Get the last value in the list that is a number.

=LOOKUP(2,1/(ISNUMBER(C1:C50)),C1:C50)

C1:C50<>"" was replaced with ISNUMBER(C1:C50)

This checks if the value in the cell is a number or not and uses that to create the TRUE/FALSE values.

Last Value that Begins with Some Value

=LOOKUP(2,1/(LEFT(C1:C50,1)="g"),C1:C50)

C1:C50<>"" was replaced with LEFT(C1:C50,1)="g"

This checks if the value in the cell starts with the letter g or not and uses that to create the TRUE/FALSE values.

Make Your Own

As you can see, you can use the LOOKUP function to create a rather robust series of tests to get the last value from a list so long as that value meets certain criteria.

In the above examples, we used the ISNUMBER, LEFT, NOT, & ISBLANK functions to show you some of the variations that you can make, but you are not limited to these; play around with the function and see what kinds of results you can get, it can make your data reporting a lot more useful.

Notes

These formulas are not array formulas!

You can use the entire column range reference if you want, but it can really slow down the performance of your spreadsheet, so be careful with that; usually, it's best to use a range that is sufficiently large enough to encompass the entirety of your data, now and into the future, but not the entire column.

Make sure to download the attached spreadsheet so you can view these examples in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
Macro: Extract whole words from a cell or sentence in Excel with this UDF. This allows you to spe...
Formula to Count Occurrences of a Word in a Cell or Range in Excel
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Find the Last Column with Data in Excel VBA
Tutorial: How to find the last column in a row that has data. This includes selecting that column or...