# TAKE function in Excel

> TAKE keeps the first or last rows (and columns) of a range and drops the rest.

Source: https://spreadsheetpractice.com/functions/take · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** TAKE keeps the first or last rows (and columns) of a range and drops the rest.
- **Syntax:** `=TAKE(array, rows, [columns])`
- **Most common use:** `=TAKE(SORT(A2:C8,3,-1),3)` returns May (Sort by revenue, then keep the top 3 rows)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** The first or last rows and columns of array. It spills.

## What it does

TAKE keeps the first or last rows (and columns) of a range and drops the rest. Wrapped around SORT it gives a live top-N list; with a negative count it returns the latest entries from a log that grows downward.

## Syntax

`=TAKE(array, rows, [columns])`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to take from. |
| `rows` | Required | How many rows to keep. A positive number counts from the top, a negative one from the bottom. |
| `columns` | Optional | How many columns to keep, counted the same way. Leave it out to keep every column. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=TAKE(SORT(A2:C8,3,-1),3)` | May (spills 3 × 3) | Sort by revenue, then keep the top 3 rows |
| I2 | `=TAKE(A2:C8,-2)` | Jun (spills 2 × 3) | A negative count takes from the bottom: the latest two months |
| M2 | `=SUM(TAKE(C2:C8,-3))` | $66,290 | Total revenue for the last three months |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Orders | Revenue |  | Top 3 months |  |  |  | Last 2 months |  |  |  | Last 3 months revenue |
| 2 | Jan | 142 | $18,250 |  | May | 190 | $24,770 |  | Jun | 154 | $19,630 |  | $66,290 |
| 3 | Feb | 128 | $16,940 |  | Mar | 175 | $22,310 |  | Jul | 168 | $21,890 |  |  |
| 4 | Mar | 175 | $22,310 |  | Jul | 168 | $21,890 |  |  |  |  |  |  |
| 5 | Apr | 161 | $20,480 |  |  |  |  |  |  |  |  |  |  |
| 6 | May | 190 | $24,770 |  |  |  |  |  |  |  |  |  |  |
| 7 | Jun | 154 | $19,630 |  |  |  |  |  |  |  |  |  |  |
| 8 | Jul | 168 | $21,890 |  |  |  |  |  |  |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #NAME? | TAKE needs Excel 2024 or Microsoft 365. In Excel 2021, =INDEX(SORT(A2:C8,3,-1),SEQUENCE(3),SEQUENCE(1,3)) returns the same top 3 rows. |
| #CALC! | rows or columns is 0, which asks for an empty result. Use a positive number to count from the start or a negative one to count from the end. |
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |

## Tips

- Use an Excel table as array, such as Table1, so TAKE(Table1,-5) always returns the five newest rows.
- Asking for more rows than exist is fine: TAKE returns everything there is.

## Related functions

- [DROP](https://spreadsheetpractice.com/functions/drop): DROP removes rows or columns from the start or end of a range and returns what is left.
- [SORT](https://spreadsheetpractice.com/functions/sort): SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.
- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.

## Questions

### How do I get the top 5 values with a formula?

Sort, then take: =TAKE(SORT(A2:C20,3,-1),5) returns the five rows with the highest values in the third column.

### How do I return the last row of a table?

=TAKE(A2:C20,-1) returns the last row. Point it at an Excel table so new rows are included automatically.

### What is the difference between TAKE and DROP?

TAKE keeps the rows you name and discards the rest; DROP removes the rows you name and keeps the rest.

## Practice

A graded TAKE exercise ("Top three deals") is on [the TAKE page](https://spreadsheetpractice.com/functions/take). It is checked instantly, then re-checked on changed data.

Lessons that use TAKE:

- [Lesson 29: SORTBY, TAKE & SEQUENCE](https://spreadsheetpractice.com/learn/sortby-take-sequence) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
