# How to get the last N rows of a table in Excel

Use TAKE with a negative number: =TAKE(A2:C9,-3) returns the last three rows. A positive number returns the first rows.

**Excel:** `=TAKE(A2:C9,-3)` (Excel 2024 and Microsoft 365)

**Google Sheets:** Google Sheets has no TAKE. Use `=QUERY(A2:C9,"select * offset 5")`, or `=INDEX(A2:C9,ROWS(A2:C9)-2):INDEX(A2:C9,ROWS(A2:C9))`.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Rep | Amount |  | Latest 3 |
| 2026-03-02 | Priya | 420 |  |  |
| 2026-03-04 | Tom | 135 |  |  |
| 2026-03-09 | Ines | 980 |  |  |
| 2026-03-11 | Marcus | 260 |  |  |
| 2026-03-15 | Hana | 74 |  |  |
| 2026-03-18 | Owen | 510 |  |  |
| 2026-03-21 | Lars | 305 |  |  |
| 2026-03-23 | Aiko | 190 |  |  |

Formula in E2: `=TAKE(A2:C9,-3)`

Result (spills 3 × 3): 46099, Owen, 510; 46102, Lars, 305; 46104, Aiko, 190

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

## Other ways

- `=INDEX(A2:C9,ROWS(A2:C9)-2):INDEX(A2:C9,ROWS(A2:C9))`: 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)`.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-11-20.

Source: https://spreadsheetpractice.com/formulas/get-the-last-n-rows-of-a-table
