Users often need to find the last non-empty cell of a column. In a long dataset, it is quite time-consuming to find the last non-empty cell or the value of that cell. In this article, we will discuss 9 ways how to go to the last non-empty cell in a column in Excel.

## How to Go to Last Non Empty Cell in Column in Excel (9 Handy Ways)

In this article, we will learn 9 ways to go to the last non-empty cell in Excel. Firstly, we will use ** the XMATCH function.** Secondly, we will go for

*the*

*XLOOKUP***. Thirdly, we will utilize a keyboard shortcut. Then, we will use a combination of the**

*function*

*SUMPRODUCT, MAX,**and*

*ROW**functions*. After that, we will combine

*the*

*INDEX, MAX,**and*

*ROW**functions*. Afterward, we will apply

*the*

*OFFSET**and*

*COUNTA**functions*together. Next, we will use

*the*

*INDEX**and*

*COUNTA**functions*in conjunction to get the result. In the penultimate method, we will opt for

**to do our job. Finally, we will resort to a**

*the LOOKUP function***code to go to the last non-empty cell in a column in Excel.**

*VBA*### 1. Using XMATCH Function

** The XMATCH function** locates a specified item within an array or cell range and then returns the item’s position within the array or range. In this method, we will use this feature of the function to get the position of the last non-empty cell in a column in Excel.

**Steps:**

- Firstly, select the
cell and write the following formula:*E5*

`=XMATCH("*",B:B,2,-1)`

- Then, hit
.*Enter*

- Consequently, we will find the row number of the last nonempty cell of the column.

Here, the first argument of the function,** “*”**, denotes what to look for in the column. The asterisk sign means it will look for cells with values. The next argument,

**, indicates the range of cells the function will look for values. The next argument,**

*B:B***, denotes a**

*2***. This match is employed when the exact content of the cell is not known. Finally, the**

*Wildcard character match***means it will match from bottom to top. Thus, it will find the last nonempty value in the dataset**

*-1*### 2. Applying XLOOKUP Function

To find items in a table or range by row, we use ** the XLOOKUP function**. In this method, we will use this Excel function to find the value of the last non-empty cell of a column.

**Steps:**

- To begin with, select the
cell and write the following formula:*E5*

`=XLOOKUP("*",B:B,B:B,"",2,-1)`

- Then, press
.*Enter*

.

- As a result, the function will return the content of the last nonempty cell in the column.

Here, the first argument is a ** Wildcard character**. It is used when we do not know the content of a cell. The next argument is the

**. In this case, it is**

*look_array***. The third argument is the**

*B:B***. It is also**

*return_array***, as we will return the value from the same array. The next argument is a blank character. If the function finds no match, it will return the blank character. The following argument,**

*B:B***, stands for**

*2***. When the exact contents of the cell are unknown, this match is used. The final**

*Wildcard character match***indicates that the order will match from bottom to top. Thus, it will find the last nonempty value in the dataset.**

*-1***Read More: **How to Select Column to End of Data in Excel

### 3. Using Keyboard Shortcut

In this instance, we will use a keyboard shortcut **Ctrl+Down Arrow** to go to the last nonempty cell of a column.

**Steps:**

- Firstly, select the first cell of the column.
- In this case, we will select the
**B5** - Then, hit
.*Ctrl+Down Arrow*

- Consequently, we will reach the last nonempty cell.

**Note:**

- This method is applicable only when there is no empty row in between the datasets.

**Read More:** [Solved!] CTRL+END Shortcut Key Goes Too Far in Excel

### 4. Combining SUMPRODUCT, MAX, and ROW Functions

** The SUMPRODUCT function **returns the sum of the products of the ranges or arrays that are given as an argument of the function. In this method, we will combine it with

*the*

*MAX**and*

*ROW**functions*to get the position of the last non-empty cell in a column in Excel.

**Steps:**

- To start with, select the
cell and write the formula below,*E5*

`=SUMPRODUCT(MAX(($B:$B<>"")*(ROW(B:B))))`

- Then, hit
.*Enter*

- As a result, we will get the row number of the last nonempty cell.

🔎 **Formula Breakdown:**

returns the row number of all the cells in the*ROW(B:B): The ROW function*column.*B*:*MAX(($B:$B<>””)*(ROW(B:B)))*returns the maximum value in an array. Here, the*The MAX function*is a condition. This condition validates if the cells in the range are nonempty or empty and create an array of*“$B:$B<>”””*and*TRUE*. If empty,it assigns*FALSE*; if not, it assigns*FALSE*. Then, this array gets multiplied with the array returned by*TRUE*. Finally,*the ROW function*returns the row of the cell that has the maximum value in the array of*the MAX function*. In this case, it is*the ROW function*.*10*Finally,**SUMPRODUCT(MAX(($B:$B<>””)*(ROW(B:B)))):**returns the product of*the SUMPRODUCT*array with*the MAX*array which is*the ROW*.*10*

### 5. Using Combination of INDEX, MAX, and ROW Functions

** The INDEX function** extracts a value from a table or range, or a reference to a value, and returns it. In this example, we will combine

*the*

*INDEX, MAX,**and*

*ROW**functions*to get the value of the last cell in a column.

**Steps:**

- To start with, write the following formula after selecting the
cell,*E5*

`=INDEX(B:B,INDEX(MAX(($B:$B<>"")*(ROW(B:B))),0))`

- Then, hit the
key.*Enter*

- As a result, the value of the last nonempty cell will be on the screen.

🔎 **Formula Breakdown:**

**(ROW(B:B):**returns the row number of all the cells in the*The ROW function*column.*B*returns the maximum value in an array. Here, the*MAX(($B:$B<>””)*(ROW(B:B))): The MAX function*is a condition. This condition validates if the cells in the range are nonempty or empty and create an array of*“$B:$B<>”””*and*TRUE*. If empty,it assigns*FALSE*; if not, it assigns*FALSE*. Then, this array gets multiplied with the array returned by*TRUE*. Finally,*the ROW function*returns the row of the cell that has the maximum value in the array of*the MAX function*. In this case, it is*the ROW function*.*10*Finally,**INDEX(B:B,INDEX(MAX(($B:$B<>””)*(ROW(B:B))),0)):**in the parenthesis returns the product of*the INDEX function*array and*the MAX*array, which is*the ROW*. This means the value*10*refers to the*10*cell in the reference range*10th*of*B:B*outside the parenthesis. So*the INDEX function*returns the value in the*the INDEX function*cell, which is*10th*.*“Tom”*

**Read More:** How to Go to the End of Excel Sheet

### 6. Applying OFFSET and COUNTA Functions Together

** The OFFSET function **returns a reference to a range that is separated from a cell or a range of cells by a specified number of rows and columns.

**returns the number of cells that are not empty. Here, we will combine these to get the value of the last non-empty cell in a column.**

*The COUNTA function***Steps:**

- First, select the
cell and write the following formula down,*E5*

`=OFFSET(B4,COUNTA(B4:B10)-1,0)`

- Then, press the
button.*Enter*

- Consequently, we will get the value of the last nonempty cell in the
cell.*E5*

🔎 **Formula Breakdown:**

**COUNTA(B4:B10):**counts the number of non-empty cells in a given range. In this case, the number of nonempty cells in the*The COUNTA function*range is*B4:B10*.*7**OFFSET(B4,COUNTA(B4:B10)-1,0)*:takes the*The OFFSET function*cell as a reference. The function’s next two arguments-*B4*and*rows*– respectively, define how many rows and columns to move from the reference cell. Here, the*cols*argument is*rows*or*COUNTA(B4:B10)-1*or*7-1*. The*6*argument is That means*cols*will return the value of the cell that is*the OFFSET function*cells below the*6*cell. In this case, the value is*B4*.*“Tom”*

### 7. Combining INDEX and COUNTA Functions

With the help of** the INDEX function**, you can retrieve a value from a table, a range, or a reference to a value. In this instance, we will use this function together with

**.**

*the COUNTA function***Steps:**

- Initially, choose cell
and enter the following formula:*E5*

`=INDEX(B4:B10,COUNTA(B4:B10),0)`

- Then, hit
.*Enter*

- We will therefore obtain the value of the final nonempty cell in the
cell.*E5*

🔎** Formula Breakdown:**

**COUNTA(B4:B10):**counts the number of non-empty cells in a given range. In this case, the number of nonempty cells in the*The COUNTA function*range is*B4:B10*.*7*numbers the cells of the range that is given as a reference. Here,*INDEX(B4:B10,COUNTA(B4:B10),0): The INDEX function*will number the cells in the range*the INDEX function*from*B4:B10*to*1*. It will return the value of the cell in the number*7*The value is*7*.*“Tom”*

### 8. Using LOOKUP Function

In this method, we will use ** the LOOKUP function** to go and find the value of the last nonempty cell in a column.

**searches for a reference indicated in the argument and returns the value of the reference cell.**

*The LOOKUP function***Steps:**

- At the start, select the
cell and type the following formula in the cell,*E5*

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

- Then, hit
.*Enter*

- Consequently, we will get the value of the last nonempty cell.

*Here, the*

**searches through the**

*LOOKUP function***range as indicated by the third argument. It searches if the cells are empty or not through the expression “**

*B:B***”.When it assigns**

*B:B<>””***if the cell is non-empty and**

*TRUE***otherwise. It returns the result as an array of**

*FALSE***and**

*TRUE***. Then, it turns the**

*FALSE***and**

*TRUE***into**

*FALSE***and**

*1***respectively. Finally, in the**

*-1***expression, the**

*“1/(B:B<>””)”***and**

*1’s***in the array divide the**

*-1’s***value and return an array of**

*1***and**

*1’s***.Then,**

*-1’s***looks for**

*the INDEX function***in the**

*2***range. Since the column contains only text data, it can’t find it. So, it returns the largest value in the array that is equal to or less than 2. In this case, it’s the last value in the array, which is**

*B:B***.**

*“Tom”*### 9. Applying VBA Code

In the final method, we will use a simple **VBA **code to go to the last non-empty cell in a column in * Excel*.

**Steps:**

- Firstly, select the dataset.
- Then, go to the
tab in the ribbon.*Developer* - From there, select the
tab.*Visual Basic* - Consequently, the
window will be opened.*Visual Basic*

- After that, in the
tab, click on*Visual Basic*.*Insert* - Then, select the
tab.*Module* - Consequently, a coding module will appear.

- In the coding module, write down the following code.
- Then, save the code.

```
Sub Last_Non_Empty_Cell()
'declaring variable
Dim Non_Empty_Cell As Long
Non_Empty_Cell = Range("B" & Rows.Count).End(xlUp).Row
'displaying result in a message box
MsgBox (Non_Empty_Cell)
End Sub
```

- Then, run the code from the
tab.*Run*

- Consequently, we will see the row number of the last nonempty cell in a message box.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

In a long Excel dataset, it takes a significant amount of time to go to the last non-empty cell in a column. These methods will allow users to go to the last nonempty cell and fetch the value or the row number of the cell in no time in Excel.

**Related Articles**

- How to Select Cells in Excel Using Keyboard
- Multiple Excel Cells Are Selected with One Click
- How to Select Multiple Cells in Excel Without Mouse
- How to Select Cells in Excel Without Dragging
- How to Select Large Data in Excel Without Dragging

**<< Go Back to Select Cells | Excel Cells | Learn Excel**