Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to get the value of the last non-empty cell in Excel and Google Sheets
Excel & Google Sheets
=LOOKUP(2,1/(A2:A6<>""),A2:A6)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the column that contains your data list, e.g. column A starting at row 2.
- 2Enter =LOOKUP(2,1/(A2:A6<>""),A2:A6) in an empty cell — the LOOKUP trick uses the fact that dividing 1 by a TRUE/FALSE array produces 1s and errors, and LOOKUP finds the last 1.
- 3Press Enter; the formula returns the value of the last non-empty cell in the range.
- 4Extend the range (e.g. A2:A1000) to accommodate future data without changing the formula.
Tips
- Wrap the formula in IFERROR to return a friendly message when the entire range is empty: =IFERROR(LOOKUP(2,1/(A2:A100<>""),A2:A100),"No data").
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does this work for numeric and text values?
Yes — the <> "" test treats any non-blank cell (numbers, text, dates) as non-empty.
Why use LOOKUP(2,...) instead of LOOKUP(1,...)?
The lookup value 2 is larger than the maximum value in the array (1), so LOOKUP always lands on the last 1 — i.e. the last non-empty row.