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

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

**Google Sheets:** `=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)`

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Date | Weight (kg) |  | Latest |
| 2026-01-05 | 82.4 |  |  |
| 2026-01-12 | 81.9 |  |  |
| 2026-01-19 |  |  |  |
| 2026-01-26 | 81.6 |  |  |
| 2026-02-02 | 81.1 |  |  |
| 2026-02-09 |  |  |  |
| 2026-02-16 | 80.8 |  |  |
| 2026-02-23 | 80.9 |  |  |

Formula in D2: `=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)`

Result: 80.9

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

## Other ways

- `=INDEX(FILTER(B2:B,B2:B<>""),COUNTA(B2:B))`: INDEX with the number of filled cells does the same.
- `=INDEX(B2:B,COUNTA(B2:B))`: The 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),"")`.

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

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/get-the-last-value-in-a-column