Dynamic arrays function

Excel TAKE function

Q: How do I get the last 5 rows in Excel?

Use =TAKE(range,-5). With a table or a whole-column range, new rows are included automatically.

Q: How do I show the top 3 values with names?

Use =TAKE(SORT(range,column,-1),3), where column is the position of the value column inside the range.

TAKE returns a number of rows or columns from the start or end of a range. A positive number takes from the top or left, a negative one from the bottom or right. Combined with SORT it gives a top-N list; on its own it grabs the latest entries of a log.

=TAKE(array, rows, [columns])

Arguments

array: The range or array to take from.
rows: How many rows: positive from the top, negative from the bottom. Leave it empty to keep all rows.
columns: Optional. How many columns: positive from the left, negative from the right.

TAKE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Week	Rep	Sales ($)
2	1	Kim	3400
3	2	Raj	4100
4	3	Lou	2900
5	4	Kim	5200
6	5	Ana	3800
7	6	Raj	4600
8	7	Lou	3100
9	8	Ana	4900

Download this sheet as an Excel practice file (.xlsx)

=TAKE(SORT(A2:C9,3,-1),3)
Result: spills 3 rows × 3 columns
4 Kim 5200
8 Ana 4900
6 Raj 4600
Sort by sales, largest first, then take three rows: the top three weeks, led by week 4 at 5,200.
=TAKE(A2:C9,-2)
Result: spills 2 rows × 3 columns
7 Lou 3100
8 Ana 4900
A negative number takes from the bottom: the last two weeks, 7 and 8.
=SUM(TAKE(C2:C9,-4))
Result: 16400
A rolling total of the last four weeks: 16,400. Point it at a Table column and it moves along as you add weeks.
=TAKE(A2:C9,1,-1)
Result: 3400
One row and one column from the right: the first week’s sales, 3,400.

How to use TAKE

Click a cell with room to spill.
Type =TAKE( and select the range.
Type the number of rows, negative to count from the bottom.
Optionally add a number of columns, then press Enter.

Common TAKE mistakes

Asking for 0 rows

TAKE(range,0) leaves nothing and returns #CALC!. Leave the rows argument empty to keep every row: TAKE(range,,2).

Taking a top 3 without sorting

TAKE keeps the original order. Sort first with SORT or SORTBY.

Older Excel

TAKE needs Excel for Microsoft 365, Excel 2024 or Excel for the web. In older versions use INDEX with LARGE, or OFFSET.

TAKE FAQ

How do I get the last 5 rows in Excel?

Use =TAKE(range,-5). With a table or a whole-column range, new rows are included automatically.

How do I show the top 3 values with names?

Use =TAKE(SORT(range,column,-1),3), where column is the position of the value column inside the range.

What happens if I ask for more rows than there are?

TAKE returns all the rows; it does not pad or return an error.

Guides that use TAKE

Related functions

Updated November 3, 2026