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: TAKE(). In the next blog, I discuss its mirror function, DROP().
TAKE FUNCTION
Purpose: To extract specified rows and/or columns from a range or table either from the top or from the bottom.
Syntax:
TAKE(array, number_of_rows_to_extract, [columns_to_extract])
If 2nd arg is positive, it extracts rows from the top.
If 2nd arg is negative, it extracts rows from the bottom.
If 3rd arg is positive, it extracts columns starting from the left.
If 3rd arg is negative, it extracts columns counting from the right.
You may wonder: How is TAKE() different from CHOOSECOLS() and CHOOSEROWS()?
CHOOSEx functions returns specific rows or columns specified in their 2nd argument. Although you can add optional 3rd, 4th, etc. args to return multiple rows or columns.
Whereas TAKE function returns the number of rows or columns counting from 1 for that many rows or columns specified in the argument (2nd arg for row, 3rd arg for column).
PRO TIP: To make the range dynamic, convert the range to a table, that way the last row or column will always be updated automatically if the table grows/shrinks.
Let’s look at some examples of how it works. We have a dataset as below. The range starts from cell B21 and ends in F32.
Example 1: Get first 3 rows months of product sales = Extract 3 rows from the top, all columns.
Solution: TAKE(B21:F32, 3)
This will return the following:
| Jan | Chai | 35 | 18 | 630 |
| Feb | Chang | 20 | 19 | 380 |
| Mar | Aniseed Syrup | 11 | 10 | 110 |
Example 2: Get last 3 months of product sales = Extract 3 rows from the bottom, all columns).
Solution: TAKE(B21:F32, -3)
This will return the following:
| Oct | Ikura | 9 | 31 | 279 |
| Nov | Queso Cabrales | 20 | 21 | 336 |
| Dec | Queso Manchego | 21 | 38 | 798 |
Example 3: Get last 3 months of product sales and show just the total sales for each = Extract 3 rows from the bottom and 1 column from the right).
Solution: TAKE(B21:F32, -3, -1)
This will return the following because we specified to take just one column from the end.
| 279 |
| 336 |
| 798 |
We can nest other functions in TAKE as well to do more advanced extraction.
Example 4: Calculate the AVERAGE unit price of the last 5 items.
Solution: AVERAGE(TAKE(E21:E32,-5)) [where E column is the UnitPrice column]
And you will get the result: 45.4
Example 5: Show 4 items from bottom of the list that had sales >=$200.
Solution: TAKE(FILTER(C21:F32,F21:F32>=200),-4) [where C column is the ProductName column. We’re specifying the range starting from C instead of B to intentionally exclude the Month from the result.]
You will get this result:
| Mishi Kobe Niku | 6 | 97 | 582 |
| Ikura | 9 | 31 | 279 |
| Queso Cabrales | 16 | 21 | 336 |
| Queso Manchego | 21 | 38 | 798 |
Of course, you can also wrap the whole thing in SORT function either before filtering or after taking the result depending on your specific needs.
Example 6: Get 3 least selling products (i.e. by total sales, pick bottom 3).
Solution: TAKE(SORTBY(C21:C32, F21:F32,-1),-3) [where C column is the ProductName column.]
For this solution, we are first sorting the desired range in descending order (-1 argument in SORT, then specifying the bottom three rows by -3 argument in TAKE function that wraps the SORT. As a result, we get:
| Anton’s Gumbo Mix |
| Aniseed Syrup |
| Anton’s Cajun Mix |
As you can see there are myriad of uses and numerous ways of using the TAKE function. In the next blog post, I’ll describe the DROP function along with its advanced use cases.
