Alok Paul
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Methods to Find Last Cell with Value in 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.
1. Insert LOOKUP Function to Find Last Cell with Value in Column
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 Use 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 LOOKUP with NOT and ISBLANK Functions
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 LOOKUP with ISNUMBER Function
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
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:
- Find the Last Cell With Value in Row in Excel (6 Methods)
- Excel Find Last Column With Data (4 Quick Ways)
- Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)
- How to Find Multiple Values in Excel (8 Quick Methods)
2. Find Last Cell with Numeric Value in Column Using 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. Apply Excel OFFSET Function to Find Last Cell with Value in Column
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 Use 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 Use OFFSET and COUNT Functions
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.
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. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Thanks for the info, will be following up with structured lessons
You are welcome, Ruben! Please share your thoughts with us, too. Regards.
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