Excel functions / Dynamic arrays

DROP function

Removes the first or last rows or columns of an array.

Syntax

=DROP(array, rows, [columns])

Arguments

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

  1. Drop the header row

    ABC
    1ItemPen
    2PenStapler
    3StaplerFolder
    4Folder
    5Tape
    6Glue
    7Ruler

    In C1: =DROP(A1:A4, 1) → Pen; Stapler; Folder

    Try this example

  2. Drop the last two rows

    ABC
    1ItemPen
    2PenStapler
    3StaplerFolder
    4FolderTape
    5Tape
    6Glue
    7Ruler

    In C1: =DROP(A2:A7, -2) → Pen; Stapler; Folder; Tape

    Try this example

Common mistakes

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.

Translate a whole formula

Try DROP in the playground

Practise it

Related functions