DROP

Description

Use this function to remove a specified number of rows or columns from the beginning or end of an array or range.

This makes it useful for removing headers, footers, or unwanted data sections.

Syntax

DROP(array, num_rows, [num_cols])

Inputs

This function accepts the following argument:

Name	Required	Description	Valid Input
`array`	Yes	The range or array from which to remove rows or columns.	A cell, a cell range, or a formula which results in either of these.
`num_rows`	Yes	Number of rows to remove from the start or end of the array. Positive values remove from the start, negative values from the end.	A positive integer, a reference to a cell that contains a positive integer, or a formula which results in either of these.
`[num_cols]`	No	Number of columns to remove from the start or end of the array. Positive values remove from the start, negative values from the end.	A positive integer, a reference to a cell that contains a positive integer, or a formula which results in either of these.

Examples

Sample data

	A	B	C	D
1	Name	Age	City	Salary
2	John	35	New York	75000
3	Alice	28	Chicago	65000
4	Bob	42	Los Angeles	85000
5	George	22	Denver	55000
6	Helen	30	Scottsdale	71000

Sample formulas

Use case Formula Result and Explanation

Remove the header row from the dataset.

=DROP(A1:D6, 1)

This formula duplicates the data set without including the first (header) row "1".
Using the data table above, this produces the following:

A	B	C	D
John	35	New York	75000
Alice	28	Chicago	65000
Bob	42	Los Angeles	85000
George	22	Denver	55000
Helen	30	Scottsdale	71000

Removes the last row from the dataset.

=DROP(A1:D6, -1)

This formula duplicates the data set without including the last row.
Using the data table above, this produces the following:

A	B	C	D
Name	Age	City	Salary
John	35	New York	75000
Alice	28	Chicago	65000
Bob	42	Los Angeles	85000
George	22	Denver	55000

Remove the first row, no matter how many columns are in the dataset.

=DROP(A1:INDEX(D:D, COUNTA(A:A)), 1, 0)

This formula uses DROP in combination with INDEX and COUNTA to dynamically adjust what is copied based on the number of rows with data without including the first (header) row "1".

These operations work as follows:

COUNTA(D:D): Counts the number of non-empty cells in column D. So if column D has 10 filled rows, COUNTA(D:D) returns 10.
INDEX(D:D, COUNTA(A:A)): Returns the last filled cell in column D. If column D has data in A1:A10, this becomes INDEX(A:A, 10), which refers to A10.
A1:INDEX(A:A, COUNTA(A:A)): starts the dataset to be duplicated at A1, and then expands it to encompass all the identified rows.
DROP then removes the first row ("1"), but no columns ("0").

Remove the first row and the first two columns from a data set.

=DROP(A1:D4, 1, 2)

This formula uses DROP to remove the first row (1), and the first two columns (2).
Using the data table above, this produces the following:

A	B
New York	75000
Chicago	65000
Los Angeles	85000

Make a copy of the data set that shows only employees over 30, without the header row.

=DROP(FILTER(A1:D4, B1:B430), 1)

This formula uses FILTER to select employees over 30 (B1:B430), then drops the header row.
Using the data table above, this produces the following:

A	B	C	D
John	35	New York	75000
Bob	42	Los Angeles	85000

Key benefits:

Prevents trailing zeros → Filters out blank rows.
Fully dynamic → No need to update when adding rows.
Works for any dataset size → Expands/shrinks automatically.

Notes

As with all array functions, DROP dynamically populates results across adjacent cells, starting from the cell containing the DROP function.
DROP:
- Can remove rows or columns from the start or end of an array.
- Works with both static ranges and dynamic arrays.

Tips

If you have header row(s), start your array values one row down (for example in the above data set, start with A2 rather than A1) if you don't want the headers to be included.
DROP can be combined with other functions, including:
- FILTER: To remove rows from filtered data.
- SORT: To remove rows before or after sorting.
- UNIQUE: To remove duplicate rows.
- SEQUENCE: To generate arrays and remove specific sections.
- RANK: To identify ranking.

Related functions

Here are the other array formula functions supported in Workiva spreadsheets.

Support

Community

Workiva Support Center