Excel functions / Dynamic arrays
DROP function
Removes the first or last rows or columns of an array.
Syntax
=DROP(array, rows, [columns])
Arguments
array: The array to drop from.rows: How many rows to remove: positive from the top, negative from the bottom.columns(optional): How many columns to remove: positive from the left, negative from the right.
When to use it
DROP removes a number of rows (and optionally columns) from the start or end of a range, and spills what is left. Its everyday use is dropping a header row; a negative number drops from the end.
Examples
Drop the header row
A B C 1 Item Pen 2 Pen Stapler 3 Stapler Folder 4 Folder 5 Tape 6 Glue 7 Ruler In C1:
=DROP(A1:A4, 1)→ Pen; Stapler; FolderDrop the last two rows
A B C 1 Item Pen 2 Pen Stapler 3 Stapler Folder 4 Folder Tape 5 Tape 6 Glue 7 Ruler In C1:
=DROP(A2:A7, -2)→ Pen; Stapler; Folder; Tape
Common mistakes
- Dropping everything. Dropping as many rows as there are gives #CALC!: there is nothing left.
DROP in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: ZAHODIT
- Danish: UDELAD
- Dutch: WEGLATEN
- Finnish: HYLKÄÄ
- German: WEGLASSEN
- Italian: ESCLUDI
- Polish: POMIŃ
- Russian: СБРОСИТЬ
- Spanish: EXCLUIR
- Turkish: BIRAK
Practise it
Related functions
- TAKE: Keeps the first or last rows or columns of an array.
- CHOOSEROWS: Returns the rows you name from an array, in the order you name them.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- VSTACK: Stacks arrays on top of each other into one taller array.