TAKE Function

Excel Functions › Lookup & Reference

Excel 365 Excel 2024+ Dynamic Array

The Excel TAKE function slices an array down to just its first — or last — rows and columns. Positive numbers count from the start, negatives count from the end, so =TAKE(data, 5) is the first five rows and =TAKE(data, -1) is the last row, no matter how long the data grows. Wrapped around SORT, it’s the cleanest top-N formula Excel has ever had.

Quick answer: to get the top 3 rows of a table sorted by its 3rd column:

=TAKE(SORT(A2:C7, 3, -1), 3)

SORT puts the biggest values first; TAKE keeps the first 3 rows and discards the rest. The result spills and updates itself as the data changes.

Syntax

=TAKE(array, rows, [columns])

Argument		Description
`array`	Required	The range or array to slice from.
`rows`	Required*	How many rows to keep. Positive = from the top, negative = from the bottom. Omit (leave the comma) to keep all rows.
`columns`	Optional	How many columns to keep. Positive = from the left, negative = from the right. Omitted = all columns.

*At least one of rows / columns must be supplied.

Available in: Excel for Microsoft 365, Excel 2024+, and Excel for the web. Older versions show #NAME?. The result is a dynamic array that spills onto the sheet.

Keep the first rows — or the last

The sales table sits in A2:C6 (headers in row 1). One formula in E1 keeps the first three rows:

	A	B	C	E	F	G
1	Rep	Region	Sales	Avery	North	9,400
2	Avery	North	9,400	Blake	South	8,750
3	Blake	South	8,750	Casey	East	7,900
4	Casey	East	7,900
5	Devon	West	7,300
6	Ellis	North	6,800

=TAKE(A2:C6, 3) // first 3 rows, all columns - spills into E1:G3

Negative counts work from the end, which is where TAKE gets really handy — “the most recent entry” in a log that grows daily:

=TAKE(A2:C100, -1) // the last row of the data

=TAKE(A2:C6, 2, -1) // first 2 rows, last column only: {9400; 8750}

Asking for more rows than exist is safe — =TAKE(A2:C6, 50) simply returns all 5 rows, no padding, no error.

Try it: interactive TAKE demo

Live demo

Slice the sales table live: set rows and columns (negatives count from the end) and watch the spill change.

rows

columns

Top-N lists and dynamic-array combos

TAKE was practically built to sit around SORT and SORTBY:

=TAKE(SORT(A2:C100, 3, -1), 5) // top 5 by sales

=TAKE(SORTBY(A2:A100, C2:C100, -1), 3) // just the 3 best rep names

Grab headers from a spilled result, or trim a running total to the latest figures:

=TAKE(E1#, 1) // first row of a spill - its header line

=TAKE(FILTER(A2:C100, B2:B100="North"), -3) // the 3 most recent North rows

TAKE’s mirror image is DROP, which removes rows instead of keeping them — =DROP(data, 1) “everything but the header” is the classic. For picking specific rows or columns by position, see CHOOSEROWS and CHOOSECOLS.

Errors & common pitfalls

#CALC! — you asked for zero. rows or columns of 0 returns an empty array, which Excel can’t display. Watch for this when the count comes from a formula that can hit zero — guard it with MAX(1, n) or an IF.

#SPILL! — the landing zone isn’t empty. The sliced array needs blank cells to spill into. Clear whatever is blocking, or slice smaller.

#NAME? — older Excel. TAKE requires Microsoft 365 or Excel 2024+. In Excel 2021 and earlier the name isn’t recognized — the closest classic substitute is INDEX with row/column math.

Pitfall: TAKE doesn’t pick the biggest values — it picks the first rows. =TAKE(data, 5) on unsorted data is just “the top of the sheet.” For a real top-5, sort first: =TAKE(SORT(data, 3, -1), 5).

Pitfall: headers included in the array. If your range starts at row 1, TAKE counts the header as row one of the slice. Start the array at the first data row, or peel headers off with DROP first.

Practice workbook

📊

Download the free TAKE practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What's the difference between TAKE and DROP?

Two sides of one coin. TAKE keeps a number of rows/columns from the edge of an array; DROP removes them. =TAKE(data, 3) keeps the first 3 rows; =DROP(data, 3) returns everything after them.

How do I get the last row of a range with TAKE?

Use a negative count: =TAKE(A2:C100, -1) returns the bottom row. -2 returns the last two rows, and =TAKE(data, -1, -1) returns just the bottom-right cell.

How do I build a top-5 list with TAKE?

Sort first, then slice: =TAKE(SORT(A2:C100, 3, -1), 5). SORT puts the largest values in the 3rd column first; TAKE keeps the first five rows of that result.

What happens if I ask TAKE for more rows than the array has?

Nothing bad - it returns the whole array. TAKE never pads with blanks or errors when the request exceeds the data; only a count of 0 causes a #CALC! error.

Which Excel versions support TAKE?

Excel for Microsoft 365, Excel 2024 and later, and Excel for the web. Excel 2021, 2019, and earlier show #NAME?. There is no direct equivalent in older versions short of INDEX gymnastics.

Can TAKE keep rows and columns at the same time?

Yes: =TAKE(data, 3, 2) keeps the first 3 rows of the first 2 columns, and the signs work independently - =TAKE(data, 3, -1) is the first 3 rows of the last column.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: DROP · CHOOSEROWS · CHOOSECOLS · SORT · EXPAND