DROP
Remove a set number of rows or columns from the start or end of an array with Excel's DROP function.
Spreadsheet editor
Spreadsheet editor
Syntax
=DROP(array, rows, [columns])
Returns: Array Arguments
| Argument | Required | Description |
|---|---|---|
| array | Yes | The range or array you want to trim rows or columns from. |
| rows | Yes | The number of rows to remove. A positive number drops from the top, and a negative number drops from the bottom. |
| columns | No | The number of columns to remove. A positive number drops from the left, and a negative number drops from the right. Leave it out to keep every column. |
About
DROP trims a range or array by removing rows, columns, or both from its edges, then spills what is left into a new dynamic array. You tell it how many rows and columns to cut, and it hands back everything else. This is the quickest way to skip a header row, ignore a totals row at the bottom, or peel off a label column without rebuilding the table by hand.
Use positive numbers to drop from the top or left, and negative numbers to drop from the bottom or right. For example, dropping 1 row removes the first row, while dropping -1 row removes the last one. The columns argument is optional, so you can leave it out when you only need to trim rows. Because the result is a live spill, it updates automatically as your source data grows or shrinks.
DROP is the natural partner to TAKE, which keeps the edge rows and columns that DROP throws away. Combine it with FILTER to clean a list before slicing, or with VSTACK and HSTACK to reshape data first. To pick specific columns by position instead, reach for CHOOSECOLS.
Use positive numbers to drop from the top or left, and negative numbers to drop from the bottom or right. For example, dropping 1 row removes the first row, while dropping -1 row removes the last one. The columns argument is optional, so you can leave it out when you only need to trim rows. Because the result is a live spill, it updates automatically as your source data grows or shrinks.
DROP is the natural partner to TAKE, which keeps the edge rows and columns that DROP throws away. Combine it with FILTER to clean a list before slicing, or with VSTACK and HSTACK to reshape data first. To pick specific columns by position instead, reach for CHOOSECOLS.
Examples
Cut a totals row off the bottom
See how a negative number drops from the bottom, so -1 strips the Total row and keeps the four regions above it. Change a sales figure and watch the trimmed list update.
Spreadsheet editor
Dropping more rows than the list holds
Watch what happens when you drop all 4 rows from a 4-row list: DROP returns a #CALC! error. Lower the 4 to 1, 2, or 3 to keep some tasks in view.
Spreadsheet editor
Trim a row and a column at once
Pass both arguments to cut in two directions together. Here DROP removes the header row and the Name column, leaving just the Q1 and Q2 numbers to spill.
Spreadsheet editor
Watch out for
⚠
Dropping more than the array holds
Asking DROP to remove as many or more rows (or columns) than the array contains leaves nothing behind, so you get a #CALC! error.
→ Keep the rows and columns counts smaller than the size of the array. Use ROWS and COLUMNS to check how big your range is before you trim it.
⚠
Confusing drop with keep
People expect DROP(array, 3) to return 3 rows, but it removes 3 rows and returns everything else.
→ DROP cuts rows from the edge. If you want to keep a set number of rows instead, use TAKE.
⚠
Output blocked by existing data
DROP spills its result, so a value sitting in the output area triggers a #SPILL! error and nothing appears.
→ Clear the cells below and to the right of the formula so the trimmed array has empty space to expand into.
Tips & notes
DROP is a Microsoft 365 function and is not available in Excel 2021 or earlier, or in Google Sheets. Setting rows or columns to 0 keeps that direction unchanged, so DROP(array, 0, 1) drops only the first column.
Common questions
What is the difference between DROP and TAKE?
DROP removes rows or columns from the edges and returns the rest, while TAKE keeps the edge rows or columns and discards the rest. They are opposites that work well together.
How do I drop rows from the bottom instead of the top?
Use a negative number. DROP(A1:A10,-2) removes the last two rows, while DROP(A1:A10,2) removes the first two.
Why does DROP return a #CALC! error?
That happens when you try to drop every row or column, leaving an empty result. Lower the rows or columns count so something remains.
Can I use DROP in Google Sheets?
No. DROP is a Microsoft 365 Excel function and has no Google Sheets equivalent. You can reproduce it there with FILTER or ARRAYFORMULA combinations.