Spreadsheet Practice

How to get the last value in a column in Google Sheets

Google Sheets formula

=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)

Keep the filled cells and take the last one: =CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1). It works even with gaps in the column.

Result on the example
80.9 in D2
Excel
See below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

FILTER returns only the filled cells of B2:B, so the gaps disappear. CHOOSEROWS accepts negative positions counted from the end, so -1 is the last value in that list. Log a new weight and the result moves to it.

=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)

Result80.9

  1. B2:B48 rows × 1 col: 82.4, 81.9, (empty), 81.6, …

    A range: 48 cells in one column.

  2. B2:B<>""{48 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …

    The <> operator tests whether they differ.

  3. FILTER(B2:B,B2:B<>""){6 rows × 1 col} 82.4, 81.9, 81.6, 81.1, …

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeB2:B48 rows × 1 col: 82.4, 81.9, (empty), 81.6, …
    • condition1B2:B<>""{48 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …
  4. -1-1

    Makes the value negative.

  5. CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)80.9

    CHOOSEROWS: The rows you name from an array; negative numbers count from the end.

    • arrayFILTER(B2:B,B2:B<>""){6 rows × 1 col} 82.4, 81.9, 81.6, 81.1, …
    • row_num1-1-1

In Excel

Excel has no open ranges; use a range with room to grow and TAKE in Microsoft 365, =TAKE(FILTER(B2:B500,B2:B500<>""),-1), or =LOOKUP(2,1/(B2:B500<>""),B2:B500) in any version.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(FILTER(B2:B,B2:B<>""),COUNTA(B2:B))80.9INDEX with the number of filled cells does the same.
=INDEX(B2:B,COUNTA(B2:B))0Different resultThe common shortcut, which only works when the column has no gaps. Returns the wrong cell here: COUNTA finds 6 values, and the 6th row is a gap.

Common mistakes

  • Formulas returning "" count as filled for COUNTA. The FILTER version skips them.
  • If the column is empty, FILTER returns #N/A. Wrap it: =IFNA(CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1),"").

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.

Get the last value in a column

In D2, return the most recent weight: the last filled cell in column B, even though some weeks are blank.

Questions

How do I get the date of the last entry too?

Filter both columns: =CHOOSEROWS(FILTER(A2:B,B2:B<>""),-1) returns the date and the weight.

How do I get the last value in a row?

Use CHOOSECOLS on a filtered row: =CHOOSECOLS(FILTER(2:2,2:2<>""),-1).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.