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.

Simple Formula to Get Value of Last Non-Empty Cell

Most Robust 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.

Result:

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.

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)`

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.

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.

**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.**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.**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.**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.

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))**

Result:

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.

`=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.

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.

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.

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...

Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...

LARGE Function - Get the Highest Values from a List in Excel

Tutorial: Return the largest value from a list, or any of the top values, in Excel. This method use...

Tutorial: Return the largest value from a list, or any of the top values, in Excel. This method use...

SMALL Function - Get the Lowest Values from a List in Excel

Tutorial: Return the smallest values from a list, or any of the smallest values, in Excel. This inc...

Tutorial: Return the smallest values from a list, or any of the smallest values, in Excel. This inc...

Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF

Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...

Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...

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...

Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...

Formulas to Remove First or Last Character from a Cell in Excel

Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...

Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...