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

sample dataset

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.


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.

use VLOOKUP function to Find Last Cell with Value in Column in Excel

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.

Output

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.

combine LOOKUP, NOT anfd ISBLANK to Find Last Cell with Value in Column in Excel

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

final output


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.

combine LOOKUP and ISNUMBER to Find Last Cell with Value in Column in Excel

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

final output


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

combine LOOKUP and ROW functions to Find Last Cell with Value in Column in Excel

Read More: How to Find Last Row with a Specific Value in Excel


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.

Find Last Cell with Value in Column in Excel

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

combine INDEX, COUNT and COUNTA FUNCTIONS to Find Last Cell with Value in Column in Excel


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.

use of OFFSET function


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.

combine OFFSET, COUNT & COUNTA functions to Find Last Cell with Value in Column in Excel

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

final output

Read More: How to Find Last Cell with Value in a Row in Excel


Download Practice Workbook

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


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!


Related Links


<< Go Back To Excel Last Value in Range | Excel Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

8 Comments
  1. Thanks for the info, will be following up with structured lessons

  2. Excellent. Useful formulas. Well explained in a very easy way.

    • Hello, Mubashir!

      Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.

      Regards
      ExcelDemy

  3. Thank You!
    I’ve been looking for a solution to referencing the last non empty cell in a sheet to another cell within the same sheet.
    Took me a while and a lot of searching to find your solution.
    But, it works great!
    Thanks again!

  4. I am using your solution 1.4 Using LOOKUP with ROW Function to Find Row Where Last Value Exists. I tried to modify it to lookup the information on another sheet, but it is returning #NAME? instead of the row number.

    I am using this: =LOOKUP(2,1/((Week_1!B:Week_1!B)),ROW(Week_1!B:Week_1!B))

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 10, 2024 at 6:23 PM

      Hello JULI

      Thanks for visiting our blog and sharing your query. You can modify the formula mentioned in example 1.4 and find the last cell with a number value on another sheet: =LOOKUP(2,1/(Week_1!C:C),ROW(Week_1!C:C))

      I hope the formula will overcome your issue; good luck.

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo