DROP

Excel Functions

Remove portion of an array

Syntax

EXCEL

=DROP(array, [rows], [col])

Arguments

Parameter	Description
`array`	The source array or range.
`rows`	[optional] Number of rows to drop.
`col`	[optional] Number of columns to drop.

Return Value

The remaining array

Details

The DROP function returns a subset of a given array by "dropping" rows and columns. The number of rows and columns to remove is provided by separate rows and columns arguments. Rows and columns can be dropped from the start or end of the given array. When positive numbers are provided for rows or columns, DROP removes values from the start or top of the array. Negative numbers remove values from the end or bottom of the array. The DROP function takes three arguments: array, rows, and columns. Array is required, along with at least one value for rows or columns. Array can be a range or an in-memory array from another formula. Rows and columns can be negative or positive. Positive numbers remove values from the start of the array; negative numbers remove values from the end of the array. Bot

Examples

Basic usage

To use DROP, provide an array or range, and numbers for rows and/or columns:

EXCEL

=DROP(array,3) // drop first 3 rows
=DROP(array,,3) // drop first 3 columns
=DROP(array,3,2) // drop first 3 rows and 2 columns

Drop from start

To remove rows or columns from the start of a range or array, provide positive numbers for rows and columns. In the worksheet below, the formula in F3

EXCEL

=DROP(B3:D11,6) // drop first 6 rows

Drop from start

The second formula in F8 is:

EXCEL

=DROP(B3:D11,5,1) // drop first 5 rows and column 1

Drop from end

To remove values from the end of an array, provide negative numbers for rows and columns. In the worksheet below, the formula in cell F3 is:

EXCEL

=DROP(B3:D11,-6)

Drop from end

The formula in F8 is:

EXCEL

=DROP(B3:D11,-5,-1)

DROP vs. TAKE

The DROP and TAKE functions both return a subset of an array, but they work in opposite ways. While the DROP function removes specific rows or columns

EXCEL

=DROP(array,1) // remove first row
=TAKE(array,1) // get first row

DROP

Syntax

Arguments

Return Value

Details

Examples

See Also