|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

DROP

DROP

  • Last updated on

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 Explanation and Result
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:B4>30), 1)

This formula uses FILTER to select employees over 30 (B1:B4>30), 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. 

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