How to find the last row with data in a column in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)Search from the bottom for a filled cell and return its row number: =XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1).
- Result on the example
- 11 in
D1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D1. Change any input and the result updates, or edit the formula to experiment.
How it works
A2:A15<>"" marks the filled cells and ROW(A2:A15) lists their row numbers. XLOOKUP searches from the bottom (-1) and returns the row number of the first filled cell it meets: 11. The empty rows in between don't matter.
=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)Result11
A2:A1514 rows × 1 col: "Received pallet 14", "Counted bin A3", (empty), "Moved stock to B2", …A range: 14 cells in one column.
A2:A15<>""{14 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The <> operator tests whether they differ.
ROW(A2:A15){14 rows × 1 col} 2, 3, 4, 5, …ROW: Row number of a reference, or of this cell.- reference
A2:A1514 rows × 1 col: "Received pallet 14", "Counted bin A3", (empty), "Moved stock to B2", …
- reference
-1-1Makes the value negative.
XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)11XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
A2:A15<>""{14 rows × 1 col} TRUE, TRUE, FALSE, TRUE, … - return_array
ROW(A2:A15){14 rows × 1 col} 2, 3, 4, 5, … - if_not_found
(omitted) - match_mode
0 - search_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1). We ran it in Google Sheets mode on the same data and it returns 11.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX((A2:A15<>"")*ROW(A2:A15)) | 11 | Every version (Ctrl+Shift+Enter in Excel 2019 and earlier): the largest row number among filled cells. |
| =COUNTA(A:A) | 8Different result | Shown for contrast: counting entries only gives the last row when there are no gaps. Returns 8, the number of filled cells including the header. |
Common mistakes
- COUNTA-based tricks like
INDEX(A:A,COUNTA(A:A))fail as soon as a row is skipped. - A formula returning "" looks empty and is treated as empty here, but Excel's Ctrl+End still counts it as used.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the last row with data in a column
D1, return the sheet row number of the last filled cell in A2:A15.Questions
How do I get the last value instead of its row?
Return the column itself: =XLOOKUP(TRUE,A2:A15<>"",A2:A15,,0,-1).
How do I find the last row used in any of several columns?
Take the larger of each column's result, or test the block: =MAX((A2:C15<>"")*ROW(A2:C15)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.