TAKE returns a specified number of rows or columns from the start or end of an array or range. It lets you quickly slice data without sorting, filtering, or manual copying.
=TAKE(array, rows, [columns])What does TAKE do?
TAKE is a dynamic array function that pulls a set number of rows or columns from either the beginning or the end of a range. You give it an array, tell it how many rows or columns you want, and it returns that slice as a new array. Positive numbers count from the start; negative numbers count from the end.
This matters because most reports need just a portion of the data—the top 5 performers, the last 3 months, or the first few columns of a wide table. Before TAKE, you might sort and copy, or wrestle with INDEX and ROW formulas. TAKE does it in one formula that updates automatically when the source data changes.
Imagine you maintain a sales log and your manager asks for the five most recent orders every morning. Instead of sorting the whole sheet, you can use =TAKE(A2:F100, -5) to return the last five rows instantly. The formula stays put and always shows the latest entries, saving you repetitive manual work.
When to use TAKE
- Show only the top N rows of a sorted list, such as the first 10 customers by revenue.
- Display the last few entries of a log, like the most recent transactions or submissions.
- Extract the first few columns of a wide dataset for a summary view.
- Pull the last column of a table, such as the final status or total column.
- Create a dynamic dashboard section that always shows the latest records.
- Combine with SORT or FILTER to build compact, self-updating reports.
TAKE syntax
=TAKE(array, rows, [columns])| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array you want to take from. Example: A2:F100. |
rows | Required | The number of rows to return. Positive counts from the top; negative counts from the bottom. Example: 5 returns the first 5 rows; -3 returns the last 3 rows. |
columns | Optional | The number of columns to return. Positive counts from the left; negative counts from the right. If omitted, all columns are returned. Example: 2 returns the first 2 columns; -1 returns the last column. |
What it returns: A dynamic array containing the requested rows and columns from the source array. If the result is a single value, it spills as one cell; otherwise it spills into neighbouring cells.
TAKE examples
1 Example 1: Get the first three rows of a product list
You have a small product table and want to see only the first three products. The formula =TAKE(A2:C6, 3) takes the range A2:C6 and returns the first 3 rows, all columns. It spills the result into the target area, showing Product, Category, and Price for the first three items.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | |||
| 2 | Laptop | Electronics | 1200 | Laptop | ||
| 3 | Mouse | Accessories | 25 | |||
| 4 | Keyboard | Accessories | 45 | |||
| 5 | Monitor | Electronics | 300 | |||
| 6 | Webcam | Electronics | 80 | |||
| 7 |
2 Example 2: Show the last two orders from a sales log
A sales log lists orders in chronological order, and you need the two most recent. =TAKE(A2:D6, -2) uses a negative row count to take the last 2 rows from A2:D6. The result spills into the target area, showing the Order ID, Customer, Amount, and Date for the two latest orders.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order ID | Customer | Amount | Date | |||
| 2 | 1001 | Acme Corp | 450 | 2024-01-10 | 1004 | ||
| 3 | 1002 | Beta Ltd | 320 | 2024-01-12 | |||
| 4 | 1003 | Gamma Inc | 780 | 2024-01-15 | |||
| 5 | 1004 | Delta Co | 210 | 2024-01-18 | |||
| 6 | 1005 | Epsilon LLC | 950 | 2024-01-20 | |||
| 7 |
3 Example 3: Extract the first two columns of an employee table
You have a wide employee table and only need the first two columns: Name and Department. =TAKE(A2:D6, , 2) omits the rows argument (leaving it blank) and sets columns to 2. TAKE returns all rows but only the first 2 columns, spilling the Name and Department for every employee.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Department | Salary | Start Date | |||
| 2 | Alice | HR | 55000 | 2020-03-01 | Alice | ||
| 3 | Bob | IT | 72000 | 2019-07-15 | |||
| 4 | Carol | Finance | 68000 | 2021-01-20 | |||
| 5 | David | IT | 75000 | 2018-11-05 | |||
| 6 | Eve | Marketing | 60000 | 2022-05-10 | |||
| 7 |
4 Example 4: Get the last column of a monthly sales table
A monthly sales table has months across columns, and you want only the latest month's figures. =TAKE(A2:D6, , -1) omits rows and uses -1 for columns, so TAKE returns all rows but only the last column (April). The result spills the April sales values for each region.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | |||
| 2 | North | 100 | 120 | 130 | 130 | ||
| 3 | South | 90 | 110 | 115 | |||
| 4 | East | 80 | 95 | 105 | |||
| 5 | West | 120 | 130 | 140 | |||
| 6 | Central | 70 | 85 | 90 | |||
| 7 |
5 Example 5: Combine TAKE with SORT to show top 3 sales
You want the top three sales amounts from a list. First, SORT(B2:B6, 1, -1) sorts the amounts in descending order. Then TAKE(..., 3) takes the first 3 rows of that sorted array. The formula =TAKE(SORT(B2:B6, 1, -1), 3) returns the three highest amounts, spilling them into the target area.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Salesperson | Sales | |||
| 2 | John | 500 | 890 | ||
| 3 | Sarah | 750 | |||
| 4 | Mike | 620 | |||
| 5 | Emma | 890 | |||
| 6 | Tom | 430 | |||
| 7 |
Common TAKE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The rows or columns argument is not a number, or the array argument is not a valid range. | Ensure rows and columns are numeric. Use positive or negative integers, or omit columns if not needed. |
| #CALC! | The requested number of rows or columns is zero, or the result is empty. | Use a non-zero number. If you need all rows, omit the rows argument or use a number equal to the row count. |
| #SPILL! | The target area is blocked by existing data or the spill range overlaps other cells. | Clear the cells below and to the right of the target, or move the formula to an empty area. |
| #NAME? | The function name is misspelled or the version of Excel does not support TAKE. | Check the spelling. TAKE requires Excel 365 or Excel 2021. Update Excel if needed. |
Tips for using TAKE
- Use negative numbers in rows or columns to take from the end of the array.
- Omit the columns argument to return all columns; omit rows by leaving a blank between commas.
- Combine TAKE with SORT or FILTER to create dynamic top-N or bottom-N lists.
- TAKE returns a dynamic array, so leave enough empty space for the result to spill.
- Use TAKE on the entire table range (e.g., A2:F100) so it automatically includes new rows.
- If you need a single value, wrap TAKE in INDEX or use the @ operator to prevent spilling.
Frequently asked questions
What is the difference between TAKE and DROP?
TAKE returns the specified number of rows or columns from the start or end, while DROP removes them and returns the rest.
Can TAKE return rows from the middle of a range?
No, TAKE only works from the start or end. To get middle rows, combine DROP and TAKE, e.g., =TAKE(DROP(range, 2), 3).
Does TAKE work with horizontal ranges?
Yes, TAKE works with any array or range. Use the columns argument to take from the left or right.
Is TAKE available in Excel 2019?
No, TAKE is available in Excel 365 and Excel 2021. It is not in older versions.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TAKE reference