# How to show the last 5 entries in a list in Excel

Drop the empty rows with FILTER, then take from the bottom: `=TAKE(FILTER(A2:C20,A2:A20<>""),-H1)`. A negative number counts from the end.

**Excel:** `=TAKE(FILTER(A2:C20,A2:A20<>""),-G1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=CHOOSEROWS(FILTER(A2:C20,A2:A20<>""),SEQUENCE(G1,1,-G1))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Item | Qty |  |  | Show last | 5 |
| 2026-06-01 | Copy paper | 10 |  |  |  |  |
| 2026-06-03 | Toner | 2 |  |  |  |  |
| 2026-06-04 | Staples | 6 |  |  |  |  |
| 2026-06-08 | Sticky notes | 12 |  |  |  |  |
| 2026-06-09 | Binders | 4 |  |  |  |  |
| 2026-06-11 | Copy paper | 8 |  |  |  |  |
| 2026-06-15 | Pens | 24 |  |  |  |  |
| 2026-06-16 | Toner | 1 |  |  |  |  |
| 2026-06-18 | Folders | 15 |  |  |  |  |

Formula in F3: `=TAKE(FILTER(A2:C20,A2:A20<>""),-G1)`

Result (spills 5 × 3): 6/9/2026, Binders, 4; 6/11/2026, Copy paper, 8; 6/15/2026, Pens, 24; 6/16/2026, Toner, 1; 6/18/2026, Folders, 15

## How it works

The range runs to row 20 so new entries are picked up, but that means its last rows are empty. FILTER removes them first, and TAKE with `-G1` returns the last five rows of what's left. Add an entry in row 11 and the oldest one drops off the list.

Google Sheets has no TAKE, so the Sheets version uses CHOOSEROWS, which accepts negative row numbers: SEQUENCE builds -5, -4, -3, -2, -1.

## Other ways

- `=DROP(FILTER(A2:C20,A2:A20<>""),COUNTA(A2:A20)-G1)`: DROP removes rows from the top instead: everything except the last five.
- `=SORT(TAKE(FILTER(A2:C20,A2:A20<>""),-G1),1,-1)`: Sort the result to show the newest entry first. (Newest first: Folders on top.)

## Common mistakes

- `TAKE(A2:C20,-5)` without FILTER returns the empty rows at the bottom of the range, shown as zeros.
- TAKE needs Excel 2024 or Microsoft 365. In Excel 2021 use `INDEX` with SEQUENCE on the filtered rows.
- In Google Sheets, CHOOSEROWS returns an error if the list has fewer rows than you ask for. Use `MIN(G1,COUNTA(A2:A20))` as the count.

## Questions

**How do I get the 5 most recent entries when the list isn't in date order?**

Sort by date first, newest on top, and take from the top: `=TAKE(SORT(FILTER(A2:C20,A2:A20<>""),1,-1),5)`.

**How do I show only the last entry?**

Take one row: `=TAKE(FILTER(A2:C20,A2:A20<>""),-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/show-the-last-n-entries-in-a-list