📎 This article includes 1 downloadable practice file ↓
In this article
DROP removes rows or columns from the start (positive) or end (negative) of an array — e.g. strip headers or total rows.
Syntax
=DROP(array, rows, [columns])| Argument | What it means |
|---|---|
rows |
Rows to remove; negative = from the bottom. |
Examples
Example 1
=DROP(A1:E101, 1)Data without the header row.
Example 2
=DROP(A2:E101, -1)Remove a grand-total row at the bottom.
DROP in practice
Where you will use it
- Remove header rows from an import
- Drop the totals row
- Skip the first N records
Worked example
| Drop rows | Formula | Result |
|---|---|---|
| 1 | =ARRAYTOTEXT(DROP({"Head","Amt";"A",10;"B",20},A2)) | A, 10, B, 20 |
| -1 | =ARRAYTOTEXT(DROP({"Head","Amt";"A",10;"B",20},A3)) | Head, Amt, A, 10 |
| 2 | =ARRAYTOTEXT(DROP({"Head","Amt";"A",10;"B",20},A4)) | B, 20 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Negative counts | Drop from the end |
| Dropping everything | #CALC! |
Related functions
TAKE · CHOOSEROWS
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong