The Excel DROP function returns an array with rows or columns sliced off the edges — the mirror image of TAKE. Positive counts remove from the start, negatives remove from the end, so =DROP(data, 1) peels off a header row and =DROP(data, -1) discards a totals row at the bottom. It’s the tidiest way to feed clean data into other dynamic array functions.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to slice from. |
rows | Required* | How many rows to remove. Positive = from the top, negative = from the bottom. Omit (leave the comma) to remove none. |
columns | Optional | How many columns to remove. Positive = from the left, negative = from the right. Omitted = none. |
*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.
Remove header rows, totals rows, and edge columns
The export in A1:C5 arrives with a header row baked in. One DROP in E1 returns just the data:
| 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 | Devon | West | 7,300 | |
| 5 | Devon | West | 7,300 |
Negative counts trim from the far edge — perfect for system exports that append a totals row, or for cutting an unwanted ID column:
Removing more rows than the array has leaves nothing to return — that’s a #CALC! error, unlike TAKE which just caps at the data size.
Try it: interactive DROP demo
Slice the export table live: choose how many rows and columns to remove (negatives trim from the end).
DROP in dynamic-array pipelines
DROP’s signature move is cleaning data inside a formula so the rest of the pipeline never sees the junk:
Combine positive and negative trims to cut both ends of a messy export at once:
And the classic pairing — TAKE for the header, DROP for the body — lets you process each part separately, then reassemble with VSTACK:
Excel 365 tip: for trimming blank outer rows and columns rather than a fixed count, see TRIMRANGE.
Errors & common pitfalls
#CALC! — nothing left. Dropping as many (or more) rows or columns as the array contains returns an empty array. If the row count is dynamic — say COUNTA-driven — guard against the data being shorter than the trim.
#SPILL! — the landing zone isn’t empty. The trimmed array still needs room to spill. Clear the blocking cells.
#NAME? — older Excel. DROP requires Microsoft 365 or Excel 2024+. Earlier versions don’t recognize the name; the old-school workaround is OFFSET or INDEX arithmetic.
Pitfall: DROP removes by position, not by content. =DROP(data, -1) removes the last row whether or not it’s actually a totals row. If the export sometimes omits totals, you’ll silently lose a data row — test the layout first.
Pitfall: zero is fine here, unlike TAKE. =DROP(data, 0) just returns the whole array — handy when the trim count is calculated. It’s dropping everything that errors, not dropping nothing.
Practice workbook
Frequently asked questions
What's the difference between DROP and TAKE?
=DROP(data, 1) and =TAKE(data, -(ROWS(data)-1)) are the same thing — DROP just says it better.How do I remove a header row from a range?
How do I remove the last row (a totals row) with DROP?
Why does DROP return #CALC!?
Which Excel versions support DROP?
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