# DROP function in Excel

> DROP removes rows or columns from the start or end of a range and returns what is left.

Source: https://spreadsheetpractice.com/functions/drop · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** DROP removes rows or columns from the start or end of a range and returns what is left.
- **Syntax:** `=DROP(array, rows, [columns])`
- **Most common use:** `=DROP(A1:C6,1)` returns North (Removes the first row of the range, the header)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** What is left of array after the dropped rows and columns. It spills.

## What it does

DROP removes rows or columns from the start or end of a range and returns what is left. It is the quick way to strip a header row or a total row from imported data before you sort, filter or total it.

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to drop from. |
| `rows` | Required | How many rows to remove. A positive number drops from the top, a negative one from the bottom. |
| `columns` | Optional | How many columns to remove, counted the same way. Leave it out to keep every column. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=DROP(A1:C6,1)` | North (spills 5 × 3) | Removes the first row of the range, the header |
| I2 | `=DROP(A2:C6,-1)` | North (spills 4 × 3) | A negative count drops from the bottom: the Total row is gone |
| M2 | `=SUM(DROP(B2:B6,-1))` | 445 | Totals Q1 without counting the Total row twice |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 |  | Without the header |  |  |  | Without the total row |  |  |  | Q1 from the rows only |
| 2 | North | 120 | 135 |  | North | 120 | 135 |  | North | 120 | 135 |  | 445 |
| 3 | South | 95 | 110 |  | South | 95 | 110 |  | South | 95 | 110 |  |  |
| 4 | East | 150 | 160 |  | East | 150 | 160 |  | East | 150 | 160 |  |  |
| 5 | West | 80 | 90 |  | West | 80 | 90 |  | West | 80 | 90 |  |  |
| 6 | Total | 445 | 495 |  | Total | 445 | 495 |  |  |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #CALC! | Every row or column was dropped, so nothing is left. Check the count, especially when it comes from another formula. |
| Off by one | DROP counts how many rows to remove, not which row to start at. To start at the third row, drop 2. |
| #NAME? | DROP needs Excel 2024 or Microsoft 365. |

## Tips

- Drop columns as well as rows: =DROP(A2:E20,,1) removes the first column and keeps every row.
- Chain it: =DROP(DROP(A1:C20,1),-1) removes both the header and a total row.

## Related functions

- [TAKE](https://spreadsheetpractice.com/functions/take): TAKE keeps the first or last rows (and columns) of a range and drops the rest.
- [FILTER](https://spreadsheetpractice.com/functions/filter): FILTER returns only the rows of a range that pass a test, such as every order from the West region.
- [VSTACK](https://spreadsheetpractice.com/functions/vstack): VSTACK stacks ranges on top of each other into one list, such as this month's orders under last month's or one region's reps under another's.

## Questions

### How do I remove the header row in a formula?

=DROP(A1:C20,1) returns everything except the first row.

### What is the difference between TAKE and DROP?

TAKE keeps the rows you name; DROP removes them and keeps the rest. TAKE(A2:C9,3) and DROP(A2:C9,3) split the same table into two parts.

## Practice

A graded DROP exercise ("Lose the total row") is on [the DROP page](https://spreadsheetpractice.com/functions/drop). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
