The `DROP` function in Excel is used to remove a specified number of rows or columns from the start or end of an array. This function is particularly useful when you want to manipulate data by excluding certain parts of a dataset.
Syntax of the DROP Function
DROP(array, rows, [columns])
- array: This is the range or array you want to drop rows or columns from.
- rows: The number of rows to drop. Use a positive number to drop rows from the start and a negative number to drop rows from the end of the array.
- [columns]: The optional number of columns to drop. Use a positive number to drop columns from the start and a negative number to drop columns from the end.
Examples
- Dropping Rows from the Start
To drop the first 2 rows from a range `A1:C5`:
=DROP(A1:C5, 2)
This will return the range from `A3:C5`.
- Dropping Rows from the End
To drop the last 1 row from the same range:
=DROP(A1:C5, -1)
This returns the range from `A1:C4`.
- Dropping Columns from the Start
To drop the first column:
=DROP(A1:C5, 0, 1)
This returns the range `B1:C5`.
- Dropping Columns from the End
To drop the last column:
=DROP(A1:C5, 0, -1)
This returns the range `A1:B5`.
- Dropping Rows and Columns Simultaneously
To drop the first row and the last column at once:
=DROP(A1:C5, 1, -1)
This returns the range `A2:B5`.
Tips
- Negative Values: Use negative values to drop from the end.
- Zero Values: If you don’t want to drop any rows or columns in that dimension, use `0`.
- Compatibility: The `DROP` function is available in Excel for Microsoft 365, Excel 2021, and later versions.
This function provides a dynamic and flexible way to handle subsets of data without modifying the original dataset.
Unlock Your Potential
Excel
Access
Power BI
