How the TAKE Function works in Excel
At its core, Excel’s TAKE function allows you to extract the first (or last) N number of rows or columns from a table (i.e., array).
This is a great way to extract a ‘moving’ target from a table, like the “last 7 days profit”, or the “top 3 sales”.
Excel’s TAKE function has the following syntax:
=TAKE(array, rows, [columns] )- array: The array from which to take rows or columns.
- rows: The number of rows to take. A negative value takes from the end of the array.
- columns: The number of columns to take. A negative value takes from the end of the array.
For example, if we have a table named “Sales”, and we need to capture the last 7 sales (rows), the formula will appear as follows.
=TAKE(Sales, -7)Note: positive numbers indicate capturing from the top or left of a table, while negative numbers indicate capturing from the bottom or right of a table.
If we want the first 3 columns of the table, we will omit the rows argument (omitting this means to capture all rows of the table) and only define the columns argument.
=TAKE(Sales, , 3)Of course, we could combine these to get the last 7 rows and the first 3 columns of the table.
=TAKE(Sales, -7, 3)Any of these examples takes the original dataset and reduces it down to a subset. The magic lies in what you do with that subset of data.
Let’s break down 4 real-world uses of the TAKE function mixed with other functions.
- Calculate the average spend of the last 5 marketing campaigns
- List the names of the last 5 marketing campaigns that spent over $1,000
- List the highest and lowest-cost marketing campaigns (in a single formula)
- List the top 5 marketing campaigns by average cost over two years (data coming from two separate tables)
Excel’s Take Function – Basic Usage
Starting with a table named “Table_C0”…
To extract the first 2 columns from the table, the formula would be…
=TAKE(Table_C0, , 2)
By omitting the rows argument, TAKE will default to returning all rows of the array.
Difference between TAKE and CHOOLECOLS Functions
I hear you asking, “Isn’t that what the CHOOSECOLS function does?”
Yes, CHOOSECOLS can extract the first two columns from the array. What makes TAKE different from CHOOSECOLS is that CHOOSECOLS allows you to select any column(s) in any order you need.
For example, we could use CHOOSECOLS to extract the 1st and 4th columns of a table and display them in reverse order.
=CHOOSECOLS(Table_C0, 4, 1)
The TAKE function cannot extract non-contiguous rows or columns. TAKE will always extract columns in contiguous order from a defined direction.
This may sound like a limitation, but the following examples will showcase where contiguous row/column extraction is beneficial.
Average of Last Rows Using TAKE Function
In this example, we will use Excel’s TAKE function to calculate the average spend of the last 5 marketing campaigns.
Using a table named “Table_C1”…
Begin by extracting the last 5 rows from the [Marketing Spend] column of the table.
=TAKE(Table_C1[Marketing Spend], -5)
This extracts the last 5 values from the selected column. Now we wrap the result of the TAKE function inside an AVERAGE function.
=AVERAGE(TAKE(Table_C1[Marketing Spend], -5) )
Because the TAKE function is dynamic, when new rows are added to the table, the TAKE function extracts the new “last 5 rows” and sends those values to the AVERAGE function for processing.
Find Last Rows with Conditional Logic Using TAKE Function
In this example, we will use Excel’s TAKE function to list the names of the last 5 marketing campaigns that spent over $1,000.
Using a table named “Table_C2”…
Begin by using the FILTER function to reduce the list of campaigns to only those where the [Marketing Spend] column has values greater than or equal to $1,000.
=FILTER(Table_C2[Campaign Name], Table_C2[Marketing Spend] >= 1000)
From this list of derived campaign names, use the TAKE function to extract the last 5 names from the list.
=TAKE(FILTER(Table_C2[Campaign Name], Table_C2[Marketing Spend] >= 1000), -5)
Remember: because the TAKE and FILTER functions are dynamic, when new rows are added to the table, the functions will incorporate the new rows into their analysis.
Get First and Last Rows Using TAKE Function
In this example, we will use Excel’s TAKE function to list the highest and lowest-cost marketing campaigns using a single formula.
Using a table named “Table_C3”…
Begin by using the SORTBY function to sort the [Campaign Name] column in descending order by the [Average Cost / Conversion] column’s values.
=SORTBY(Table_C3[Campaign Name], Table_C3[Average Cost / Conversion], -1)
To extract the top entry from this list of campaign names, we nest this formula within a TAKE function and extract the 1st row from the top.
=TAKE(SORTBY(Table_C3[Campaign Name], Table_C3[Average Cost / Conversion], -1), 1)
To extract the bottom entry from the list of sorted campaign names, we nest the formula within a TAKE function and extract the 1st row from the bottom using a “-1” in the rows argument.
=TAKE(SORTBY(Table_C3[Campaign Name], Table_C3[Average Cost / Conversion], -1), -1)
Using one TAKE formula to get two answers
You can flex your Excel superpowers by extracting both the first and last values from the sorted list using a single formula.
This is accomplished by asking for the “first row from the top” along with the “first row from the bottom”.
To do this, we define the “1” and the “-1” as a list.
{1; -1}Lists (also known as arrays) are defined by placing the target values within a set of curly braces.
The updated formula appears as follows.
=TAKE(SORTBY(Table_C3[Campaign Name], Table_C3[Average Cost / Conversion], -1), {1; -1} )
It may not be obvious, but both results are generated using a single formula.
NOTE: The use of the semi-colon is to get the results to spill vertically; in a column fashion. If you need the results to spill horizontally (in a row fashion), use a comma to separate the values in the curly-brace list.
=TAKE(SORTBY(Table_C3[Campaign Name], Table_C3[Average Cost / Conversion], -1), {1, -1} )
Combine Data From Multiple Tables and Extract Specific Rows
Our final example will use Excel’s TAKE function to list the top 5 marketing campaigns by average cost over two years. The data will be combined from two separate tables named “Table_2022” and “Table_2023”.
The generalized steps are as follows:
- Combine the two tables using a VSTACK function.
- Sort the combined tables by the [Average Cost / Conversion] column in ascending order using the SORT function.
- Select only the first and last columns of the combined table using the CHOOSECOLS function.
- Retain only the first 5 rows of the table using the TAKE function.
Use Excel VSTACK to Combine Tables
Step 1 will combine the tables using Excel’s VSTACK function. Stack the table named “Table_2023” atop the table named “Table_2022” with the following formula.
=VSTACK(Table_2023, Table_2022)
Use Excel SORT Function to Sort Tables
Step 2 will sort the newly combined table using Excel’s SORT function.
Sort the combined table by the 4th column in ascending order using the following formula.
=SORT(VSTACK(Table_2023, Table_2022), 4)
Use Excel CHOOSECOLS to Remove Columns
Step 3 will select specific columns, removing the unwanted columns, using Excel’s CHOOSECOLS function.
Target the 1st and 4th columns of the table to discard the unwanted columns (i.e., perform vertical filtering) using the following formula.
=CHOOSECOLS(SORT(VSTACK(Table_2023, Table_2022), 4), 1, 4)
Use Excel TAKE to Extract Rows
Step 4 will retain only the first 5 rows of the table using Excel’s TAKE function, delivering the result to us.
Final Thoughts about Excel’s TAKE Function
The simplicity of the TAKE function portrays a false sense of importance when compared to more complex functions. However, without its help, those more sophisticated functions would be hard-pressed to achieve the goal. Solving more complex problems is typically a team effort.
As with many Excel functions, their powers are best used when in the service of other functions. You should always be thinking about how you can combine functions in a single formula to create super-functions.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
