DROP is a new Excel function that allows you to get all the records from a data set after you have dropped the specified number of rows or columns.
DROP function is available in Excel for Microsoft 365, Excel for the web, and Excel for Microsoft 365 for Mac. It was released along with the TAKE function (which is the opposite of the DROP function)
In this article, I will show you a couple of examples of how to use the DROP function in Excel.
DROP Function Syntax in Excel
Below is the syntax of the Excel DROP function:
=DROP(array, rows,[columns])
Where:
- array – This is the array from which you want to drop rows and columns
- rows – This is a number that specifies the number of rows that you want to drop. If the number is positive, that many number of rows are dropped from the beginning of the data set, and if the number is negative, that many number of rows are dropped from the bottom of the data set.
- [columns] – This is a number that specifies the number of columns that you want to drop. If the number is positive, that many number of columns are dropped from the beginning of the data set, and if the number is negative, that many number of columns are dropped from the end of the data set.
Let’s now look at a couple of examples of using the DROP function in Excel.
Example 1 – Dropping Rows from the Beginning or End
Below, I have data in a Table with Name, Region, and Sales values, and I want to get all the records after dropping the first three records.
Here is the formula to do this:
=DROP(Data,3)
Note that the table is named Data in this example.
And if you want to drop the last three records, you can use the below formula:
=DROP(Data,-3)
Since the result of the DROP formula is an array, make sure that the cells that are supposed to occupy the result of the drop function are empty. If they’re not empty, you will see a SPILL error.
Example 2 – Dropping Columns from Beginning or End
Just like rows, you can also drop columns from a dataset using the DROP function.
Below, I have data in a Table with Name, Region, and Sales values, and I want to get only the sales column from this data set.
You can do this using the formula below:
=DROP(Data,,2)
If you want to drop columns from the beginning, you can use a negative number as the column number argument.
For example, I can use the below formula only to get the names column from the data set:
=DROP(Data,,-2)
Example 3 – Dropping Rows and Columns both
You can also drop both rows and columns from a data set by specifying the row number as well as the column number in the formula.
Below, I have a data set from which I want to get only the names column, and even from that column, I want to drop the last five names.
Here is the formula that will do this:
=DROP(Data,-5,-2)
Example 4 – Using DROP with SORT
DROP function becomes more useful when you start using it with other functions, such as SORT or FILTER.
Below, I have a data set with the names, regions, and sales values. I want to get only the names column, which should be sorted based on the sales values.
You can do this using the below formula:
=DROP(SORT(Data,3,-1),,-2)
The above formula first uses the SORT(Data,3,-1) formula to sort our data set based on the sales value (which is in column 3 of the dataset). I have used -1 as the third argument here, as I wanted the data to be sorted in descending order.
The result of this SORT formula is then fed into the DROP function, Which drops the last two columns to give us only the names.
Example 5 – Using DROP with FILTER
With the FILTER function, you can filter your larger data set to get the required array and then feed that into the DROP function to extract the relevant rows or columns.
Below, I have a data set with names, regions, and sales values columns. I want to extract all the records for the US region and then only get the top records after removing the bottom three records.
Here is the formula to do that:
=DROP(FILTER(Data,Data[Region]="US"),-3)
The above formula first uses the FILTER function to extract all the records where the region is US. This result is then fed into the drop function, which gives us all the records after dropping the last three records.
You can further refine this formula to first filter the records, then sort them based on the sales values, and then use the DROP function to extract all the records after dropping the last three records.
Here is the formula to do that:
=DROP(SORT(FILTER(Data,Data[Region]="US"),3,-1),-3)
The above formula first filters the data to only get the records for US, then sorts this filtered data based on the sales values, and then DROP function is used over it to get all the records except the last three.
I hope all the examples I’ve covered here have helped you get a better understanding of how to best use the DROP function in Excel.
If you have any questions or suggestions for me, please let me know in the comments section.
Other Excel articles you may also find useful:
