Practice free

DROP function

DROP removes rows or columns from the start or end of an array and returns what is left. It is a quick way to strip a header row from a range.

Example

Read it as a sentence: take B1:E7 and drop its first row, so the list now starts with Boxer cartridge.

Practice this in the app

Syntax

DROP(array, rows, [columns])

Arguments in [square brackets] are optional.

array (required)
Source array.
rows (required)
Rows to remove; positive from the top, negative from the bottom.
columns (optional)
Columns to remove; positive from the left, negative from the right.

Returns: The array without the dropped rows and columns.

One DROP formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=DROP({1,2,3},,1) 2 Array
=DROP(Items!B1:E7,1) Boxer cartridge Drop header
=DROP(Items!B1:E7,-5,-2) Item Drop from end
=DROP(Items!B1:E7,,2) Qty Columns only
=DROP(Items!B1:E3,"1") Boxer cartridge Text count
=DROP(Items!B1:E3,1.9) Boxer cartridge Fractional count
=DROP(Items!B1:E7,7) #CALC! Drop all rows
=DROP(Items!B1:E7,0,4) #CALC! Drop all columns

The Items sheet these formulas read

Watch out for

  • Dropping everything returns #CALC!.
  • Use DROP(range,1) to remove a header row.

Where it works

Excel
DROP arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
Google Sheets
No direct equivalent: `=ARRAY_CONSTRAIN / INDEX(range,2,0) to skip a header row`. No known direct DROP in Sheets (unsure if added recently); use INDEX or a QUERY offset instead.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .