How to show the last 5 entries in a list in Excel
Excel formula
=TAKE(FILTER(A2:C20,A2:A20<>""),-G1)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.
- Result on the example
- a 5-row list that spills from F3 in
F3 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in F3. Change any input and the result updates, or edit the formula to experiment.
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.
=TAKE(FILTER(A2:C20,A2:A20<>""),-G1)Result{5 rows × 3 cols} 46182, "Binders", 4, 46184, …
A2:C2019 rows × 3 cols: 6/1/2026, "Copy paper", 10, 6/3/2026, …A range: 19 rows × 3 columns.
A2:A2019 rows × 1 col: 6/1/2026, 6/3/2026, 6/4/2026, 6/8/2026, …A range: 19 cells in one column.
A2:A20<>""{19 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <> operator tests whether they differ.
FILTER(A2:C20,A2:A20<>""){9 rows × 3 cols} 46174, "Copy paper", 10, 46176, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C2019 rows × 3 cols: 6/1/2026, "Copy paper", 10, 6/3/2026, … - condition1
A2:A20<>""{19 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
- range
G15The value in G1.
-G1-5Makes the value negative.
TAKE(FILTER(A2:C20,A2:A20<>""),-G1){5 rows × 3 cols} 46182, "Binders", 4, 46184, …TAKE: First (or last, if negative) rows or columns.- array
FILTER(A2:C20,A2:A20<>""){9 rows × 3 cols} 46174, "Copy paper", 10, 46176, … - rows
-G1-5
- array
In Google Sheets
Google Sheets needs a different formula: =CHOOSEROWS(FILTER(A2:C20,A2:A20<>""),SEQUENCE(G1,1,-G1)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =DROP(FILTER(A2:C20,A2:A20<>""),COUNTA(A2:A20)-G1)Excel only | spills 5 × 3 | DROP removes rows from the top instead: everything except the last five. |
| =SORT(TAKE(FILTER(A2:C20,A2:A20<>""),-G1),1,-1)Excel only | spills 5 × 3Different result | 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
INDEXwith 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.
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.
Show the last 5 entries in a list
F3, show the last G1 entries of the log in A2:C20. The range has empty rows at the bottom for new entries.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.