DROP function
Quick answer
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)
It is the quick way to strip a header row or a total row from imported data before you sort, filter or total it.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- What is left of array after the dropped rows and columns. It spills.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =DROP(A1:C6,1) | Northspills 5 × 3 | Removes the first row of the range, the header |
| I2 | =DROP(A2:C6,-1) | Northspills 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Region | Q1 | Q2 | Without the header | Without the total row | Q1 from the rows only | |||||||
| North | 120 | 135 | North | 120 | 135 | North | 120 | 135 | 445 | |||
| South | 95 | 110 | South | 95 | 110 | South | 95 | 110 | ||||
| East | 150 | 160 | East | 150 | 160 | East | 150 | 160 | ||||
| West | 80 | 90 | West | 80 | 90 | West | 80 | 90 | ||||
| Total | 445 | 495 | Total | 445 | 495 |
Practice DROP
Graded instantly, then re-checked on changed data.
Lose the total row
E2, return the table A2:C8 without its last row, the Total row.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.
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.
In other languages
DROP is EXCLUIR in Spanish Excel and WEGLASSEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .