What it does
A positive second argument counts from the start and a negative one from the end: =TAKE(A2:C20,3) returns the first three rows and =TAKE(A2:C20,-3) the last three. The third argument trims columns in the same way.
TAKE is mostly used to cut down the result of other dynamic array functions. The first three rows of a table sorted largest first with SORT give a "top three" list; the first row of a FILTER result is the first record that meets the condition.
To drop rows from the start or end, use its partner DROP; to pick particular columns, use CHOOSECOLS. Outside Microsoft 365, similar ranges are built with INDEX or OFFSET.
Syntax
=TAKE(array, rows, [columns])=AL(dizi; satırlar; [sütunlar])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range or array to take rows and columns from. |
rows | Required | The number of rows to take; a negative number takes from the end. |
columns | Optional | The number of columns to take; a negative number takes from the end. If omitted, all columns are returned. |
Examples
1List the three highest sales
SORT orders the table by amount, largest first; TAKE keeps the first three rows of that result. The result spills into E2:G4.
=TAKE(SORT(A2:C7,3,-1),3)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Rep | Region | Amount | |
| 2 | Emma | West | 48,500 | Liam | North | 61,200 | |
| 3 | Liam | North | 61,200 | Ava | North | 57,800 | |
| 4 | Olivia | West | 52,300 | Olivia | West | 52,300 | |
| 5 | Noah | South | 29,750 | ||||
| 6 | Ava | North | 57,800 | ||||
| 7 | Mason | South | 33,100 |
- E2 (the result spills into E2:G4)
=TAKE(SORT(A2:C7,3,-1),3)
Liam (61,200) heads the list and Olivia (52,300) is third. When a rep's amount changes, the top three refresh by themselves.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Show the last three stock movements
The stock movements are listed in date order. With rows set to -3, TAKE takes three rows from the end of the list.
=TAKE(A2:C8,-3)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Movement | Qty | Date | Movement | Qty | |
| 2 | 20-Oct-2026 | In | 120 | 30-Oct-2026 | Out | 60 | |
| 3 | 22-Oct-2026 | Out | 45 | 2-Nov-2026 | Out | 25 | |
| 4 | 26-Oct-2026 | Out | 30 | 4-Nov-2026 | In | 50 | |
| 5 | 28-Oct-2026 | In | 80 | ||||
| 6 | 30-Oct-2026 | Out | 60 | ||||
| 7 | 2-Nov-2026 | Out | 25 | ||||
| 8 | 4-Nov-2026 | In | 50 |
- E2 (the result spills into E2:G4)
=TAKE(A2:C8,-3)
The last three movements land in E2:G4; the oldest is dated 30-Oct-2026 and the newest 4-Nov-2026. The order stays as it is in the source.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #CALC!
Cause: rows or columns is 0; an empty array cannot be returned.
Fix: Make sure the formula that gives the number returns at least 1 (or at most -1).
- #SPILL!
Cause: A cell in the spill area is not empty.
Fix: Clear the cells below and to the right of the formula.
Good to know
- If rows is larger than the number of rows in the array, there is no error; you simply get all the rows.
- If you only need the five highest amounts, use
=TAKE(SORT(C2:C20,1,-1),5); in older versions the same values can be taken one by one with LARGE.
In Nexell
Get Nexell to do it from a sentence
Show the three highest sales from the sales list in a separate table with the rep and the amount
Convert to tableBring your sales list into the tool and type this. Nexell sorts the list by amount, puts the top three rows into a separate table and shows it in a preview first; once you approve, you download it as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
Related functions
- SORTSIRALA · Sorts a range (1 ascending, -1 descending).
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- CHOOSECOLSSÜTUNSEÇ · Returns chosen columns of an array.
- VSTACKDÜŞEYYIĞ · Stacks arrays vertically.
- LARGEBÜYÜK · k-th largest value.
- DROPBIRAK · Drops rows or columns from the start or end of an array.