TAKE Function in Excel (and DROP): First or Last N Rows

The TAKE function in Excel returns the first or last N rows or columns of a range, and its partner DROP removes them. =TAKE(A2:C8,3) gives the first three rows; =TAKE(A2:C8,-3) gives the last three. Both are dynamic array functions in Microsoft 365 and Excel 2024. This guide shows the syntax, the top N pattern with SORT, and a FILTER version for Excel 2021.

TAKE and DROP syntax#

TAKE keeps rows or columns from one end of an array; DROP removes them.

=TAKE(array, rows, [columns])
=DROP(array, rows, [columns])

To work on columns only, leave rows empty: =TAKE(A2:C8,,1) returns just the first column. Both functions spill: the result fills as many cells as it needs, starting from the formula cell. If anything is in the way, you get #SPILL!. The guide to dynamic arrays in Excel explains spilling.

TAKE and DROP need Microsoft 365, Excel 2024 or Excel for the web. In Excel 2021 and earlier they show #NAME?, so use the FILTER version further down if you share files.

TAKE function examples#

All examples use this sales list in A1:C8:

Asking for 0 rows or 0 columns returns #CALC!, Excel's error for an empty result.

Make a top N list with TAKE and SORT#

The most useful TAKE formula sorts first, then keeps the top rows. To list the three best sellers:

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

SORT orders the rows by column 3 (Sales), largest first (-1). TAKE keeps the first three: Dev 72300, Finn 66800 and Ben 61500. Change the 3 to a cell like F1 and your reader can pick how many rows to show. For the bottom three, use -3 in TAKE, or sort ascending.

SORT and FILTER are covered in the guide to SORT and SORTBY, the SORT function guide, and the dedicated article on building a top N list, which also handles ties.

Top N within a group#

Filter first, then sort, then take. For the top two reps in the East region:

=TAKE(SORT(FILTER(A2:C8,B2:B8="East"),3,-1),2)

FILTER keeps Ava, Cara and Finn. SORT puts Finn (66800) first, then Ava (48200). TAKE keeps those two.

How to use DROP to remove rows#

DROP is TAKE in reverse: you say what to remove. Three everyday uses:

DROP is handy when another function returns more than you need, for example a SORT result that includes a header or a total. If you drop every row, the result is #CALC!.

Get the latest N entries from a growing list#

A log where new rows are added at the bottom is a natural fit for a negative TAKE. The trick is to ignore empty rows below the data, otherwise TAKE returns blanks:

=TAKE(FILTER(A2:C500,A2:A500<>""),-3)

FILTER keeps only rows where column A has a value, and TAKE returns the last three of those. If your data is an Excel table, use the table's column names instead, and the range grows on its own.

Total or average the last N values#

TAKE doesn't have to spill. Put it inside another function to work on just the rows it returns. On the sales list, this adds up the three most recent entries:

=SUM(TAKE(C2:C8,-3))

The last three sales are Ella (55000), Finn (66800) and Gia (44100), so the result is 165900. Swap SUM for AVERAGE and you get a rolling average of the last three: 55300. If you point it at an Excel table column instead of a fixed range, both numbers move forward by one row each time a new row is added. That makes this a simple way to track recent performance without rewriting the formula each week.

TAKE alternatives for Excel 2021#

Excel 2021 has FILTER, SORT and SEQUENCE, which together can copy TAKE. SEQUENCE numbers the rows 1, 2, 3 and so on, and FILTER keeps the ones you want:

ROWS counts the rows in the range (7 here). The FILTER function guide, the article on the FILTER function and the guide to SEQUENCE explain each piece. INDEX works too: =INDEX(A2:C8,SEQUENCE(3),SEQUENCE(1,3)) returns the first three rows and all three columns.

Common TAKE mistakes#

Watch for these when a TAKE formula misbehaves:

1. #NAME? The file is open in a version without TAKE. Use the FILTER version.
2. #SPILL! Cells below or to the right of the formula aren't empty. Clear them.
3. Blank rows in the result. The range runs past the data. Wrap it in FILTER to drop empty rows.
4. Wrong end of the list. Positive numbers count from the top or left, negative from the bottom or right.
5. Header in the result. Start the range below the header, or wrap it in DROP(…,1).

Practise it#

TAKE is mostly SORT and FILTER with a shortcut on top, so those are the skills to build. Practise the SORT function in a live spreadsheet: you sort by any column and direction, and each formula is checked instantly. Then practise the FILTER function, and try the exercise on this page to build a top 3 list that works even without TAKE.