How to get the last N rows of a table in Excel
Excel formula
=TAKE(A2:C9,-3)Use TAKE with a negative number: =TAKE(A2:C9,-3) returns the last three rows. A positive number returns the first rows.
- Result on the example
- a 3-row list that spills from E2 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Not available, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
TAKE(array, rows, [columns]) keeps rows from the start when the number is positive and from the end when it is negative. Pass a second number to keep only some columns, and use DROP for the opposite: removing rows instead of keeping them.
=TAKE(A2:C9,-3)Result{3 rows × 3 cols} 46099, "Owen", 510, 46102, …
A2:C98 rows × 3 cols: 3/2/2026, "Priya", 420, 3/4/2026, …A range: 8 rows × 3 columns.
-3-3Makes the value negative.
TAKE(A2:C9,-3){3 rows × 3 cols} 46099, "Owen", 510, 46102, …TAKE: First (or last, if negative) rows or columns.- array
A2:C98 rows × 3 cols: 3/2/2026, "Priya", 420, 3/4/2026, … - rows
-3-3
- array
In Google Sheets
=QUERY(A2:C9,"select * offset 5"), or =INDEX(A2:C9,ROWS(A2:C9)-2):INDEX(A2:C9,ROWS(A2:C9)).Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:C9,ROWS(A2:C9)-2):INDEX(A2:C9,ROWS(A2:C9)) | spills 3 × 3 | Works in every version. |
Common mistakes
- The result spills, so keep the cells to the right and below empty.
- If the data is not in date order, sort first:
=TAKE(SORT(A2:C9,1),-3).
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 N rows of a table
E2, return the last three rows of the table in A2:C9.Questions
How do I get the top N after sorting?
Wrap it: =TAKE(SORTBY(A2:C9,C2:C9,-1),3) returns the three largest amounts.
Checked by running on 2026-11-21: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.