How to Use the DROP Function to Remove Rows from an Array in Excel

The Problem with Extraneous Data

In Microsoft Excel, when you use a function like SORT or FILTER to generate a dynamic array of data, Excel frequently pulls the entire dataset, including rows or columns that you do not actually want to see in your final report.

For example, you might have a master spreadsheet with 100 rows of sales data, including a “Header Row” at the very top (Row 1) and a “Grand Total” summary at the very bottom (Row 100). If you want to feed that raw data into an advanced chart or another formula, you cannot include the Header or the Total rows, because the text in the header and the massive aggregate number in the total will completely corrupt the math.

Traditionally, fixing this required writing complex, volatile OFFSET formulas to carefully trim the edges of the dataset. Today, you can use the much cleaner, native DROP function.

Understanding the Syntax

The DROP function takes a large array of data and instantly slices off a specific number of rows or columns from the edges, outputting a perfectly trimmed, dynamic array.

=DROP(array, [rows], [columns])

• array: The master grid of data you want to trim.
• rows: The number of rows you want to delete from the array.
• columns: The number of columns you want to delete from the array.

Example 1: Dropping a Header Row

Assume your master data is located in A1:D100. Row 1 contains text headers (Name, Date, Price, ID). You want to extract all the raw data, but you want to cleanly slice off that top header row.

You use a positive number in the rows argument to tell Excel to drop from the top of the array.

=DROP(A1:D100, 1)

How this works:

1. Excel looks at the 100-row grid.
2. It sees the instruction to drop 1 row.
3. It instantly deletes Row 1 from the top of the array.
4. It spills Rows 2 through 100 perfectly onto your screen.

If you wanted to drop the first five rows, you would simply change the number to 5 (=DROP(A1:D100, 5)).

Example 2: Dropping a Grand Total (Bottom Row)

Now assume that Row 100 is a “Grand Total” summary row that is ruining your charts. You need to slice it off, but you want to keep the header at the top.

To tell Excel to drop data from the bottom of the array instead of the top, you simply use a negative number.

=DROP(A1:D100, -1)

This formula leaves Rows 1 through 99 perfectly intact, but slices the very last row off the bottom.

Example 3: Dropping Rows and Columns Simultaneously

The true power of the DROP function is that it can slice both axes (rows and columns) at the exact same time.

Let’s look at the same A1:D100 dataset. You want to:

1. Drop the text header from the top (Row 1).
2. Drop the grand total from the bottom (Row 100).
3. Drop the “Employee ID” data from the far-right edge (Column D).

First, you cannot drop from the top and bottom simultaneously in one argument. You must nest the DROP functions.

=DROP(DROP(A1:D100, 1), -1, -1)

How this works:

1. The inner formula (DROP(A1:D100, 1)) executes first, slicing off the top header row. The data is now 99 rows tall.
2. The outer formula then takes over. The -1 in the rows argument slices off the bottom total row.
3. The -1 in the columns argument slices off the far-right column (Column D).

The result is a perfectly trimmed, 98-row by 3-column array of pure, flawless data ready for advanced analysis.