How to Use the DROP Function in Microsoft Excel

One of the most underused lookup and reference functions in Microsoft Excel is the DROP function. This powerful yet simple function lets you remove a specified number of rows or columns from the start or end of an array without altering the original dataset.

The DROP Syntax

Excel’s DROP function has three arguments:

=DROP(a,b,c)

where

• a is the array from which you want to drop a certain number of rows or columns,
• b is the number of rows to drop from the array, and
• c is the number of columns to drop from the array.

As you use this function, bear in mind the following criteria and characteristics:

• Argument a is mandatory, and at least one of arguments b and c must be stated to avoid an error message.
• For arguments b and c, a positive number drops rows from the top or columns from the left, and a negative number drops rows from the bottom or columns from the bottom.
• If you input a number for arguments b or c that exceeds the number of rows or columns in the array, you’ll see the CALC! error.
• DROP is a dynamic array function. In other words, the result extends to cells adjacent to where the formula is entered—a behavior known as spilling. Since dynamic arrays cannot spill into table columns, the DROP formula must be entered into a regular cell.

Examples: The DROP Function in Use

In all the examples below, I’ll use the DROP function to manipulate copies of the following Excel table, which contains students’ IDs in column A, their genders in column B, their classes in column C, and their test scores in column D.

Exercise 1: Drop Rows From the Top of an Array

Let’s imagine you want to create a new dataset comprising all the students not in the top 10. First, reorder the students according to their scores by clicking the filter button in the column header, and selecting “Sort Largest To Smallest.”

One way to duplicate the dataset with the top 10 rows removed would be to hard-code argument b:

=DROP(T_Stud,10)

where T_Stud is the table containing the array from which you want to drop some rows, and 10 is the number of rows to drop. Because this is a positive number, the rows are dropped from the top of the dataset. Argument c is omitted because you want all the columns to be transferred to the new array.

One of the issues with hard-coding values in Excel formulas is that it makes them inflexible. That is to say, if, instead, you wanted to show all the students not in the top 15, you would need to edit the formula.

Instead, you can type the value in a cell and reference this in the formula:

=DROP(T_Stud,H1)

Now, if you need to change the number of rows removed from the result, you can simply type a new value into cell H1.

Exercise 2: Drop Rows From the Bottom of an Array

The principle for dropping rows from the bottom of the array is the same as dropping rows from the top, except for one tiny tweak to the formula. Specifically, argument b needs to be a negative number.

This time, let’s say you want to show all students not in the bottom 10. To do this, after sorting the data in the table called T_Stud2 into descending order by the Score column, and typing 10 into cell H1, type:

=DROP(T_Stud2,-H1)

where the minus sign before the cell reference turns the number negative.

Exercise 3: Drop Columns From the Left of an Array

Now, imagine you’re creating a report of student scores, but you want to anonymize the data. In other words, you want to duplicate the dataset in T_Stud3, but with the first column removed. To do this, type:

=DROP(T_Stud3,,1)

Notice how the formula skips over argument b and only specifies a number for argument c.

Exercise 4: Drop Columns From the Right of an Array

This time, your aim is to produce a list of students, their gender, and their class from table T_Stud4, without including their score. To achieve this, type:

=DROP(T_Stud4,,-1)

Remember, the minus sign tells Excel to drop columns from the right of the data. This means that if you add another column of data to the right of the Score column, the new column would be dropped, and the result would return four columns overall.

Exercise 5: Drop Rows and Columns at the Same Time

So far, we’ve dropped either rows or columns. However, you can use all the function’s arguments at the same time to drop both.

Let’s say you want to produce a list of student IDs and their gender from table T_Stud5, but only those not in the bottom 10. After sorting the Score column into descending order and typing 10 into cell H1, type:

=DROP(T_Stud5,-H1,-2)

where -H1 turns the value in cell H1 (10) negative to remove the bottom 10 rows, and -2 removes the two rightmost columns.

Using DROP With Other Functions

While the DROP function is useful on its own, its true value and power become apparent when combined with other dynamic array functions.

Exercise 6: Sort and Drop Data

In all the examples above, the order of the DROP result has been determined by sorting the source data. While that works well, you’ll run into issues if you need to sort the source data by another column for other analytical purposes.

To make sure the result of the DROP formula is always sorted as desired, nest the SORT function.

In this example, where the table is called T_Stud6, suppose you want to produce a new, anonymized dataset of students not in the bottom 10, sorted by score. To do this, type:

=DROP(SORT(T_Stud6,4,-1),-H1,1)

where

• 4 tells Excel to sort the data by the fourth column (Score),
• -1 tells Excel to sort this column in descending order (largest to smallest),
• -H1 tells Excel to drop the bottom 10 rows, since cell H1 contains the number 10 and the formula contains a minus sign, and
• 1 tells Excel to drop the first column.

Now, even if you re-sort the source data by the Class column, the result remains sorted by the Score column.

Exercise 7: Combine and Drop Data

In this example, each class has its own table: T_Blue for the blue class, T_Green for the green class, and T_Yellow for the yellow class.

Rather than analyzing these tables separately, you want to extract and group the lowest-scoring students across all classes. Specifically, you want the result to display all students not in the top 10, while also sorting the data. This is where the VSTACK function comes in handy.

Type:

=DROP(SORT(VSTACK(T_Blue,T_Green,T_Yellow),3,-1),G1)

where

• The VSTACK function appends the three tables, one on top of the other,
• 3 tells Excel to sort the result by the third column (Score),
• -1 tells Excel to sort this column in descending order, and
• G1 tells Excel to drop the first 10 rows in the appended data, since cell G1 contains a positive number 10.

Exercise 8: Drop Rows or Columns From Hand-Picked Data

You already know that the DROP function lets you remove rows or columns from the edge of an array. But what if you want to remove rows or columns from both edges, and then clip the result further?

Specifically, in this case, you want to list the classes of the students not in the bottom 10 of table T_Stud8, and sort the result by the Score column, so that you can visualize which class is performing the best.

In this case, you can use the CHOOSECOLS function. Type:

=DROP(CHOOSECOLS(SORT(T_Stud8,4,-1),3),-I1)

where

• The SORT part of the formula sorts the source data by the fourth column (4) in descending order (-1),
• The CHOOSECOLS part of the formula extracts the third column (3) from the dataset, and
• The DROP part of the formula excludes the bottom ten rows, since cell I1 contains the number 10, and the cell reference is preceded by a minus symbol.

Where Excel’s DROP function lets you remove rows or columns from the original dataset in the result, the TAKE function does the exact opposite—it lets you keep specific numbers of rows or columns in the result. They both follow the same syntax and are available in the same versions of Excel. So, once you learn how to use one, it’s easy to adapt to the other.