DROP removes a specified number of rows or columns from the start or end of an array or range. It returns the remaining data as a new dynamic array without changing the original cells.
=DROP(array, rows, [columns])What does DROP do?
The DROP function is a lookup and reference function that trims rows or columns from the edges of a range. You tell it how many rows to remove from the top, how many columns to remove from the left, or both, and it hands back the rest of the data as a new array. The original cells stay untouched, so DROP is safe to use inside reports and dashboards.
Why does this matter? Most real spreadsheets have headers, totals, or spacer rows that you do not want in a calculation or a chart. DROP lets you strip those away in a single formula instead of manually selecting smaller ranges or deleting rows. Because it returns a dynamic array, the result updates automatically when the source data changes.
Imagine a weekly sales export where row 1 is a report title, row 2 is blank, and the last row is a grand total. You need just the clean table for a pivot or a chart. With DROP you can remove the top two rows and the bottom total row in one formula, and the result will always match the current export.
When to use DROP
- Removing report titles or blank spacer rows above a data table.
- Stripping a grand total row from the bottom of an export before charting.
- Dropping the first column of a range when it contains row labels you do not need.
- Trimming both rows and columns from a raw data dump to isolate the core table.
- Feeding a clean array into functions like SORT, FILTER, or XLOOKUP without helper ranges.
- Building dynamic dashboards that adapt when new rows are added to the source.
DROP syntax
=DROP(array, rows, [columns])| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array you want to trim. Example: A1:D10. |
rows | Required | The number of rows to remove. Positive numbers remove rows from the top; negative numbers remove rows from the bottom. Use 0 to remove no rows. Example: 2 removes the first two rows, -1 removes the last row. |
columns | Optional | The number of columns to remove. Positive numbers remove columns from the left; negative numbers remove columns from the right. Use 0 to remove no columns. If omitted, no columns are removed. Example: 1 removes the first column, -2 removes the last two columns. |
What it returns: A dynamic array containing the remaining rows and columns after the specified edges are removed. If all rows or all columns are removed, DROP returns a #CALC! error.
DROP examples
1 Remove the header row from a simple list
The goal is to get just the product names without the header. The formula =DROP(A1:B5, 1) tells DROP to look at the range A1:B5 and remove one row from the top. That drops the header row, leaving the four data rows. The result spills into the cells below the formula and shows the product names and prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | |||
| 2 | Pen | 1.5 | Pen | ||
| 3 | Notebook | 3.25 | |||
| 4 | Eraser | 0.75 | |||
| 5 | Ruler | 2 | |||
| 6 |
2 Remove the first column of a table
Here you want only the scores, not the student names. The formula =DROP(A1:C5, 0, 1) removes zero rows and one column from the left. DROP keeps all five rows but starts from column B, so the result contains just the Math and Science scores. The names in column A are left out of the returned array.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Math | Science | |||
| 2 | Ana | 88 | 91 | Math | ||
| 3 | Ben | 74 | 80 | |||
| 4 | Cara | 95 | 89 | |||
| 5 | Dan | 62 | 70 | |||
| 6 |
3 Remove the last row, which is a total
The last row of this table is a grand total that you do not want in a chart. The formula =DROP(A1:B6, -1) uses a negative row count to remove one row from the bottom. DROP returns rows 1 through 5, which include the header and the four monthly sales figures. The total row is excluded.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1200 | Month | ||
| 3 | Feb | 1350 | |||
| 4 | Mar | 1100 | |||
| 5 | Apr | 1500 | |||
| 6 | Total | 5150 | |||
| 7 |
4 Trim both the top rows and the left column
This export has a title row and a blank row at the top, plus an ID column on the left that you do not need. The formula =DROP(A1:C6, 2, 1) removes two rows from the top and one column from the left. What remains is the clean table of names and departments, starting from the header row. The result is ready for sorting or filtering.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee Report | |||||
| 2 | Name | |||||
| 3 | ID | Name | Dept | |||
| 4 | 101 | Mia | Sales | |||
| 5 | 102 | Noah | IT | |||
| 6 | 103 | Olivia | HR | |||
| 7 |
5 Clean a sales export before charting
A monthly sales export often has a report title, a blank row, and a grand total at the bottom. The formula =DROP(A1:C6, 2, 0) removes the top two rows and keeps all columns. The returned array starts at the header row and includes every data row, including the total row. To also drop the total, you would use =DROP(A1:C6, 2, 0) and then wrap it in another DROP with -1, but here the goal is simply to remove the title and blank row.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Monthly Sales Export | |||||
| 2 | Region | |||||
| 3 | Region | Sales | Date | |||
| 4 | North | 1200 | 2024-01-15 | |||
| 5 | South | 950 | 2024-01-16 | |||
| 6 | Total | 2150 | ||||
| 7 |
Common DROP errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #CALC! | The rows or columns argument removes all rows or all columns, leaving an empty array. | Reduce the number you are dropping, or check that the array has more rows or columns than you are removing. |
| #VALUE! | The rows or columns argument is not a number, for example text or a blank cell reference. | Use a whole number, including negative numbers and zero. Avoid text values in the rows and columns arguments. |
| #NAME? | The function name is misspelled or the version of Excel does not support DROP. | Check the spelling as DROP. DROP requires Microsoft 365 or Excel 2021 and later; older versions do not have it. |
| #SPILL! | The dynamic array result cannot spill because there are other values in the cells below or to the right of the formula. | Clear the cells where the result needs to spill, or move the formula to an area with enough empty space. |
Tips for using DROP
- Use positive numbers to remove rows from the top or columns from the left, and negative numbers to remove from the bottom or right.
- Combine DROP with SORT or FILTER to clean and organise data in one formula.
- Wrap DROP in a named range or table reference so the formula updates automatically when data is added.
- Remember that DROP does not change the original cells; it only returns a new array.
- Use 0 for rows or columns when you only want to trim the other dimension.
Frequently asked questions
What is the difference between DROP and TAKE?
DROP removes rows or columns from the edges of a range, while TAKE keeps only the specified number of rows or columns from the start or end. Use DROP to exclude data and TAKE to select data.
Can DROP remove rows from the middle of a range?
No. DROP only removes rows or columns from the start or end. To remove specific rows in the middle, use FILTER or CHOOSEROWS instead.
Does DROP work in Excel 2019?
No. DROP is available in Microsoft 365 and Excel 2021 and later. In older versions you would need to use manual range selection or other functions.
What happens if I drop more rows than the range has?
If you try to remove all rows or all columns, DROP returns a #CALC! error because the result would be empty.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. DROP reference