Excel TAKE & DROP Functions — Slice Rows and Columns Off an Array

TL;DR — TAKE keeps rows or columns from the edge of an array; DROP removes them. Syntax: =TAKE(array, rows, [columns]) and =DROP(array, rows, [columns]). A positive count works from the top/left, a negative count from the bottom/right: TAKE(data, 5) is the first five rows, TAKE(data, -5) the last five, DROP(data, 1) strips the header row. Pair TAKE with SORT and you have a live "Top 10" in one formula. Excel 365 and 2021+.

=TAKE(Sales, 10)                     ' first 10 rows
=DROP(Sales, 1)                      ' everything except the header row

Pulling "the top 10" or "everything but the header" used to mean a manual selection that broke the moment the data changed, or a tangle of INDEX, LARGE and ROW. TAKE and DROP slice an array from its edges in one argument, and because they're formulas the slice stays live.

What you'll learn

• TAKE (keep N) vs DROP (remove N) — and the positive/negative direction trick
• A live Top 10 with TAKE(SORT(...))
• Stripping header rows off a stacked table with DROP
• Slicing columns, not just rows, with the third argument
• When these replace LARGE/SMALL/INDEX gymnastics

The mental model: a slice off the edge

Both functions work from an edge of the array, never the middle. TAKE keeps the slice; DROP throws it away — they're complements. The sign of the count picks the edge: positive counts from the top (or left), negative from the bottom (or right). That single rule covers "first N," "last N," "all but the first," and "all but the last":

=TAKE(data, 3)       ' first 3 rows
=TAKE(data, -3)      ' last 3 rows
=DROP(data, 3)       ' everything after the first 3 rows
=DROP(data, -3)      ' everything except the last 3 rows

The rule that unlocks everything: rows first, then columns, sign picks the edge

The second argument is rows, the optional third is columns. Leave the third out and you slice whole rows; supply it and you also slice columns — and each takes its own sign:

=TAKE(data, 5, 2)    ' first 5 rows AND first 2 columns
=TAKE(data, -5)      ' last 5 rows, all columns
=DROP(data, 0, -1)   ' all rows, drop the last column (0 rows means "no row slice")

DROP(data, 0, -1) is the tidy way to lop off a trailing column — 0 rows means "don't slice rows," -1 columns means "drop the last one." Once the rows-then- columns order and the sign rule click, any edge slice is one short formula.

The live Top 10 — TAKE around SORT

This is the pairing that makes TAKE indispensable. Sort the data, then take the first N — and the whole thing re-runs when the data changes, so your leaderboard is always current:

=TAKE(SORT(Sales, 3, -1), 10)        ' the 10 biggest sales, live
=TAKE(SORT(Sales, 3, -1), 10, 2)     ' top 10, and only the first 2 columns

No LARGE + INDEX + MATCH, no helper column, no manual re-sort. This single expression replaces the most common "ranking" pattern people still copy from forums.

Strip the header off a stack

DROP is the partner of VSTACK when you combine tables. Each source carries a header row, so dropping all but the first keeps the merged stack clean:

=VSTACK(JanTable, DROP(FebTable, 1), DROP(MarTable, 1))   ' one header, all data
=DROP(WholeTable, 1)                                      ' just remove the header

DROP(range, 1) removes the top row; DROP(range, -1) removes a totals row at the bottom. This is how you feed a clean, header-free array into SUM, FILTER or a chart.

The judgment call: when TAKE/DROP replace the old gymnastics

If you're writing INDEX(SORT(...), SEQUENCE(10)) or stacking LARGE with ROW() to pull a top-N, stop — TAKE(SORT(...), 10) says it directly and reads in one line. If you're selecting "everything but row 1" by hand, DROP(data, 1) makes it live. The honest exception: TAKE/DROP only slice from the edges, so for "rows 20–40 in the middle" you still want FILTER with a condition or INDEX. For edge slices and rankings, TAKE and DROP win.

How ExcelMaster helps

Most of these requests are really "show me the top few, kept current" or "give me the data without the header/total rows." ExcelMaster writes the TAKE(SORT(...)) for a live ranking, the DROP to strip headers off a VSTACK, with the right sign for top-vs-bottom and the rows-then-columns order correct — all from a plain-English description. You say "top 10 by revenue"; it builds the slice.

Frequently asked questions

How do I get the top N rows with a formula in Excel?

Sort, then take: =TAKE(SORT(data, 3, -1), 10) returns the 10 rows with the largest value in column 3, and it updates live when the data changes.

How do I remove the header row with a formula?

Use =DROP(data, 1) — it drops the first row and spills everything below. To remove a totals row at the bottom instead, use =DROP(data, -1).

What's the difference between TAKE and DROP?

TAKE keeps the rows/columns you specify from the edge; DROP removes them. TAKE(data, 5) is the first five rows; DROP(data, 5) is everything except the first five.

How do positive and negative numbers work in TAKE?

A positive count works from the top (or left); a negative count from the bottom (or right). TAKE(data, -5) returns the last five rows; DROP(data, -1) removes the last row.

Do TAKE and DROP work in Excel 2016 or 2019?

No. TAKE and DROP require Excel 365 or Excel 2021+. On older versions you use INDEX with LARGE/SMALL or a helper column.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-17.

Related guides: Excel SEQUENCE · Excel VSTACK & HSTACK · Excel SORT · Excel FILTER