TAKE

Return a set number of rows or columns from the start or end of an array with TAKE.

Lookup & Reference
|
Excel 365 only
|
Google Sheets Not supported

Spreadsheet editor

Syntax

=TAKE(array, rows, [columns]) Returns: Array

Arguments

Argument Required Description
array Yes The range or array you want to trim.
rows Yes The number of rows to return. Use a negative number to count from the end of the array.
columns No The number of columns to return. Use a negative number to count from the end of the array.

About

The TAKE function returns a block of rows or columns from the start or end of an array. Give it your data, then say how many rows you want and, if you like, how many columns. Positive numbers keep items from the start, and negative numbers keep them from the end.

Use TAKE when you need a live top-N or bottom-N view that resizes itself when your data changes. It pairs well with SORT and SORTBY, so you can rank a table first and then keep only the top few rows. It is also handy for trimming a FILTER result down to a short preview.

TAKE is a dynamic array function, so the result spills into the cells next to your formula. If you want to remove rows or columns instead of keeping them, use DROP, which is the mirror image of TAKE.

Examples

Show the last few rows

See how a negative number flips TAKE around to count from the end. Change the revenue figures and the last three months always stay in view.

Spreadsheet editor

Trim rows and columns at once

Try passing both a rows and a columns count to grab a corner of a table. Edit either number to pull a bigger or smaller block from the top-left.

Spreadsheet editor

Build a top-3 leaderboard

Watch SORT rank the reps by sales, then TAKE keep the top three. Edit a sales figure and the leaderboard reshuffles on the spot.

Spreadsheet editor

Watch out for

⚠

Using zero for rows or columns

Setting rows or columns to 0 returns a #CALC! error because TAKE cannot return an empty array.

→ Use at least 1 for the count, or use a negative number to take from the end instead of 0.

⚠

Asking for more than exists

You expect TAKE to pad the result when you request more rows than the array has.

→ TAKE returns only what is available, so asking for 10 rows from a 6-row array simply returns all 6. There is no error and no padding.

⚠

Spill range is blocked

The result needs to spill into nearby cells, but something is already in the way, so you see a #SPILL! error.

→ Clear the cells next to your formula so the array has room to expand.

⚠

Function not available

TAKE returns a #NAME? error in older Excel versions that do not include it.

→ TAKE needs Microsoft 365 or Excel for the web. On older versions, use INDEX to pull specific rows instead.

Tips & notes

TAKE counts from the start with positive numbers and from the end with negative numbers. You can combine the rows and columns arguments to trim the array in both directions at once.

Common questions

What does a negative number do in TAKE?

A negative rows or columns value tells TAKE to count from the end of the array, so =TAKE(A2:A20, -3) returns the last 3 rows.

Do I need to sort my data before using TAKE for a top-N report?

Usually yes. TAKE just trims the array you give it, so wrap your range in SORT or SORTBY first, then keep the first N rows.

What is the difference between TAKE and DROP?

TAKE keeps the rows or columns you name, while DROP removes them and returns everything else. They are opposites of each other.

Does TAKE work in Google Sheets?

No. TAKE is a Microsoft 365 function. In Google Sheets you can get a similar result with ARRAY_CONSTRAIN or QUERY.