The TAKE function in Excel allows you to extract a specified number of rows or columns from an array or range. This function simplifies data manipulation, especially when working with large datasets, by enabling you to quickly isolate the data you need. You can choose to extract from the beginning or the end of the array, providing flexibility in how you subset your data.
Syntax
The TAKE function uses the following syntax:
=TAKE(array, rows, [cols])
Arguments:
The TAKE function requires two mandatory arguments and accepts one optional argument:
-
array: This is the range or array from which you want to extract rows or columns. This argument is mandatory and can be a cell range, a static array, or the result of another function. -
rows: This mandatory argument specifies the number of rows to extract. Ifrowsis a positive number, the function extracts rows from the top of the array. If it is a negative number, the function extracts rows from the bottom of the array. -
cols: This optional argument specifies the number of columns to extract from the array. A positive value extracts columns from the left, while a negative value extracts columns from the right. If omitted, all columns are returned.
Important Characteristics of the TAKE Function
The TAKE function creates a new array consisting of the specified rows and columns. Here are some important features:
-
If the value of
rowsorcolsis greater than the size of the inputarray, theTAKEfunction returns all rows or columns of thearray. -
If the input
arrayis empty, theTAKEfunction returns an array of 0s, based on therowsandcolsarguments. -
If the
arrayargument is empty, the function returns a#VALUE!error. -
When the
rowsorcolsargument is set to 0, theTAKEfunction returns a#CALC!error.
Examples of the TAKE Function
To illustrate the TAKE function, let’s explore different input values for each argument.
Here’s a sample dataset with 3 rows and 3 columns that will be used in the below examples.
| A | B | C | |
|---|---|---|---|
| 1 | 10 | 20 | 30 |
| 2 | 40 | 50 | 60 |
| 3 | 70 | 80 | 90 |
In the first case, the cols argument is omitted, extracting the first 2 rows from the range B1:D2 while returning all columns.
The second example showcases column extraction. The rows argument is skipped, and the function extracts all rows and the first 2 columns from the range B1:D3.
The subsequent instance utilizes all arguments, extracting the first row and 2 columns from the dataset.
In the last example, both rows and cols are empty, and the TAKE function returns the entire input range B1:D3.
Now, let’s explore practical scenarios where the TAKE function proves invaluable.
Example 1 – Simple Use of the TAKE Function
Imagine organizing workshops with limited seating, using a first-come, first-served enrollment. You’ve gathered applications in an Excel dataset, including applicant names, contact details, workshop preferences, and enrollment timestamps.
After the application deadline, extract the first 5 applicants based on their enrollment timestamp and notify them of their status.
=TAKE(A1:D16,6,-3)
This extracts the first 6 rows from the dataset A1:D16, along with the last 3 columns, excluding the enrollment timestamps column. The first 6 rows include the header and the first 5 applicants.
This is a basic application of the TAKE function. Let’s explore other scenarios where it can be useful.
Example 2 – Calculating Average of Last N Numbers with TAKE Function
Suppose you want to analyze stock prices over a period and calculate the average closing price of the last N trading days to identify recent trends. You’ve downloaded stock price data that updates daily.
First, you must format the existing dataset as a Table in Excel to make the formula adaptable and encompass the newly added rows to remain effective.
Step 1: Select the dataset (e.g., $A$1:$E$11).
Step 2: Press Ctrl + T or choose Format as Table from the Home tab.
Step 3: In the Table Design tab, rename the table in the Table Name box (e.g., ‘stockprice’).
Now you can calculate the average closing price for the last 5 days using the TAKE function.
=AVERAGE(TAKE(stockprice,-5,-1))
This formula dynamically includes the most recent 5 rows in the calculation.
Other Methods for Calculating Average of Last N Numbers
Using FILTER and TAKE functions
Another way to dynamically adapt the formula is by combining the FILTER function with the TAKE function. The method we discussed above about creating a Table is far more effective than this approach.
Step 1: Use the FILTER function to filter the range of cells that are non-empty.
FILTER(E:E,E:E<>"")
Step 2: Use the TAKE function on the filtered data to extract the last 5 values.
=TAKE(FILTER(E:E,E:E<>""),-5)
Step 3: Calculate the average of the filtered data.
=AVERAGE(TAKE(FILTER(E:E,E:E<>""),-5))
This formula remains effective even after incorporating new rows.
Example 3 – Using SORTBY and TAKE Function
Suppose you have a dataset of movie titles, user ratings, release years, and genres. You want to extract the highest and lowest-rated movies.
Step 1: Sort the dataset by user ratings using the SORTBY function.
=SORTBY(A2:A16,B2:B16,-1)
This rearranges the “Movie Title” column (A2:A16) based on user ratings in the “User Rating” column (B2:B16) in descending order.
Step 2: Extract the highest and lowest-rated movies using the TAKE function.
=TAKE(SORTBY(A2:A16,B2:B16,-1),{1;-1})
The array input {1;-1} tells the TAKE function to extract the first and last row from the sorted array, displaying them vertically.
Example 4 – Using TAKE Function on Separate Datasets
Imagine you have sales data for electronics, clothing, and home goods in separate datasets and want to identify the top 3 selling products across all categories.
Step 1: Combine the datasets using the VSTACK function.
=VSTACK(A3:C6,A10:C13,A17:C20)
This vertically combines the datasets from each category.
Step 2: Reorder the combined dataset using the SORT function based on the “Units Sold” column (column 2) in descending order.
=SORT(VSTACK(A3:C6,A10:C13,A17:C20),2,-1)
Step 3: Extract the top 3 selling products using the TAKE function.
=TAKE(SORT(VSTACK(A3:C6,A10:C13,A17:C20),2,-1),3)
This extracts the first 3 rows, showing the top 3 selling products.
The same logic can be used to combine data from different worksheets or combine them horizontally using HSTACK and then use TAKE to extract the desired data.
TAKE Function vs DROP Function
Suppose you have a dataset of soccer league standings with team names, matches played, wins, draws, losses, and points. You want to extract the top 5 teams and remove the last 5 teams.
The TAKE function can extract the top 5 teams.
=TAKE(A1:F17,6)
The DROP function removes the last 5 teams.
=DROP(A1:F17,-5)
The function excludes the last five rows indicated by the negative input value.
Both the TAKE and DROP functions are complementary tools for data manipulation in Excel, where you can extract a subset from a larger dataset. One function retains the data, while the other removes it.
In conclusion, the TAKE function is a versatile tool for extracting specific portions of data in Excel, making data analysis more efficient, and when combined with other functions like SORTBY, VSTACK, and FILTER, it can handle complex data manipulation tasks. By understanding its syntax and capabilities, you can significantly enhance your Excel skills and streamline your data analysis workflows.