Dynamic arrays function

Excel DROP function

Q: How do I remove the header row in a formula?

Use =DROP(range,1). It returns everything except the first row.

Q: How do I remove the last row in Excel with a formula?

Use =DROP(range,-1). It is handy for ignoring a totals row.

Q: Can DROP remove rows and columns at once?

Yes. =DROP(range,1,1) removes the first row and the first column.

DROP returns a range without its first or last rows or columns. A positive number removes from the top or left, a negative one from the bottom or right. Use it to strip a header row, a totals row or a column you do not need before passing the data on.

=DROP(array, rows, [columns])

Arguments

array: The range or array to trim.
rows: How many rows to remove: positive from the top, negative from the bottom. Leave it empty to keep all rows.
columns: Optional. How many columns to remove: positive from the left, negative from the right.

DROP examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	ID	Item	Cost ($)
2	P-1	Paper	42
3	P-2	Toner	189
4	P-3	Pens	18
5	P-4	Folders	35
6	P-5	Labels	27
7	Total		311

Download this sheet as an Excel practice file (.xlsx)

=DROP(A1:C7,1)
Result: spills 6 rows × 3 columns
P-1 Paper 42
P-2 Toner 189
P-3 Pens 18
P-4 Folders 35
P-5 Labels 27
Total 0 311
Removes the header row and returns the five items and the total row.
=DROP(A2:C7,-1)
Result: spills 5 rows × 3 columns
P-1 Paper 42
P-2 Toner 189
P-3 Pens 18
P-4 Folders 35
P-5 Labels 27
A negative number removes from the bottom: the items without the Total row.
=DROP(A2:C6,,1)
Result: spills 5 rows × 2 columns
Paper 42
Toner 189
Pens 18
Folders 35
Labels 27
Leave rows empty and drop one column: the items and costs without the ID column.
=SUM(DROP(C2:C7,-1))
Result: 311
Adds the costs without the total row, 311, so the total is not counted twice.

How to use DROP

Click a cell with room to spill.
Type =DROP( and select the range.
Type how many rows to remove, negative to remove from the bottom.
Optionally add a number of columns to remove, then press Enter.

Common DROP mistakes

Dropping everything

If you remove as many rows or columns as there are, nothing is left and DROP returns #CALC!.

Confusing DROP and TAKE

TAKE(range,2) keeps the first two rows; DROP(range,2) removes them and keeps the rest.

Older Excel

DROP needs Excel for Microsoft 365, Excel 2024 or Excel for the web. In older versions use OFFSET or INDEX to point past the rows you do not want.

DROP FAQ

How do I remove the header row in a formula?

Use =DROP(range,1). It returns everything except the first row.

How do I remove the last row in Excel with a formula?

Use =DROP(range,-1). It is handy for ignoring a totals row.

Can DROP remove rows and columns at once?

Yes. =DROP(range,1,1) removes the first row and the first column.

Related functions

Updated November 3, 2026