TAKE and DROP Functions in Excel: Slice Arrays and Ranges Like a Pro

Home » pub » Tutorials » TAKE and DROP Functions in Excel: Slice Arrays and Ranges Like a Pro

FILTER returns everything that matches. SORT returns everything reordered. But sometimes you only want the first five rows, or everything except the last three, or a specific middle segment. TAKE and DROP fill this gap in Excel 365. TAKE keeps a specified number of rows or columns from either end of an array. DROP removes a specified number and returns the rest. Negative numbers work from the bottom, so you never need to know the total row count. This guide covers six practical examples: first and last N rows with positive and negative arguments, stripping header and footer rows with DROP, extracting a middle slice by chaining DROP and TAKE, two-dimensional slicing with both rows and columns arguments, a SORT+FILTER+TAKE pipeline for a self-updating top-5 leaderboard, and in-worksheet pagination where a page number cell controls which block of rows is displayed. It also covers the pre-TAKE workarounds using INDEX and SEQUENCE, so you can understand what these two functions replace and choose the right approach for your Excel version.

FILTER returns everything that matches. SORT returns everything reordered. But sometimes you only want the first five rows, or everything except the last three, or a specific middle slice. Before Excel 365 introduced TAKE and DROP, extracting a partial array required OFFSET, INDEX, or a complex SEQUENCE-based construction. Now two functions handle every slicing scenario cleanly. TAKE returns a specified number of rows or columns from the beginning or end of an array. DROP removes a specified number from either end and returns the rest.

This guide covers both functions in full, the negative-number shortcut for working from the end of an array, and six practical examples. You will learn how to extract top-N and bottom-N rows, remove headers or footers from a dynamic array, combine TAKE and DROP for a middle slice, and chain these functions with SORT, FILTER, and UNIQUE for precise array control. Each example also shows the pre-TAKE workaround so you can see exactly what TAKE replaces.

Availability: TAKE and DROP are available in Excel 365 only (specifically from the version released in March 2022 onward). They are not available in Excel 2021, 2019, or Google Sheets. For older versions, INDEX with SEQUENCE or OFFSET provides similar behaviour but requires more complex formulas. Both functions spill automatically and do not require Ctrl+Shift+Enter.

What Are TAKE and DROP?

TAKE and DROP are complementary array-slicing functions. TAKE keeps a portion of an array. DROP removes a portion and returns the remainder. Together they cover every scenario where you need a subset of an array rather than the whole thing. Positive row/column numbers work from the top-left corner. Negative numbers work from the bottom-right corner. This negative-number shortcut is the key to extracting the last N rows without knowing the total length of the array.

Both functions accept two-dimensional arrays, which means they work on tables as well as single columns. Specifying both rows and columns lets you extract a rectangular sub-table from a larger range. Omitting the columns argument slices by row only and returns all columns. Similarly, omitting the rows argument slices by column only.

What Is the TAKE and DROP Syntax?

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

Argument	TAKE behaviour	DROP behaviour
array	The source array or range. Can be a cell range, named range, or the result of any spilling function.
rows (positive)	Keep the first n rows	Remove the first n rows, return the rest
rows (negative)	Keep the last n rows	Remove the last n rows, return the rest
columns (positive)	Keep the first n columns	Remove the first n columns, return the rest
columns (negative)	Keep the last n columns	Remove the last n columns, return the rest
rows = 0	Returns empty (use with caution)	Returns entire array (removes nothing)

Negative numbers = work from the end: TAKE(-3, A2:A100) returns the last 3 rows. DROP(-3, A2:A100) removes the last 3 rows and returns everything else. This means you never need to know the total number of rows — negative numbers always count from the bottom, regardless of array length. Similarly, negative column arguments work from the right-hand side of a multi-column array.

How Do TAKE and DROP Compare to Older Methods?

Before TAKE and DROP, extracting a slice required INDEX with SEQUENCE or OFFSET — both verbose and fragile with dynamic arrays. The table below shows each slicing scenario and the old vs new formula approach.

Scenario	Old approach	TAKE / DROP approach
First 5 rows	=INDEX(A:A, SEQUENCE(5))	=TAKE(A2:A100, 5)
Last 5 rows	=INDEX(A:A, ROWS(A2:A100)-4+SEQUENCE(5))	=TAKE(A2:A100, -5)
Skip first row	=INDEX(A:A, SEQUENCE(ROWS(A2:A100)-1,,2))	=DROP(A2:A100, 1)
Skip last row	=INDEX(A:A, SEQUENCE(ROWS(A2:A100)-1))	=DROP(A2:A100, -1)
Middle slice (rows 3–7)	=INDEX(A:A, SEQUENCE(5,,3))	=TAKE(DROP(A2:A100, 2), 5)

Examples 1–4: Core TAKE and DROP Patterns

TAKE — first N and last N rows from an array

The most common use is extracting a fixed number of rows from the top or bottom of a data column. TAKE with a positive number returns from the top. TAKE with a negative number returns from the bottom. Both work identically whether the source is a static range or the spilled result of SORT or FILTER.

Source (A2:A10)

TAKE(A,3)

TAKE(A,-3)

Meaning

Apple

—

Row 1 of 9

Banana

—

Row 2 of 9

Cherry

Row 3 of 9

⋮ (rows 4–6)

—

Not included

Grape

—

Grape

Row 7 of 9

Kiwi

—

Kiwi

Row 8 of 9

Lemon

—

Lemon

Row 9 of 9

A2:A10 = 9 fruit names. First 3 rows (positive n = from the top): =TAKE(A2:A10, 3) → Spills {Apple; Banana; Cherry} Last 3 rows (negative n = from the bottom): =TAKE(A2:A10, -3) → Spills {Grape; Kiwi; Lemon} (last 3 regardless of total length) TAKE on a sorted array — top 3 products by revenue: A2:A100 = product names. B2:B100 = revenue. Sort descending then take top 3. =TAKE( SORT(FILTER(A2:B100, B2:B100>0), 2, -1), 3 ) → Top 3 rows (highest revenue first) from the filtered, sorted table Dynamic top-N — where N is controlled by a cell value: D1 = number of rows to take (user-adjustable input cell). =TAKE(SORT(A2:B100, 2, -1), $D$1) → Returns top-N rows; change D1 to adjust N without editing the formula

DROP — remove headers, footers, and unwanted rows

DROP is particularly useful when a dynamic array includes rows you want to exclude. The most common case is removing a header row from a spilled result. Another frequent use is stripping a running total or grand total row that appears at the bottom of a dynamic output. DROP handles both without requiring you to know the total row count.

A1:B20 = table with headers in row 1 and data in rows 2–20. Remove the first row (header) from an array: DROP with a positive 1 removes row 1 and returns rows 2 onward. =DROP(A1:B20, 1) → Returns rows 2–20 (all columns). Headers stripped. Remove the last row (grand total or overflow): DROP with -1 removes the last row regardless of total length. =DROP(A1:B20, -1) → Returns rows 1–19 (removes row 20) Remove both first and last rows — chain two DROP calls: First DROP removes the header. Second DROP removes the final total. =DROP(=DROP(A1:B20, 1), -1) → Returns rows 2–19 (header and last row both removed) Remove first column (e.g. an index or ID column you want to exclude): DROP with rows = 0 and columns = 1 removes only the first column. =DROP(A2:D50, 0, 1) → Returns B2:D50 (columns B, C, D — column A dropped) Remove the FREQUENCY overflow row — DROP(-1) on FREQUENCY output: FREQUENCY always returns n+1 rows. Drop the last to remove the overflow. =DROP(FREQUENCY($A$2:$A$101, $D$2:$D$5), -1) → FREQUENCY counts without the overflow row

Middle slice — combine TAKE and DROP to extract any interior segment

Neither TAKE nor DROP alone can extract a middle segment — rows 4 through 8 from a 20-row array, for example. Chaining the two functions solves this. First, DROP removes the rows before the desired segment. Then TAKE keeps only as many rows as needed from the remaining array. This pattern replaces the old INDEX+SEQUENCE middle-slice formula with a clean, readable two-function chain.

A2:A20 = 19 values. Goal: extract rows 4–8 (5 rows starting at row 4). Step 1: DROP the first 3 rows (rows 1–3 of the array): This leaves rows 4 onward — i.e. the segment start becomes the new first row. =DROP(A2:A20, 3) → Returns rows 4–20 of the source array (17 rows) Step 2: TAKE the first 5 rows of that result: Now rows 4–8 of the original are the first 5 rows of the dropped result. =TAKE(=DROP(A2:A20, 3), 5) → Returns rows 4–8 of A2:A20 (5 values) General formula: extract n rows starting at row s (1-indexed): DROP(array, s-1) removes the rows before the start. TAKE(..., n) keeps the desired number of rows. Here s=4, n=5: DROP(array, 3) then TAKE(..., 5). =TAKE(=DROP(A2:A20, D1-1), D2) → D1 = start row (1-indexed), D2 = number of rows to extract Change D1 and D2 to slice any segment dynamically Equivalent old formula (INDEX + SEQUENCE — more complex): Both return the same result. TAKE+DROP is far more readable. {=INDEX(A2:A20, SEQUENCE(5,,3))}

2D slicing — extract a rectangular sub-table using rows and columns

TAKE and DROP both accept a columns argument as well as rows. This enables two-dimensional slicing — extracting a rectangular block from a larger table. Specifying both rows and columns lets you return exactly the columns you need from the top N rows, or remove the first column while also removing the last row. This is particularly useful for reshaping structured table outputs from FILTER or SORT.

A2:D20 = a 4-column, 19-row data table. Columns: Name, Dept, Score, Date. Take the first 5 rows and first 2 columns (Name and Dept only): TAKE with rows=5 and columns=2. =TAKE(A2:D20, 5, 2) → Returns A2:B6 — first 5 rows, columns 1 and 2 only Take the last 3 rows and last 2 columns (Score and Date from bottom 3 rows): Negative n = from the end. =TAKE(A2:D20, -3, -2) → Returns C18:D20 — last 3 rows, last 2 columns Drop the first row and last column (remove header and Date column): Drop 1 row and -1 column simultaneously. =DROP(A2:D20, 1, -1) → Returns A3:C20 — all rows except first, all columns except last Extract columns 2 and 3 only from the full table (Dept and Score): DROP the first column, then TAKE the first 2 columns of the result. =TAKE(=DROP(A2:D20, 0, 1), 0, 2) → Returns B2:C20 (Dept and Score columns only)

Examples 5 and 6: Applied TAKE and DROP

Pipeline Patterns and Dynamic Reports

Chaining with SORT and FILTER — top-N leaderboard that updates automatically

TAKE becomes most powerful when chained at the end of a FILTER and SORT pipeline. The pattern is: FILTER to select the relevant rows, SORT to put them in the desired order, and TAKE to limit the output to exactly the number of rows you want to display. This produces a self-updating leaderboard or top-N report that responds to data changes without any manual adjustment.

A2:A100 = sales rep names. B2:B100 = regions. C2:C100 = revenue. Top 5 North region reps by revenue — three-function pipeline: 1. FILTER keeps only North rows. 2. SORT orders by revenue column descending. 3. TAKE returns only the first 5 rows of the sorted result. =TAKE( SORT( FILTER(A2:C100, B2:B100="North"), 3, -1 ), 5 ) → Top 5 North reps with all three columns (Name, Region, Revenue) Same with configurable N from a cell (D1 = number of rows to show): Change D1 to show a different number of results without editing the formula. =TAKE( SORT(FILTER(A2:C100, B2:B100="North"), 3, -1), $D$1 ) Bottom-N — lowest performers (worst 3): Use TAKE with -3 to take from the BOTTOM of the sorted result. Sort ascending (sort_order = 1) then take last 3. =TAKE( SORT(FILTER(A2:C100, B2:B100="North"), 3, -1), -3 ) → Bottom 3 reps (lowest revenue) from the sorted-descending result

Pagination — show rows 11–20 of a sorted list on a second page

TAKE and DROP together enable in-worksheet pagination. A page number input cell controls which set of N rows is displayed. Page 1 shows rows 1–10, page 2 shows rows 11–20, and so on. This pattern is useful for dashboards where space is limited and results need to be browsed in chunks rather than displayed all at once.

Sorted data in F2:H200 (198 rows after headers). D1 = page number (1, 2, 3...). D2 = page size (10 rows per page). Page formula — show 10 rows for the current page: DROP skips the rows from previous pages. TAKE then keeps the next 10 rows. Rows to skip = (page - 1) × page_size. Page 1: skip 0, take 10. Page 2: skip 10, take 10. Etc. =TAKE( =DROP(F2:H200, (D1-1)*D2), D2 ) → Page 1 (D1=1): rows 1–10 Page 2 (D1=2): rows 11–20 Page 3 (D1=3): rows 21–30 (change D1 to browse pages) Page count label — "Page 2 of 20": ROWS counts the full dataset. CEILING rounds up to the nearest page. "Page " & D1 & " of " & CEILING(ROWS(F2:H200) / D2, 1) → "Page 2 of 20" (updates as D1 and data change) Guard for the last page — prevent TAKE requesting more rows than exist: IFERROR handles the case when the last page has fewer than D2 rows. IFERROR( =TAKE(=DROP(F2:H200, (D1-1)*D2), D2), =DROP(F2:H200, (D1-1)*D2) ) → Shows all remaining rows on the final page (even if fewer than D2)

Common Issues and How to Fix Them

TAKE or DROP is not available in my Excel version

TAKE and DROP require Excel 365, specifically the March 2022 channel release or later. They are not in Excel 2021, 2019, or Google Sheets. To check your version, go to File → Account → About Excel. In older versions, use =INDEX(array, SEQUENCE(n)) for TAKE and =INDEX(array, SEQUENCE(ROWS(array)-n,,n+1)) for a DROP equivalent. Both require Ctrl+Shift+Enter in Excel 2019 and earlier.

TAKE returns a #CALC! error

#CALC! from TAKE means the rows or columns argument exceeds the size of the array. For example, TAKE(A2:A5, 10) asks for 10 rows from a 4-row array. TAKE does not truncate silently — it errors. Consequently, wrap the n argument with MIN to cap it at the actual array size: =TAKE(A2:A100, MIN(5, ROWS(A2:A100))). This returns up to 5 rows and fewer if the array is smaller.

DROP with rows = 0 behaves unexpectedly

Passing rows = 0 to DROP returns the entire array unchanged — it removes nothing. This is the intended behaviour and is useful when the skip amount is calculated dynamically and happens to evaluate to zero. However, passing rows = 0 to TAKE returns an empty array, which causes a #CALC! error in most contexts. Always test the formula when a dynamic n argument might evaluate to zero, and add an IF guard if needed: =IF(n=0, array, TAKE(array, n)).

TAKE and DROP are Excel 365 only: These functions are not available in Excel 2021 despite being released close in time. They specifically require the Microsoft 365 subscription channel. If you share workbooks with users on Excel 2021, 2019, or earlier, those users will see a #NAME? error. In shared environments, build a fallback using INDEX and SEQUENCE, or restrict TAKE and DROP to personal analysis workbooks that do not leave your machine.

Frequently Asked Questions

What does the TAKE function do in Excel?+

TAKE extracts a specified number of rows or columns from the beginning or end of an array. Pass a positive number to take from the top or left. Pass a negative number to take from the bottom or right. For example, =TAKE(A2:A100, 5) returns the first 5 rows, and =TAKE(A2:A100, -5) returns the last 5 rows. TAKE can also work on two dimensions: =TAKE(A2:D100, 5, 2) returns the first 5 rows and first 2 columns. The function is available in Excel 365 from March 2022 onward. It spills automatically and does not require array entry.
What does the DROP function do in Excel?+

DROP removes a specified number of rows or columns from an array and returns the remainder. A positive number removes from the top or left. A negative number removes from the bottom or right. For example, =DROP(A2:A100, 3) removes the first 3 rows and returns rows 4–100, and =DROP(A2:A100, -1) removes the last row and returns everything else. This is the complement of TAKE — where TAKE keeps a portion, DROP discards a portion. Combining TAKE and DROP enables middle-slice extraction and pagination patterns.
How do I extract the last N rows without knowing the total row count?+

Use TAKE with a negative number: =TAKE(A2:A100, -5) returns the last 5 rows regardless of how many total rows are in the array. The negative sign means "count from the bottom." This works even when the array is the result of a FILTER or SORT that returns a variable number of rows — you always get the last N rows of whatever the dynamic result contains. Similarly, =DROP(A2:A100, -3) removes the last 3 rows and returns everything else, again without needing to know the total count.