## 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**:**rows**: The number of rows to take. A negative value takes from the end of the array.**columns**:

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 1^{st} and 4^{th} 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 1^{st} 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 1^{st} 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 4^{th} 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 1^{st} and 4^{th} 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.