Skip to content

Excel function · Lookup & reference

Excel TAKE function

TAKE returns as many rows and columns as you ask for from the start or the end of an array: the three highest sales, the last three stock movements. It is available only in Microsoft 365.

In Turkish Excel: AL

What it does

A positive second argument counts from the start and a negative one from the end: =TAKE(A2:C20,3) returns the first three rows and =TAKE(A2:C20,-3) the last three. The third argument trims columns in the same way.

TAKE is mostly used to cut down the result of other dynamic array functions. The first three rows of a table sorted largest first with SORT give a "top three" list; the first row of a FILTER result is the first record that meets the condition.

To drop rows from the start or end, use its partner DROP; to pick particular columns, use CHOOSECOLS. Outside Microsoft 365, similar ranges are built with INDEX or OFFSET.

Syntax

English Excel=TAKE(array, rows, [columns])
Turkish Excel=AL(dizi; satırlar; [sütunlar])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
arrayRequiredThe range or array to take rows and columns from.
rowsRequiredThe number of rows to take; a negative number takes from the end.
columnsOptionalThe number of columns to take; a negative number takes from the end. If omitted, all columns are returned.

Examples

1List the three highest sales

SORT orders the table by amount, largest first; TAKE keeps the first three rows of that result. The result spills into E2:G4.

E2fx=TAKE(SORT(A2:C7,3,-1),3)
List the three highest sales: example table
ABCDEFG
1RepRegionAmountRepRegionAmount
2EmmaWest48,500LiamNorth61,200
3LiamNorth61,200AvaNorth57,800
4OliviaWest52,300OliviaWest52,300
5NoahSouth29,750
6AvaNorth57,800
7MasonSouth33,100
  • E2 (the result spills into E2:G4)=TAKE(SORT(A2:C7,3,-1),3)

Liam (61,200) heads the list and Olivia (52,300) is third. When a rep's amount changes, the top three refresh by themselves.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Show the last three stock movements

The stock movements are listed in date order. With rows set to -3, TAKE takes three rows from the end of the list.

E2fx=TAKE(A2:C8,-3)
Show the last three stock movements: example table
ABCDEFG
1DateMovementQtyDateMovementQty
220-Oct-2026In12030-Oct-2026Out60
322-Oct-2026Out452-Nov-2026Out25
426-Oct-2026Out304-Nov-2026In50
528-Oct-2026In80
630-Oct-2026Out60
72-Nov-2026Out25
84-Nov-2026In50
  • E2 (the result spills into E2:G4)=TAKE(A2:C8,-3)

The last three movements land in E2:G4; the oldest is dated 30-Oct-2026 and the newest 4-Nov-2026. The order stays as it is in the source.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#CALC!

Cause: rows or columns is 0; an empty array cannot be returned.

Fix: Make sure the formula that gives the number returns at least 1 (or at most -1).

#SPILL!

Cause: A cell in the spill area is not empty.

Fix: Clear the cells below and to the right of the formula.

#NAME?

Cause: The file was opened in a version of Excel other than Microsoft 365.

Fix: In those versions, build the range with INDEX or OFFSET.

Good to know

  • If rows is larger than the number of rows in the array, there is no error; you simply get all the rows.
  • If you only need the five highest amounts, use =TAKE(SORT(C2:C20,1,-1),5); in older versions the same values can be taken one by one with LARGE.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Show the three highest sales from the sales list in a separate table with the rep and the amount

Convert to table

Bring your sales list into the tool and type this. Nexell sorts the list by amount, puts the top three rows into a separate table and shows it in a preview first; once you approve, you download it as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I list the top 3 values with their rows in Excel?

Sort the table by amount, largest first, and take the first three rows: =TAKE(SORT(A2:C20,3,-1),3). This needs Microsoft 365; in older versions, combine LARGE with INDEX and MATCH.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free