DROP Function
Removes rows or columns from the start or end of an array.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2024
Returns
A spilled array
Overview
DROP trims a block of data. Tell it how many rows (and optionally columns) to remove, and it spills whatever is left.
Positive numbers drop from the top or left; negative numbers drop from the bottom or right. It's the opposite of TAKE.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to trim. |
| rows | Yes | Rows to remove. Positive = from the top, negative = from the bottom, 0 = none. |
| columns | Optional | Columns to remove. Positive = from the left, negative = from the right. |
Examples
Example 1 — Removing a header row
We want the data without its headers so we can feed it into another formula.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Pens | 12 | |
| 2 | Pens | 12 | Pads | 8 | |
| 3 | Pads | 8 | Ink | 3 | |
| 4 | Ink | 3 |
Example 2 — Dropping a total row and an ID column
Our report ends in a total row and starts with an ID column we don't need.
-1 removes the last row (the total) and 1 removes the first column (the ID).
Common mistakes
- #CALC! errors. You dropped every row or column, leaving an empty result.
- Mixing up DROP and TAKE. DROP removes what you specify; TAKE keeps what you specify.
Notes
- Related functions: TAKE, CHOOSEROWS, CHOOSECOLS, VSTACK.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.