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.

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

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

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

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

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

- How to Find Last Non Blank Cell in Row in Excel
- How to Find Last Cell with Value in a Row in Excel
- Find Last Value in Column Greater than Zero in Excel
- How to Find Last Occurrence of a Value in a Column in Excel

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

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

ExcelDemyThank 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!

Hello

Abdrman,You are most welcome.

Regards

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

Hello

JULIThanks 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 ShimantoExcelDemy