How to Find Last Cell with Value in Column in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

We use Excel for our official and business purposes. For those purposes, we use a big amount of data. Sometimes we need to find the last cell with a value in a column. It seems tiresome to check the whole column and find that manually. So, in this article, we will discuss 3 quick methods on how to find the last cell with value in a column in Excel.

Here we will discuss 3 methods to find the last cell with values in the column. The first and last methods have some sub-sections too. Because a function may be used in different ways. We will add a column named Value to show the result.

This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Table of Contents hide

1. Inserting LOOKUP Function to Find Last Cell with Value in Column in Excel

1.1 Using Basic LOOKUP Function Only

1.2 Combining LOOKUP, NOT, and ISBLANK Functions to Find Last Cell with Value in Column

1.3 Applying LOOKUP and ISNUMBER Functions to Find Last Cell with Numeric Value in Column

1.4 Using LOOKUP with ROW Function to Find Row Where Last Value Exists

2. Finding Last Cell with Numeric Value in Column Using Excel INDEX and COUNT Functions

3. Applying OFFSET Function to Find Last Cell with Value in Column in Excel

3.1 Using Basic OFFSET Function

3.2 Using OFFSET and COUNT Functions to Find Last Cell with Value in Column in Excel

Conclusion

1. Inserting LOOKUP Function to Find Last Cell with Value in Column in Excel

Here we will use the LOOKUP function to find the last cell with value in Excel. We will combine this function with other functions. 1st we will explain the basic LOOKUP function, then add the other functions.

1.1 Using Basic LOOKUP Function Only

Here we will use the basic LOOKUP function. This function looks up values from a range of columns. Here we will check the whole Column C.

Steps:

First, go to Cell D5.
Write the LOOKUP function here. We took the range C:C, because we want to find out from the whole Column C. We may also set a certain range. So, our formula becomes:

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

Now, press ENTER and we will get a result.
Here, we get the last value of Column C.

Note:

C: C<>”” – It checks the whole Column C for empty cells and returns TRUE/FALSE for each cell of that range. If the cell is not empty then return TRUE otherwise, show FALSE. We can customize the cell range as per our needs.
1/ – It performs a division operation. Here, 1 will be divided the value from the previous step, which may be TRUE or FALSE. If TRUE result will be 1 and for FALSE that will be 0. It produces 1 when TRUE otherwise, an error, #DIV/0! because we can’t divide any number by zero.
2 – The LOOKUP function attempts to locate 2 in the list of values produced in the last step. Since it can’t locate the number 2, it looks for the next maximum value, which is 1. It searches this value starting from the end of the list and proceeding to the start of this list. The process will end when it gets the first result. This will be the last cell in the range that contains a value, in the last step that was turned into 1.
C:C – This is the last statement of the LOOKUP function. It drives the value of the cell to be replaced instead of the value obtained from the 2nd step.

1.2 Combining LOOKUP, NOT, and ISBLANK Functions to Find Last Cell with Value in Column

Here we will combine NOT and ISBLANK functions with LOOKUP functions. Those are needed if our data has any error output and we want to show this. Now, add one error data in our dataset and modify the formula to show this.

Steps:

In the 10th row, we added new data which is an error. We simply divided a random number by 0.

Now, add the NOT and ISBLANK functions in the formula. After modification, the formula becomes:

=LOOKUP(2,1/(NOT(ISBLANK(C:C))),C:C)

Now, press ENTER and we will get a result.
Here, we can see that in the result section, an error value is showing. Usually, the LOOKUP function avoids this error value.

1.3 Applying LOOKUP and ISNUMBER Functions to Find Last Cell with Numeric Value in Column

Sometimes we may have both alphabetic and numeric data in our column. But we want to get only the numeric data of the last cell. Then we will use the ISNUMBER function. It returns only numeric data.

Steps:

First, add alphabetic data in the 10th row.

Now, modify the formula and add the ISNUMBER So the formula becomes:

=LOOKUP(2,1/(ISNUMBER(C:C)),C:C)

Now, press ENTER and we will get a return value.
Here, our last data is alphabetic. As we used the ISNUMBER function, we are getting only numeric data.

1.4 Using LOOKUP with ROW Function to Find Row Where Last Value Exists

We can also get to know, in which row the last value exists. For this, we need to combine the ROW function with the LOOKUP function.

Steps:

Modify the formula and add the ROW function in the last argument. Now, the formula becomes:

=LOOKUP(2,1/((C:C)),ROW(C:C))

Finally press ENTER.
Now, we get 9 as a result. From the data set, we’ve seen that our last data is in row 9. That is shown here.
Here the value of the cell will not appear; only the row number or position will indicate.

Similar Readings:

2. Finding Last Cell with Numeric Value in Column Using Excel INDEX and COUNT Functions

The INDEX function returns the value of a specific cell in a range. We are going to apply the INDEX function with COUNTA and COUNT here.

Steps:

First, modify the data set. Remove the blank cell and add an alphabetic value in the range. Also, add a blank cell at the last.
Now, type INDEX Function.
The 1st argument takes the range C5 to C10. And the 2nd argument uses the COUNT function with the same range.
So, the formula becomes:

=INDEX(C5:C10,COUNT(C5:C10))

Then press ENTER.

Here, we get only numeric values as we used the COUNT function.
Now, we want to get any value in the range. For this, we will use the COUNTA function.
Copy the formula from cell D5. Paste the formula in cell D6 and replace the COUNT function with COUNTA. So, the formula becomes:

=INDEX(C5:C10,COUNTA(C5:C10))

Finally press ENTER.
Now, we get an alphabetic value as we use the COUNTA function. So, we can get our desired result by using the COUNT or COUNTA function with the INDEX function.

3. Applying OFFSET Function to Find Last Cell with Value in Column in Excel

Here, we will show how to find the last cell with value using the OFFSET function. Also, combine the COUNT & COUNTA function with this function.

3.1 Using Basic OFFSET Function

Here we will use the basic OFFSET function only. Also adding that this basic function can not identify which cell is blank or not.

Steps:

First, make sure that no empty cell is at the end.
Then, write the OFFSET in the 1st argument for reference, we select Cell C5 as reference. The next two arguments are the number of rows and columns respectively. These row and column numbers indicate which row and column we will search. Here we select 4 as we have 4 rows after the reference cell and 0 for the column as we will check only in this column. So, the formula becomes:

=OFFSET(C5,4,0)

Finally press ENTER.
Here is the result after applying the OFFSET function. As the last cell is non-zero it is showing the result. If the last cell is blank it will show blank.

3.2 Using OFFSET and COUNT Functions to Find Last Cell with Value in Column in Excel

In the previous method, we saw that the OFFSET function is unable to find the last cell with value if there is any blank cell. In this section, we will combine COUNT and COUNTA to solve this issue.

Steps:

First, add a blank cell at the end of the data set.
Now, go to Cell D5.
Write the add the COUNT function at the 2nd argument of the formula. It will give the row number after counting. So, the formula becomes:

=OFFSET(C5,COUNT(C5:C10)-1,0)

Then press ENTER.

As we used the COUNT function it does not consider alphabetic values. As we also want to get alphabetic values so replace the COUNT with COUNTA. The steps are given below.
Copy the formula from Cell D5.
Paste the formula in Cell D6.
Now, replace the COUNT function with COUNTA. So, the formula becomes:

=OFFSET(C5,COUNTA(C5:C10)-1,0)

Then press ENTER.
Here we are getting alphabetic values as we used the COUNTA function.
In this way, we can find the last cell with value in the column in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Find Last Cell with Value in Column.xlsx

Conclusion

In this article, we explain 3 methods and some sub-methods to find the last cell value in a column. Hope you can find your desired method that you can remember easily. If you have any suggestions, please write in the comment box. Keep learning new methods and keep growing!

How to Find Last Cell with Value in Column in Excel (3 Ways)

1. Inserting LOOKUP Function to Find Last Cell with Value in Column in Excel

1.1 Using Basic LOOKUP Function Only

1.2 Combining LOOKUP, NOT, and ISBLANK Functions to Find Last Cell with Value in Column

1.3 Applying LOOKUP and ISNUMBER Functions to Find Last Cell with Numeric Value in Column

1.4 Using LOOKUP with ROW Function to Find Row Where Last Value Exists

2. Finding Last Cell with Numeric Value in Column Using Excel INDEX and COUNT Functions

3. Applying OFFSET Function to Find Last Cell with Value in Column in Excel

3.1 Using Basic OFFSET Function

3.2 Using OFFSET and COUNT Functions to Find Last Cell with Value in Column in Excel

Conclusion

Related Links

Leave a reply Cancel reply