Microsoft Excel

How to Use the TAKE Function in Microsoft Excel

Chris Menard

Chris Menard

— 2 min read
How to Use the TAKE Function in Microsoft Excel

The TAKE function in Excel lets you extract a specific number of rows or columns from a range or array. It's especially powerful when combined with other functions like SUM and AVERAGE to create dynamic calculations. In this tutorial, Chris Menard demonstrates how to use TAKE with practical examples.

Watch the full tutorial:

What Is the TAKE Function?

TAKE is a dynamic array function introduced in Excel 365. Its syntax is:

=TAKE(array, rows, [columns])
  • array — The range or array to extract from
  • rows — Number of rows to take. Positive = from the top, negative = from the bottom
  • columns — (Optional) Number of columns to take
Excel table with Period and Revenue columns showing monthly data from January 2025 to March 2026
A structured Excel table with monthly revenue data — the source for the TAKE function.

Extracting the Last N Rows

The most common use case is grabbing the last few entries from a growing dataset. For example, if you have a table of monthly revenue data:

=TAKE(tbl_rev[Revenue], -3)

This returns the last 3 revenue values from the table. The negative number tells Excel to count from the bottom. As new months are added to the table, the formula automatically updates to always show the most recent 3 entries.

Excel formula bar showing =TAKE(tbl_rev[Revenue],-3) with tooltip explanation
The TAKE function with a negative rows argument extracts the last 3 values from the table.

Combining TAKE with SUM and AVERAGE

Where TAKE really shines is in combination with aggregate functions. To get the total of the last 3 months of revenue:

=SUM(TAKE(tbl_rev[Revenue], -3))

For the average of the last 3 months:

=AVERAGE(TAKE(tbl_rev[Revenue], -3))

These formulas are fully dynamic — when you add new data to the table, the calculations automatically adjust to include the latest entries. No need to update cell references manually.

Excel spreadsheet showing SUM and TAKE combined formula with Total and Average calculations
Combining SUM with TAKE creates dynamic totals that always reference the last N rows.

TAKE vs Other Approaches

Before TAKE, you'd need complex formulas with OFFSET, INDEX/MATCH, or INDIRECT to extract the last N rows from a range. TAKE simplifies this to a single, readable function. It's part of the same family of dynamic array functions as SORTBY, UNIQUE, and FILTER.

Want to learn more? Visit courses.chrismenardtraining.com for online training courses.

Read more