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

We’ll use the following sample dataset to check how to go the last non-empty cell.

how to go to last non empty cell in column in excel

Method 1 – Using the 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.

Steps:

Select the E5 cell and use the following formula:

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

Hit Enter.

You’ll get the row number of the last non-empty cell of the column.

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, B:B, indicates the range of cells the function will look for values. The next argument, 2, denotes a Wildcard character match. Finally, the -1 argument means it will match from bottom to top.

Method 2 – Applying the XLOOKUP Function

Steps:

Select the E5 cell and insert the following formula:

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

Press Enter.

The function will return the content of the last non-empty cell in the column.

The first argument is a Wildcard character. The next argument is the look_array B:B. The third argument is the return_array. It is also B:B, as we’re returning the value from the same array. The next argument is a blank character. If the function doesn’t find a match, it will return the blank character. The following argument, 2, stands for Wildcard character match. The final -1 indicates that the order will match from bottom to top.

Method 3 – Using Keyboard Shortcuts

Steps:

Select the first cell of the column.
Hit Ctrl + Down Arrow.

We will reach the last non-empty cell.

Note:

This method is applicable only when there are no empty rows inside the dataset.

Method 4 – Combining SUMPRODUCT, MAX, and ROW Functions

Steps:

Select the E5 cell and use the formula below,

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

Hit Enter.

We will get the row number of the last non-empty cell.

Formula Breakdown:

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

Method 5 – Using a Combination of INDEX, MAX, and ROW Functions

Steps:

Use the following formula in cell E5:

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

Hit Enter.

You’ll get the value of the last non-empty cell in the column.

Formula Breakdown:

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

Method 6 – Applying OFFSET and COUNTA Functions

Steps:

Select the E5 cell and use the following formula:

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

Hit Enter.

You will get the value of the last non-empty cell in the E5 cell.

Formula Breakdown:

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

Method 7 – Combining INDEX and COUNTA Functions

Steps:

Choose cell E5 and enter the following formula:

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

Hit Enter.

We will obtain the value of the final non-empty cell in the E5 cell.

Formula Breakdown:

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

Method 8 – Using the LOOKUP Function

Steps:

Select the E5 cell and insert the following formula:

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

Hit Enter.

You will get the value of the last non-empty cell.

Here, the LOOKUP function searches through the B:B range as indicated by the third argument. It searches if the cells are empty or not through the expression “ B:B<>”” ”.When it assigns TRUE if the cell is non-empty and FALSE otherwise. It returns the result as an array of TRUE and FALSE. Then, it turns the TRUE and FALSE into 1 and -1 respectively. Finally, in the “1/(B:B<>””)” expression, the 1’s and -1’s in the array divide the 1 value and return an array of 1’s and -1’s.Then, the INDEX function looks for 2 in the B:B 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 “Tom”.

Method 9 – Applying VBA Code

Steps:

Select the dataset.
Go to the Developer tab in the ribbon.
Select the Visual Basic tab.
The Visual Basic window will be opened.

Click on Insert.
Select Module.
A coding module will appear.

Insert the following code and save it.

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