Quick Navigation
DROP Function
Summary
The Excel DROP function removes a specified number of rows or columns from the beginning or end of an array. Perfect for cleaning data by stripping headers, footers, or unwanted edges from dynamic arrays.
Syntax
DROP(array, rows, [columns])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array |
Yes | Source array to trim |
| rows | Number |
Yes | Rows to drop (positive=start, negative=end) |
| columns | Number |
No | Optional columns to drop (positive=start, negative=end) |
Using the DROP Function
DROP excels at array preprocessing by removing unwanted headers, footers, or margins. Use it to extract core data from reports, clean imported tables, or prepare arrays for further dynamic array functions.
Common DROP Examples
Remove Header Rows
=DROP(A2:C10,1)Drops first row (header) from data range A2:C10
Drop Footer Rows
=DROP(A2:C10,-1)Removes last row (footer/summary) from array
Trim First Columns
=DROP(A2:E10,,2)Drops first 2 columns, keeps all rows
Clean 2D Margins
=DROP(A1:F20,2,1)Removes 2 header rows and 1 label column
Dynamic Table Core
=DROP(FILTER(Table1,Table1[Sales]>1000),1,1)Filters sales data then drops header and first column
Frequently Asked Questions
Common Errors and Solutions
#CALC! error
Cause: rows or columns parameter equals 0
Solution: Use 1 or greater to drop items, or omit optional parameters
#NUM! error
Cause: Array too large for Excel's limits
Solution: Reduce array size or use different approach
#VALUE! error
Cause: Invalid array reference
Solution: Ensure array parameter references valid range/spill
Notes
- Positive numbers drop from START, negative from END
- Works with dynamic arrays (FILTER, SORT, etc.)
- columns parameter is optional - omit with double comma ,,
- Cannot drop zero rows/columns (causes #CALC!)
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021