Spreadsheet Practice

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, …

  1. A2:C98 rows × 3 cols: 3/2/2026, "Priya", 420, 3/4/2026, …

    A range: 8 rows × 3 columns.

  2. -3-3

    Makes the value negative.

  3. TAKE(A2:C9,-3){3 rows × 3 cols} 46099, "Owen", 510, 46102, …

    TAKE: First (or last, if negative) rows or columns.

    • arrayA2:C98 rows × 3 cols: 3/2/2026, "Priya", 420, 3/4/2026, …
    • rows-3-3

In 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)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(A2:C9,ROWS(A2:C9)-2):INDEX(A2:C9,ROWS(A2:C9))spills 3 × 3Works 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

In 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-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.