TAKE function
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.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- The first or last rows and columns of array. It spills.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =TAKE(SORT(A2:C8,3,-1),3) | Mayspills 3 × 3 | Sort by revenue, then keep the top 3 rows |
| I2 | =TAKE(A2:C8,-2) | Junspills 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | Orders | Revenue | Top 3 months | Last 2 months | Last 3 months revenue | |||||||
| Jan | 142 | $18,250 | May | 190 | $24,770 | Jun | 154 | $19,630 | $66,290 | |||
| Feb | 128 | $16,940 | Mar | 175 | $22,310 | Jul | 168 | $21,890 | ||||
| Mar | 175 | $22,310 | Jul | 168 | $21,890 | |||||||
| Apr | 161 | $20,480 | ||||||||||
| May | 190 | $24,770 | ||||||||||
| Jun | 154 | $19,630 | ||||||||||
| Jul | 168 | $21,890 |
Practice TAKE
Graded instantly, then re-checked on changed data.
Top three deals
E2, return the three largest deals from A2:C9, sorted by amount, largest first.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.
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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.