TAKE - Select a contiguous first or last slice
Keep the first three records of an already-ranked report, or take a tail or column slice without assuming TAKE sorts the data.
By the end, you can
- Select from the beginning or end using the sign of a count.
- Sort before selecting a largest-value batch.
- Distinguish contiguous selection from arbitrary column choice.
Before you start: Understand SORT and spill dimensions.
In this lesson
Take from the current order
TAKE returns a specified number of contiguous rows or columns from an array's start or end. Finance can retain three rows of a ranked claim list, and dispatch can select the first part of a number allocation. TAKE does not rank, filter, or deduplicate anything by itself; the source order determines which records it selects.
Syntax and arguments
=TAKE(array, rows, [columns])Positive counts take from the start; negative counts take from the end. For a column-only Excel selection, rows can be left omitted while columns is supplied.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | The source array to slice. | A2:B5 |
rowsOptional | Row count when selecting rows; leave its position empty for a column-only Excel request. | 3 |
columnsOptional | Optional column count; at least a row or column selection is needed. | 1 |
Three largest claims from a ranked list
C102| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Claim | Amount | Selected claim | Amount | |
| 2 | C101 | ₹1,200 | C102 | ₹3,500 | |
| 3 | C102 | ₹3,500 | C104 | ₹2,400 | |
| 4 | C103 | ₹800 | C101 | ₹1,200 | |
| 5 | C104 | ₹2,400 |
Source order, tail selection, and columns
=TAKE(A2:B5,2)The original order supplies C101 and C102; these are not automatically the two largest claims.
=TAKE(A2:B5,-1)A negative row count selects from the array's end, returning C104.
=TAKE(A2:B5,,1)Omit rows and keep the first column across the existing rows.
Contiguous slices, errors, and versions
- The operation takes contiguous rows or columns in their current order; it does not choose scattered fields. CHOOSECOLS handles explicit reordered columns.
- Zero-sized requested results can produce #CALC! in Excel. Keep the counts meaningful rather than expecting an empty usable spill.
- A blocked result area gives #SPILL!. Large arrays can exceed calculation or worksheet limits.
- TAKE is available in Microsoft 365 and Excel 2024, not perpetual Excel 2021/2019/2016. Having other dynamic-array functions does not guarantee TAKE exists.
Common mistakes and Practice scope
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.