When working with your dataset in Microsoft Excel, there are occasions when you need to analyze only a portion of the data. Thankfully, Excel provides the TAKE and DROP functions for situations like this.
TAKE and DROP allow you to include or exclude a portion of an array in your selection. Let’s start by defining these functions and then explore how to use them in Excel.
What Is the TAKE Function in Excel?
The TAKE function in Excel extracts a specified number of rows and columns from an array. The syntax of the TAKE function is:
Here’s what each parameter signifies:
What Is the DROP Function in Excel?
On the other hand, the DROP function in Excel excludes specific rows and/or columns from an array or range of cells. The syntax of the DROP function is:
The parameters for the DROP function are similar to those in the TAKE function:
The TAKE and DROP functions work with arrays, and they output arrays as well. Since they always output arrays, there’s no need to mark them asarray formulas in Excel. For the same reason, you might encounterthe #SPILL error in Excelwhen using TAKE and DROP.
TAKE and DROP are available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web. However, older versions of Excel do not have these functions.
How to Use the TAKE Function in Excel
There are infinite Excel scenarios where TAKE might come in handy. Let’s take a look at a few examples.
1. Extract the Top Five Rows of Your Data
Suppose you have a worksheet containing a list of products and their prices over the past few months. If you want to extract only the first five rows of the data, you’re able to use the TAKE function instead of copying the data.
Here’s how you can do that:
This formula will retrieve the first five rows within the data rangeB2:F1and return the output.
2. Extract the First Column of Your Data
Let’s say you want to retrieve only the first column in the same dataset. To do this with the TAKE function, you need to leave the row parameter empty and only specify the column:
This formula will retrieve the first column from theB2:F1data range. Since we didn’t specify any value for therowparameter in our formula, every row in the first column is included in the output.
How to Use the DROP Function in Excel
You can use the DROP function to exclude unwanted rows and columns from your data. The syntax for the formulas is similar to the TAKE function. Let’s see how the DROP function works in Excel.
1. Exclude the Last Ten Rows of Your Data
Suppose you want to create a data view of your worksheet that shows everything except the last ten rows. Here is the formula you may use to achieve this:
This formula will exclude the last ten rows in theB2:F1data range. This is because we specified-10as the row parameter of data.
2. Exclude the Last Column of Your Data
You can use the formula below to trim down the last column of your data with DROP:
In this example, the DROP function will return the values that fall within the rangeB2:F1while excluding the last column.
Combining the TAKE and DROP Functions With Other Excel Functions
The true magic of Excel happens when you combine different functions together. For instance, if you need to feed a specific part of an array to a function, you can combine it with TAKE or DROP instead of writing out the data range.
Suppose you have a table of some items and their details and want to output the first item that costs above N100. You can use TAKE in combination with theFILTER function in Excelto do this. Here’s the formula to do that:
Let’s break this formula down. The FILTER function gets all rows with prices above N100 within the range. The formula for this is as follows:
Next, the formula feeds the results from FILTER to the TAKE function. Specifying both therowandcolumnarguments as1retrieves only the value in the filtered data’s first row and column.
Take and Drop Data in Your Sheet
Excel’s TAKE and DROP functions offer a powerful way to extract and manipulate specific data in your worksheets. Understanding their syntax and usage allows you to efficiently manage and analyze your Excel data, especially when combined with other functions.
