# How to find the last row with data in a column in Excel

Search from the bottom for a filled cell and return its row number: `=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)`.

**Excel:** `=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Entry |  | Last row used |
| Received pallet 14 |  |  |
| Counted bin A3 |  |  |
|  |  |  |
| Moved stock to B2 |  |  |
| Counted bin A4 |  |  |
|  |  |  |
| Received pallet 15 |  |  |
| Shipped order 5521 |  |  |
|  |  |  |
| Counted bin B1 |  |  |
|  |  |  |
|  |  |  |
|  |  |  |
|  |  |  |

Formula in D1: `=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)`

Result: 11

## 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.

## Other ways

- `=MAX((A2:A15<>"")*ROW(A2:A15))`: Every version (Ctrl+Shift+Enter in Excel 2019 and earlier): the largest row number among filled cells.
- `=COUNTA(A:A)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/last-row-with-data