DROP function
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.
- 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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.