Every spreadsheet built after 2019 has the same tiny irritation: a dataset lands in Excel, and the first thing you want is just part of it — the last five rows, the top ten by revenue, everything except the header. For years that meant OFFSET, INDEX/COUNTA gymnastics, or copy-paste. Two functions clean that up. TAKE grabs a slice from the start or end of an array; DROP throws a slice away and returns the rest. Together they replace half a dozen legacy tricks with two arguments and a sign. If you’re on Microsoft 365, they’re already there — most tutorials just don’t show them past the first example.
What TAKE and DROP actually do
Both functions take three arguments and return a shape of the same array. =TAKE(array, rows, [columns]) keeps the specified count from the top or left. =DROP(array, rows, [columns]) removes that many and returns whatever is left. That’s the entire contract.
The signatures line up on purpose. Whatever TAKE keeps, DROP throws away with the same numbers. Skip an argument (leave the position empty with a comma) and that dimension is untouched. Pass a positive number and you’re working from the top or the left. Pass a negative one and you’re working from the bottom or the right.
Put a small sample in A1:C6 — a header row plus five data rows — and the difference is easy to see:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Revenue |
| 2 | Chair | East | 1200 |
| 3 | Desk | West | 2400 |
| 4 | Lamp | East | 300 |
| 5 | Sofa | North | 1800 |
| 6 | Rug | West | 600 |
=TAKE(A1:C6, 3) spills the header plus the first two rows. =DROP(A1:C6, 1) spills the same shape minus the header — five rows of pure data, no manual anchor cell needed. That single formula is the reason to learn DROP first.
Slicing from the end with negative numbers
The rows and columns arguments accept negatives, and this is where the two functions earn their keep. A positive count reads from the top or left; a negative count flips the direction. The formulas below assume the same A1:C6 layout from the previous section.
=TAKE(A2:C6, -3) // last three data rows
=DROP(A2:C6, -1) // everything except the last row
=TAKE(A1:C6, 1) // header row only
=DROP(A1:C6, 1) // data rows only, header strippedEach formula returns a spilled range starting at the cell you enter it in. No OFFSET, no COUNTA, no volatile recalculation on every keystroke. If the source table grows by ten rows tomorrow, =TAKE(A2:C1000, -3) still returns the true last three — as long as the underlying data is contiguous and ends before row 1000.
=TAKE(tblSales, -3), the range grows and shrinks automatically. No trailing empty rows, no maintenance.
Grabbing columns instead of rows
Skip the rows argument with an empty comma and both functions work column-wise. The syntax feels awkward the first time you type it — you leave a hole where the rows count would go — but the behavior is consistent with the rest of the dynamic array family.
=TAKE(A1:C6, , 1) // first column only
=TAKE(A1:C6, , -1) // last column only
=DROP(A1:C6, , 1) // drop the first column
=DROP(A1:C6, , -2) // drop the last two columnsThis is the pattern that replaces a fistful of legacy tricks. If you need only the Product and Revenue columns from a wider table, =DROP(A1:F100, , {-3,-2,-1}) doesn’t work — the arguments are scalars, not arrays. For non-contiguous column picks, reach for CHOOSECOLS and CHOOSEROWS instead; TAKE and DROP are for contiguous slices from either end.
Both arguments together let you crop in two dimensions in a single formula. =TAKE(A1:F100, 10, 3) returns the first ten rows of the first three columns as one spilled block. That’s a one-line dashboard preview from a raw data sheet.
Building a live top 10 with TAKE and SORT
The single most useful combination is TAKE wrapped around SORT. Sort the data by the metric that matters, then take the first N rows. The result is a live leaderboard that updates the second the source data changes.
=TAKE(SORT(A2:C1000, 3, -1), 10)SORT reorders the rows by column 3 (Revenue), descending. TAKE keeps the first ten. To flip it into a bottom-10, either use a positive sort order or pass -10 to TAKE. Both work; the negative-TAKE form reads more naturally when the sort direction should stay descending elsewhere in the sheet.
The pattern generalizes. Swap SORT for FILTER and you get “top 10 of a filtered subset” in one formula: =TAKE(SORT(FILTER(A2:C1000, B2:B1000="East"), 3, -1), 10). Read from the inside out — filter first, sort second, take third — and it’s still one formula, still spills, still recalculates only when the inputs change. For the FILTER half specifically, see the deeper walkthrough on FILTER with multiple criteria.
Extracting a middle slice by chaining DROP and TAKE
Neither function has a “start at row N” argument, and that’s fine because chaining them gives you the same thing. DROP the first N rows, then TAKE the next M. The wrapper reads left to right the way you’d describe it out loud.
=INDEX(A:C, SEQUENCE(5, 1, 11), SEQUENCE(1, 3))Fragile; hardcoded row 11 breaks when data shifts.
=TAKE(DROP(A2:C1000, 10), 5)Reads plainly: skip 10, take 5.
Reverse the order for a different behavior. =DROP(TAKE(A2:C1000, 20), 10) keeps rows 11 through 20 — take a window, then trim the top of it. The two chainings are not equivalent when the source has fewer rows than you’re asking for; the second form is bounded by the outer TAKE, which is often what you want when the data grows.
For pagination — showing rows 21–30, then 31–40, then 41–50 — the same shape works with the page number as an input. =TAKE(DROP(data, (page-1) * 10), 10) is one formula plus one cell for page. That’s a lightweight paged view without a pivot table.
Errors, edge cases, and what to guard against
TAKE and DROP have three failure modes worth remembering, and each raises a distinct error so you can tell them apart at a glance.
- ✓ #CALC! — you passed 0 for both rows and columns. Nothing to return.
- ✓ #NUM! — you asked for more than the array holds, in a context where that’s forbidden (rare; TAKE usually silently returns what exists).
- ✓ #VALUE! — the array argument isn’t an array (a single cell reference that isn’t a spill, an error value passed through).
The forgiving behavior is the interesting one. =TAKE(A2:A5, 100) returns four rows without complaint, because the request exceeds the input and TAKE clamps to what exists. That’s usually what you want, but it does mean a bug can hide: if =TAKE(data, 10) returns only three rows, the source has three rows, and the problem is upstream, not in the formula.
For anything user-facing, wrap the outer call in IFERROR or check the source first. =IFERROR(TAKE(SORT(data, 3, -1), 10), "no data") keeps a dashboard clean when a feeding query returns empty. Microsoft’s official TAKE reference is the source of truth for exact error conditions and version support.
_xlfn.-prefixed error cells.Where to start
Open the sheet you keep going back to most — the one where you’re always scrolling to the bottom for the latest rows, or copying the top ten into another tab. Replace one manual step with =TAKE or =DROP and see how it recalculates when new data arrives. The SORT + TAKE combo tends to earn its keep first, because a live top-N is what every stakeholder actually asks for. Chain a FILTER underneath when the subset gets specific.
- ✓ Strip the header from any dataset with
=DROP(range, 1) - ✓ Pull the latest N rows with
=TAKE(range, -N) - ✓ Build a live top 10 with
=TAKE(SORT(range, sort_col, -1), 10) - ✓ Extract a middle slice with
=TAKE(DROP(range, skip), size)
Everything else — pagination, cropping to a preview, chaining with FILTER — is the same two functions and a sign flip.