# How to Find Last Cell with Value in a Row in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you have a very large dataset, it can be quite time-consuming to find the last cell with value in a row in your Excel dataset. In this article, I’ll introduce you to 6 methods by which you can easily find the last cell with value in a row in Excel.

Consider the following dataset. Here Information about different customers who have applied for a bank loan is given. Now we will find the last cell with data in a row using this dataset. Let’s see how we can perform this task in 6 different ways.

## 1. Using Keyboard Shortcut to Find the Last Cell with Value in a Row in Excel

The easiest way to find the last cell with a value in a row is by using the keyboard command.

Just click on the row’s first cell and press CTRL+ Right Arrow Key. Your cursor will move to the last non-empty cell in that row. ## 2. Getting the Last Cell with Value in a Row by Using OFFSET Function in Excel

If you know the number of columns and rows of your dataset, you can find the last cell value in any row by using the OFFSET function. To find out the last cell value in Row 6, type the formula in an empty cell,

`=OFFSET(B4,2,7,1,1)`

Here,

• B4 = First cell of the dataset
• 2 =  Number of the row of your dataset excluding the first row
• 7 =  Number of the column of your dataset excluding the first column
• 1 = cell height
• 1 = cell width Press ENTER and you will find the value of the last cell of Row 6, in your selected cell. ## 3. Using Excel INDEX and COUNTA Functions to Find the Last Non-Blank Cell in a Row

Using the INDEX function along with the COUNTA function allows you to find the last cell value of any row. To find the last cell value in Row 5, type the following formula in an empty cell,

`=INDEX(B5:I12,1,COUNTA(5:5))`

Here,

• 5:5 = Row 5
• B5:I12 = Array
• 1 = row_num You will find the value of the last cell of Row 5, in your selected cell. 🔎 Formula Breakdown

↪️ The COUNTA function counts the number of cells in a range that are non-empty.

COUNTA(5:5) returns the total number of non-empty cells in Row 5 => 8

↪️ INDEX(B5:I12,1,COUNTA(5:5)) = INDEX(B5:I12,1,8) = 23

So, final Output => 23

## 4. Using the MATCH Function to Find the Last Cell with Value in a Row in Excel

Using  the MATCH function, you can find the number of the last cell with a value in any particular row. Here, our lookup cell value is 25 which exists in Row 10. We want to find the relative position of 25 in Row 10, type the following formula in any of the empty cells,

`=MATCH(D14,10:10,0)`

Here,

• D14 = lookup_value
• 10:10 = Row 10
• 0 = Exact match You will find the number of the last non-empty cell of Row 10, in your selected cell. ## 5. Finding the Last Cell Value in a Row Using Excel LOOKUP Function

You can find the last cell value of the last row by using the LOOKUP function. Type the formula in an empty cell,

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

Here, I:I = Last column of the dataset After pressing ENTER, You will find the value of the last cell of the last row of the dataset, in your selected cell. ## 6. Applying HLOOKUP Function to Find Last Cell with Value in a Row in Excel

Using the HLOOKUP Function is another way to find the value of the last cell of any row. Now we will find the last cell of Row 8 in our dataset. To find the value, type the formula in an empty cell,

`=HLOOKUP(I4,B4:I12,5,FALSE)`

Here,

• I4= Last column of the first row (Reference cell)
• B4:I12 = Range of Dataset
• 5 = 5th Row of our dataset including the reference cell’s row
• FALSE = Exact Match You will find the value of the last cell of Row 8, in your selected cell. ## Conclusion

So, we’re done! You can follow any of the ways described above to find the last cell with value in a row in Excel. Please leave a comment if you face any unexpected situations. To get acquainted with more Excel features and functions, kindly visit our website. We always try to provide quick solutions to your problems.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1. Reply How do you do #3 above, but pulling from another workbook? Thank you!

• Reply Hello ANTHONY,
Here, we’ll get the value of the last cell of a row and show it in cell C14. But, as per your condition, we’ll retrieve this value from another workbook named Sales. Now, we want to extract the value of the last cell of Row 5 from the Dataset worksheet of this workbook. To do this,
• At first, select cell C14 and call the INDEX function. • Then, select cells in the B5:E5 range. • After that, insert the COUNTA function and select the same range as the argument.
• As usual, press the ENTER button. • Finally, you can see the result in cell C14 and the final form of the formula is like the following.
`=INDEX([Sales.xlsx]Dataset!\$A\$5:\$D\$5,COUNTA([Sales.xlsx]Dataset!\$A\$5:\$D\$5))` Here, the part inside the square brackets are the name of the workbook. And the part before the exclamation mark and after the square bracket is the name of the worksheet.
That’s how you can do it from another workbook. It’s all from me on this topic. Follow our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

2. Reply Awesome, thank you that was helpful! Any chance you know how to reference 2nd to last cell with data from the other workbook?

• Reply Tanjima Hossain Nov 27, 2022 at 11:10 AM

Hello ANTHONY,
If your second to the last row is situated in Row 11, then you can use the following formula.
=INDEX(‘[Sales.xlsx]Dataset’!\$A\$11:\$H\$11,COUNTA(‘[Sales.xlsx]Dataset’!\$A\$11:\$H\$11))
You have to just change the reference according to the position of your desired row.

Best Regards
Tanjima Hossain

3. Reply Hi, how you can exclude a specific value thorugh the whole row?

• Reply Tanjima Hossain Nov 27, 2022 at 3:40 PM

Hello PEDRO,
As per your question, I will try to show an easier way to remove a specific value from a row. Here, we have the specific text “Furniture” in three rows which we will remove from these rows. • Go to the Home tab >> Find & Select dropdown >> Replace option. Then, you will have the Find and Replace dialog box.

• Type Furniture in the Find what box, and blank in the Replace with box.
• Click on Replace All. Then, a message box will notify you about the number of replacements. In this way, we have removed Furniture from three rows. If you want to remove this specific text from a specific row only, then before doing the stated procedures just select that specific row.

Best Regards,
Tanjima Hossain Advanced Excel Exercises with Solutions PDF  