The mirror image: the last value in a column that is actively being appended to. This tutorial walks through the simplest, most maintainable formula-only approach in Google Sheets, plus the variants that come up once your data is more than a hundred rows.

The one-cell solution

=INDEX(A:A, MATCH(2, 1/(A:A<>"")))

The expression 1/(A:A<>"") produces 1 for non-empty cells and #DIV/0 for empty ones; MATCH for value 2 in this array always lands on the last non-empty row. A classic spreadsheet trick.

Why this beats copy-paste

Doing last non-empty cell by hand works once. Doing it with a formula means the answer updates automatically when source data changes, and the audit trail is right there in the formula bar — no chance of someone forgetting to repeat the steps next month. The trade-off is a small upfront investment in writing and testing the formula; the payoff is every time the data refreshes.

Edge cases to test

Before deploying any last non-empty cell formula in a real workflow, test three cases: empty source range, single-row source, and the full expected dataset. Spreadsheet formulas have an unfortunate habit of working perfectly on the sample data and falling apart on the live data because of a single missing value or unexpected type. Walk through the edge cases in a scratch tab before promoting the formula into the dashboard.

Production-grade variant

Wrap the production version in IFERROR with a friendly message, ARRAYFORMULA the column header inline, and add a small note cell explaining what the formula does. The next person to inherit the sheet (often a future-you with no memory of writing this) will silently thank you. Build it once, document it once, never write it again.

Worked examples

Baseline

=INDEX(A:A, MATCH(2, 1/(A:A<>"")))

Drop into the cell where you want the answer.

With error handling

=IFERROR(=INDEX(A:A, MATCH(2, 1/(A:A<>""))), "")

Returns blank when the source is empty rather than #REF or #N/A.

ARRAYFORMULA-friendly version

=ARRAYFORMULA(=INDEX(A:A, MATCH(2, 1/(A:A<>""))))

Use only if the inner functions array-evaluate correctly — test on a small range.

Common mistakes (and how to avoid them)

  • Hardcoding range bounds ("A2:A100") that go stale as data grows. Use open-ended ranges.
  • Skipping the empty-source case in testing — the formula works on data and fails the day someone clears the sheet.
  • Putting many copies of the same formula in different cells. Promote it to one ARRAYFORMULA-driven column.
  • Mixing presentation (currency formatting, units) into the formula. Format the cell instead.

Tips that pay off later

  • If the formula gets longer than two lines, break it into pieces with LET for readability.
  • Always sanity-check the answer against a manually computed value on a small dataset before trusting it on production data.
  • When sharing the sheet, lock the cell containing the formula so collaborators cannot accidentally overwrite it.

When to reach for something else

If your last non-empty cell workflow needs more than three coordinated formulas, consider promoting it to Apps Script — formulas are the right tool until they are not.

Functions used in this tutorial

INDEX → MATCH → IF → OFFSET → COUNTA →