How To Pick Rows Or Columns, And Combine Them With Other Functions For Powerful Uses. Part 2

In this post, I’ll share an Excel function that enables us to easily pick specific number of rows and/or columns from a range of data. Additionally, I’ll share examples of how it can be combined with other functions to do some powerful tasks.

The function I am discussing here is: DROP(). In another blog, I discuss its mirror function, TAKE().

DROP FUNCTION

DROP() function can extract a portion of a full range/table of data by dropping rows or columns (original dataset is not changed). This is the opposite of TAKE() function. While you use TAKE() to specify what parts of a range/table you want to extract into another new range/table, you’d use use DROP() to specify what part of a range/table you do NOT want to extract into another new range/table. Both drop() and take() are dynamic functions.

PRO TIP: In Python’s pandas library, the DROP() and TAKE() are equivalent to drop() and iloc[] functions respectively. For example, code for dropping a column would look like this in Python: df = df.drop('A', axis=1) where df is a dataframe. And for dropping a row(s):

first_row = df.iloc[0] # take the first row.
first_three_rows = df.iloc[0:3] # take the first 3 rows

Let’s look at some examples of using DROP. Suppose we have the following dataset (B47:E72):

Example 1: Drop 20 rows from the top of the range (return all columns).

Solution: DROP(B47:E72,20)

Example 2: Extract first 5 rows from the top (all columns), then drop the last 2 rows and the first 2 columns from extraction.

Solution: DROP(TAKE(B47:E72,5),-2,2)

And you will get this result:

Example 3: Get all items that are over $10. Then only extract last 2 columns from that match (leaving out the first column).

Solution: DROP(FILTER(B47:E72,D47:D72>10),,1) [Use FILTER() first, then DROP() on the results.]

And you will get this result:

Be warned though that the data extracted by either TAKE or DROP (or any dynamic function w/ spill) CANNOT be directly edited in the output cells. To edit any cell manually after extraction, either copy the results to a new range, or copy the range and paste as values only.

I hope this was useful. Be sure to check out my other post on TAKE…the ‘opposite’ of DROP function.