Excel TAKE Function: Extract First or Last Rows in Dynamic Formulas

Free practice workbook

Get the TAKE Workbook

The exact file from the tutorial, ready to use.

Practice TAKE with FILTER, SORT, VSTACK, and real-world marketing data.

Enter your email for instant access.

Download the Workbook

Free. Instant access. No setup needed.

How does the TAKE function work in Excel?

The TAKE function extracts the first or last N rows or columns from an array. Use it to grab moving targets like the last 7 days of sales, the top 3 campaigns, or the bottom 5 expense entries.

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.

To capture the last 7 sales from a Table named “Sales”:

=TAKE(Sales, -7)

Positive numbers take from the top or left. Negative numbers take from the bottom or right.

To get the first 3 columns instead, omit the rows argument:

=TAKE(Sales, , 3)

Combine both arguments to get the last 7 rows and the first 3 columns at once:

=TAKE(Sales, -7, 3)

The walkthrough below covers four real-world examples and the comparison between TAKE, DROP, CHOOSEROWS, and CHOOSECOLS.

What is the basic TAKE syntax?

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.

TAKE vs CHOOSECOLS, CHOOSEROWS, and DROP (which to use when)

TAKE has three close cousins in the array function family. Here’s when to use each:

TAKE and DROP are mirror images.

• =TAKE(array, 5) returns the first 5 rows.
• =DROP(array, 5) returns everything except the first 5 rows.

CHOOSEROWS and CHOOSECOLS handle non-contiguous selection, which TAKE cannot do.

• Need rows 1, 3, and 7? Use CHOOSEROWS.
• Need the last 7 rows? Use TAKE.

For example, CHOOSECOLS can extract the 1st and 4th columns of a table and display them in reverse order:

=CHOOSECOLS(Table_C0, 4, 1)

Use TAKE when you need a contiguous block from the start or end. Use the others when you need precise selection.

How do you calculate the average of the last N rows?

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.

How do you get the last N rows that match a condition?

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)

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.

For more on FILTER with multiple conditions, see our guide on the FILTER function with multiple criteria.

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.

How do you get both the first and last row in one TAKE formula?

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} )

How do you combine tables and extract the top N 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:

1. Combine the two tables using a VSTACK function.
2. Sort the combined tables by the [Average Cost / Conversion] column in ascending order using the SORT function.
3. Select only the first and last columns of the combined table using the CHOOSECOLS function.
4. 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.

For more VSTACK patterns including stacking sheets and handling mismatched columns, see our full VSTACK guide.

=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. For a deeper walkthrough of SORT, SORTBY, and other modern array functions, see our guide to new Excel functions in Microsoft 365.

=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.

What are the limitations and common errors of TAKE?

TAKE works in Excel for Microsoft 365, Excel for the web, Excel 2024, and the Excel mobile apps. It is not available in Excel 2021, Excel 2019, Excel 2016, or earlier desktop versions.

Common errors

• #CALC! error: Either the rows or columns argument is set to 0. Use a non-zero value, or omit the argument entirely.
• #NUM! error: The array argument is too large for Excel to process.
• #SPILL! error: The cells where TAKE wants to spill its results are not empty. Clear the cells below or to the right of the formula.

Wrong result with empty cells

TAKE counts every row in the source range, including blanks. =TAKE(B4:B100, -6) on a range with only 7 filled cells returns the last 6 cells of B4:B100 (mostly empty), not the last 6 filled cells.

Fix: use a Table reference like =TAKE(Table1[Sales], -6), or wrap the source in TRIMRANGE or FILTER first.

TAKE on a Table column auto-expands

When the underlying Table grows, the TAKE result updates automatically. This is the cleanest dynamic pattern in modern Excel.

Frequently Asked Questions

What does the TAKE function do in Excel?

TAKE returns the first or last N contiguous rows or columns from an array. Syntax: =TAKE(array, rows, [columns]). A negative rows or columns value takes from the end of the array instead of the start.

What is the difference between TAKE and DROP in Excel?

TAKE keeps the first or last N rows or columns. DROP removes the first or last N rows or columns. They are mirror functions: =TAKE(array, 5) and =DROP(array, -COUNT(array)+5) return similar results from opposite directions. Use TAKE when you want to keep a portion. Use DROP when you want to remove a portion.

What is the difference between TAKE and CHOOSEROWS?

TAKE returns contiguous rows from the start or end of an array. CHOOSEROWS returns specific rows by index in any order. =TAKE(array, 3) returns rows 1, 2, 3. =CHOOSEROWS(array, 1, 3, 5) returns rows 1, 3, and 5. Use CHOOSEROWS when the rows you need are not contiguous.

How do you get the last 5 rows of a column in Excel?

Use =TAKE(range, -5). The negative sign tells TAKE to start from the bottom. For a Table column, use =TAKE(Table1[Column], -5) to get a result that auto-expands as new rows are added.

Is TAKE available in Excel 2021?

No. TAKE is only available in Excel for Microsoft 365, Excel for the web, Excel 2024, and the Excel mobile apps. For Excel 2021 and older, use OFFSET combined with COUNTA, or upgrade to Excel for Microsoft 365.

Why does my TAKE formula return blank cells?

TAKE counts every row in the source range, including empty cells. If the range has trailing blanks, TAKE returns those blanks. Fix by using a Table reference, by wrapping the source in FILTER to remove empties, or by using TRIMRANGE to strip outer blank rows before TAKE sees the data.

Download the workbook

TAKE is part of the modern Excel function family that includes FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE, and VSTACK.

Our Modern Excel Functions course covers all of them with real-world examples like the ones in this guide.

Featured Course

Master Excel’s Essential Modern Functions

FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses