How to Find the Last Cell with Value in a Row in Excel

When working with data in Excel, it’s common to have rows with varying lengths of content. In some cases, you may want to identify the last non-empty cell in a row, especially if you’re building summaries, reports, or dashboards.

In this article, you’ll learn several methods to find the last cell with a value in a row using formulas and built-in Excel functions. Each method is practical, accurate, and designed for real-world datasets.

What Does “Finding Last Cell with Value in Row” Mean in Excel?

In Excel, finding the last cell with a value in a row means identifying the rightmost cell that contains data in a horizontal line of cells. This is helpful when you’re working with data that grows over time, such as monthly sales, daily entries, or status logs. Instead of checking each cell manually, Excel can return the most recent or last filled value using built-in functions or VBA. This makes your workflow faster and more accurate, especially when dealing with large or constantly updating spreadsheets.

If you’re working with a row that has sporadic entries, like monthly data where some months are blank, the LOOKUP function offers a reliable way to find the last filled cell. In our sample dataset, each row represents a person’s data from January to July. Not all months have values, so we want to return the most recent (rightmost) number in each row.

This method is great for older versions of Excel and works efficiently even with inconsistent data patterns. It doesn’t require any special add-ins or dynamic array support.

Steps:

➤ Select the cell where you want the result (e.g., H2).
➤ Enter the following formula:

➤ Press Enter.

This formula looks for the last non-blank value in the range B2:G2. It works by dividing 1 by the TRUE/FALSE array and returning the last matching result.

This method is a bit more advanced, using array logic. It returns the value from the last filled cell in the row by combining the INDEX and MAX functions. The formula finds the column number of the last non-empty cell, then uses that position to pull the actual value from the row.

Steps:

➤ Insert the formula in any empty cell (e.g., H2):

➤ Press  Ctrl  +  Shift  +  Enter  (for older Excel versions) or just Enter in Excel 365/2021.

This is an array formula that finds the column number of the last non-empty cell, then uses INDEX to return the value.

If you’re only interested in knowing the column number of the last filled cell, not the value itself, you can use this formula. It’s useful when the column index matters more than the content, such as in dynamic table generation or logic-driven templates.

Steps:

➤ Insert the formula:

➤ Press  Ctrl  +  Shift  +  Enter  if required or just Enter for newer Excel versions.

➤ To convert the result into a letter (e.g., “D” for column 4), use:

Now you can see the last value was entered in Column E.

If you’re working with a dynamic or automated workflow, using a VBA function might be more efficient. This approach defines a custom function that can be reused across your workbook.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Insert a new module.
➤ Paste the following code:

Function LastCellValueInRow(rng As Range) As Variant
    Dim i As Long
    For i = rng.Columns.Count To 1 Step -1
        If rng.Cells(1, i).Value <> "" Then
            LastCellValueInRow = rng.Cells(1, i).Value
            Exit Function
        End If
    Next i
End Function

➤ Save and close the editor.
➤ In any cell, use the function like this:

This function returns the last value in the specified row range.

If you have access to Excel 365 or 2019, the XLOOKUP function makes this task more readable. It can search from the end of the row and return the first non-blank cell it encounters.

Steps:

➤ Select the cell where you want the result to appear.
➤ Enter the following formula:

Frequently Asked Questions

Can I use these methods for columns instead of rows?

Yes. While this article focuses on rows (horizontal data), all formulas can be adapted for vertical data by changing the cell references. Just replace horizontal ranges like B2:G2 with vertical ones such as B2:B100, and the formulas will work accordingly.

What if there are errors in the cells?

If your data includes cells with errors like #DIV/0! or #N/A, some formulas may stop at those errors. To avoid this, wrap the formula with IFERROR or use cleaner data ranges. VBA functions also let you customize how errors are handled.

Do these methods work with merged cells?

Merged cells can interfere with these formulas, especially when checking for blanks or using array functions. It’s recommended to avoid merged cells when using formulas like LOOKUP or INDEX. Instead, keep cells unmerged and format for appearance without merging.

How do I adapt these formulas for dynamic ranges?

To make your formulas flexible, you can use named ranges, OFFSET, or INDEX with MATCH. These tools allow your formulas to adjust automatically when rows or columns are added. This is helpful in dashboards or templates that expand over time.

Wrapping Up

In this tutorial, you learned multiple ways to find the last cell with a value in a row in Excel. Whether you prefer formulas like LOOKUP, XLOOKUP, or more advanced solutions like VBA, Excel provides flexible tools to retrieve the last entered value in a row. Feel free to download the practice file and share your thoughts and suggestions.