DROP - Remove a contiguous beginning or ending slice
Continue unused audit labels after skipping issued ones, or remove an import metadata column without confusing DROP with TAKE.
By the end, you can
- Remove leading or trailing rows using signed counts.
- Return the remaining array.
- Use a column-only Excel removal deliberately.
Before you start: Understand array order and TAKE's selection rule.
In this lesson
Return what remains after a slice is removed
DROP removes contiguous rows or columns from an array and returns the remaining values. An audit team can skip the first three labels already used and print the rest. Unlike TAKE, a negative DROP count removes rows from the end; it does not select that tail. The source allocation remains unchanged.
Syntax and arguments
=DROP(array, rows, [columns])Positive counts remove from the start; negative counts remove from the end. In Excel, rows can be omitted when only columns are removed.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | The source array from which a slice is discarded. | A2:A9 |
rowsOptional | Rows to remove when selecting a row operation; omit for an Excel column-only request. | B2 |
columnsOptional | Optional columns to remove; its sign chooses the end. | 1 |
Unused labels after three were issued
504| A | B | C | D | |
|---|---|---|---|---|
| 1 | Allocated label | Used count | Unused label | |
| 2 | 501 | 3 | 504 | |
| 3 | 502 | 505 | ||
| 4 | 503 | 506 | ||
| 5 | 504 | 507 | ||
| 6 | 505 | 508 | ||
| 7 | 506 | |||
| 8 | 507 |
Remove from the end or keep just the final remainder
=DROP(A2:A9,-2)Discard 507 and 508, returning the first six allocation values.
=DROP(A2:A9,7)Only the final allocation value remains. A one-cell result is still the remainder of an array operation.
Discard an import metadata field in Excel
Batch A| A | B | C | |
|---|---|---|---|
| 1 | Batch note | Code | Quantity |
| 2 | Batch A | P101 | 5 |
| 3 | Batch A | P102 | 7 |
=DROP(A2:C3,,1)Leave rows omitted and remove the first column of each record.
Limits, errors, and versions
- Removing all records leaves no normal populated array and can give #CALC!. Invalid zero-sized results are not a reliable blank-report format.
- A blocked output range gives #SPILL!. Keep the anchor outside an Excel Table and leave room for the remaining records.
- DROP removes contiguous positions, not records matching a condition. FILTER is the function for a status-based eligibility rule.
- DROP is available in Microsoft 365 and Excel 2024, not perpetual Excel 2021/2019/2016. Fixed source ranges still need updating or an expanding source when allocation data changes.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.