Excel DROP function

Summary

The Excel DROP function is used to return a subset of a given array or range by “dropping” a specified number of rows and columns. This function is versatile in manipulating data arrays, allowing for the removal of parts of an array from either the start or the end. DROP is particularly useful in scenarios where only certain sections of a dataset are needed for analysis or presentation.
Syntax
				
					=DROP(array, [rows], [cols])
				
			
  • array: The source array or range from which rows and columns will be dropped
  • [rows]: [Optional] The number of rows to drop. Positive numbers drop from the start, negative numbers drop from the end
  • [cols]: [Optional] The number of columns to drop. Positive numbers drop from the start, negative numbers drop from the end
Return value
Returns the remaining array after the specified rows and columns have been dropped.

How to use

To use the DROP function, provide the array or range you want to modify, and specify the number of rows and/or columns to be dropped. You can specify positive numbers to drop from the start of the array, or negative numbers to drop from the end.

Examples

Simple DROP
Dropping Rows from the Start of an Array: To drop the first 3 rows from a given range:
				
					=DROP(A1:C10, 3)
				
			
This formula removes the first 3 rows from the range A1:C10, returning the remaining array starting from the 4th row.
DROP from the Start
Removing Columns from the Beginning of a Range: To drop the first 2 columns from a range:
				
					=DROP(A1:C10,, 2)
				
			
This removes the first 2 columns from the range A1:C10. The resulting array starts from column C.
DROP both Rows and Columns
Eliminating Rows and Columns from the Start: To drop both rows and columns from the beginning of an array:
				
					=DROP(A1:D10, 2, 1)
				
			
This formula drops the first 2 rows and the first column from the range A1:D10.
DROP from the End
Removing Rows from the End of an Array: To drop rows from the end of a range:
				
					=DROP(A1:C10, -2)
				
			
By using a negative number, this formula removes the last 2 rows from the range A1:C10.
Complex DROP
Advanced Manipulation by Dropping from Both Ends: To dynamically remove rows and columns from both ends of an array:
				
					=DROP(A1:E10, -1, -3)
				
			
This example removes the last row and the last 3 columns from the range A1:E10, tailoring the array for specific data requirements.

Additional Notes

  • The DROP function is a powerful tool for array manipulation, enabling flexible data handling within Excel.
  • It’s particularly useful when combined with other array functions like SORT, FILTER, or UNIQUE.
  • Keep in mind that providing a number of rows or columns to drop greater than the total in the array results in a #VALUE! error.

Related Functions

Excel CHOOSEROWS function

The Excel CHOOSEROWS function selectively returns specified rows from a range, streamlining dataset reorganization or analysis.

Excel EXPAND function

The Excel EXPAND function enlarges arrays by adding rows/columns, ideal for dataset dimension increase or padding with values.

Excel TAKE function

The Excel TAKE function extracts a specific subset of rows and columns from an array, ideal for isolating sections of a dataset.

Excel CHOOSECOLS function

The Excel CHOOSECOLS function extracts specific columns from a range, ideal for reorganizing or simplifying datasets for analysis.

Content Navigation