TAKE Function
Keeps a number of rows or columns from the start or end of an array.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2024
Returns
A spilled array
Overview
TAKE keeps just the slice you want: the first 5 rows, the last 3, the first 2 columns, and so on. It's the opposite of DROP.
Positive numbers take from the top or left; negative numbers take from the bottom or right.
Syntax
=TAKE(array, rows, [columns])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to take from. |
| rows | Yes | Rows to keep. Positive = from the top, negative = from the bottom. |
| columns | Optional | Columns to keep. Positive = from the left, negative = from the right. |
Examples
Example 1 — Top 3 performers
Sort by sales, then keep the first three rows.
D2fx=TAKE(SORT(A2:B6, 2, -1), 3)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Sales | |
| 2 | Ana | 400 | Cara | 500 | |
| 3 | Ben | 350 | Ana | 400 | |
| 4 | Cara | 500 | Ben | 350 | |
| 5 | Dev | 275 |
=TAKE(SORT(A2:B6, 2, -1), 3) Explain
Example 2 — The latest entries
Keep the last 5 rows of a growing log.
=TAKE(TRIMRANGE(A2:C1000), -5) Explain
Leave rows empty to take only columns: =TAKE(A2:F20, , 2) keeps the first two columns.
Common mistakes
- #CALC! errors. rows or columns is 0, giving an empty result.
Notes
- Asking for more rows than exist simply returns them all.
- Related functions: DROP, CHOOSEROWS, SORT, TRIMRANGE.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.