DROP Function

Removes rows or columns from the start or end of an array.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2024

Returns

A spilled array

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

Overview

DROP trims a block of data. Tell it how many rows (and optionally columns) to remove, and it spills whatever is left.

Positive numbers drop from the top or left; negative numbers drop from the bottom or right. It's the opposite of TAKE.

Syntax

=DROP(array, rows, [columns])

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to trim.
rowsYesRows to remove. Positive = from the top, negative = from the bottom, 0 = none.
columnsOptionalColumns to remove. Positive = from the left, negative = from the right.

Examples

Example 1 — Removing a header row

We want the data without its headers so we can feed it into another formula.

D1fx=DROP(A1:B4, 1)
ABCDE
1ItemQtyPens12
2Pens12Pads8
3Pads8Ink3
4Ink3
=DROP(A1:B4, 1) Explain

Example 2 — Dropping a total row and an ID column

Our report ends in a total row and starts with an ID column we don't need.

=DROP(A2:F20, -1, 1) Explain

-1 removes the last row (the total) and 1 removes the first column (the ID).

Combine with VSTACK to stack several sheets' data while dropping each sheet's header: =VSTACK(Jan!A1:C50, DROP(Feb!A1:C50, 1)).

Common mistakes

  • #CALC! errors. You dropped every row or column, leaving an empty result.
  • Mixing up DROP and TAKE. DROP removes what you specify; TAKE keeps what you specify.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.