Microsoft Excel comes packed with a variety of useful features to explore. Some of its functionality can come in handy when you least expect it. If you’ve ever wondered if you could “crop” or “trim” a data set without deleting the original information, you’ll be interested in Excel’s DROP function. Read on to find out how it works and where it can be useful for you.
What Is the DROP function in Microsoft Excel?
The DROP function in Excel allows you to remove a specified number of rows or columns (or both) from a particular set of data. This is useful for removing headings and footers from data sets, or breaking smaller tables out of larger ones for more focused, granular analysis.
The function’s main limitation is that you can’t remove a row or column from the middle of the array and then stitch the remainder together; the DROP function only removes rows or columns from the ends of the array. You would have to put together multiple DROP functions, use a different formula, or rearrange your array to take out data from the middle of a set.
Understanding the Syntax of the DROP Function
The DROP function is relatively simple, containing just three arguments that are all relatively straightforward.
Thearrayargument specifies the data set that will be changed. Therowsargument defines the number of rows that will be removed from the start of the array. Likewise, thecolumnsargument defines how many columns will be removed from the start of the array.
you may change the “rows” and “columns” arguments to remove them from the end of the array instead by putting a negative number in the value. For example, typing in “3” into the “rows” argument will take away the first three rows of data in the array, but typing “-3” will remove the last three rows of data instead.
If the array is empty, or the number of specified rows or columns in their respective arguments is zero, Excel will return a “#CALC!” error, and the function won’t work.
How to Use the DROP function in Excel
Let’s demonstrate the DROP function in practice by working with an example spreadsheet of product orders at a hypothetical electronics store.
In our example, we’re working on a report that measures the flow of inventory, which usually require the product order numbers to be removed from the sheet. While we’re putting our sheet together, we find out from customer service that the last four orders of the day have been returned, and we need to remove them from the list of sales.
We’re looking to remove the last four orders from the table, which are arranged across the rows, as well as the order numbers, which are displayed in a column. Both of these tasks are relatively easy to do using the DROP function.
In cellJ1, type or paste the below formula into your spreadsheet and pressEnter.
This formula asks the DROP function to first take our data as the array (from cellsA1throughG17). Then, the value of the function’s “rows” argument is set to “-4”. This tells the function to work backward and remove four rows from the end of the array, rather than from the top.
Lastly, the “columns” argument is set to “1” to instruct the function to work from the start of the sheet and remove the first column from the array.
Like in the output of our example, you may notice some formatting differences in the resulting array, but these are not a result of the function. These appearance issues are easily fixed with settings from the “Font” and “Number” sections of the “Home” ribbon in Excel.
Help Your Data Fall Into Place With Excel’s DROP Function
Microsoft Excel has a whole host of unique features to organize, analyze, and even design your data, so you can achieve the results you want and present your information at its best.
Functions like DROP may seem unusual, but they come in handy when your data presents unusual challenges to conquer. There’s even more you’re able to do with your spreadsheets, just waiting to be discovered within the many functions of Microsoft Excel.
