Excel 2024 or laterDynamic arrays

TAKE

NEMEN in Dutch Excel

Keeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEFG
1EntryJournalAmount
225-1201BNK€1,250.00€ 1.250,0025-1205=TAKE(A2:C8, -3)=TAKE(A2:C8; -3)BNK€2,750.00€ 2.750,00
325-1202INK€3,400.00€ 3.400,0025-1206INK€640.00€ 640,00
425-1203VRK€920.00€ 920,0025-1207VRK€1,980.00€ 1.980,00
525-1204MEM€15,600.00€ 15.600,00
625-1205BNK€2,750.00€ 2.750,00
725-1206INK€640.00€ 640,00
825-1207VRK€1,980.00€ 1.980,00
=TAKE(A2:C8, -3)
=TAKE(A2:C8; -3)

What TAKE does and when to use it

What it does: it keeps only the first or last rows and columns of a list or table, for example the three newest lines of a bank journal.

When to use it: for a top n, the latest n lines or the first columns of a wide export, usually behind SORT or FILTER. It replaces INDEX with a hand-built range, which you had to adjust when the list grew. To remove a header or total row instead, DROP says it more directly.

Two main gains:

  • Counts from either end: a negative number takes from the end, so the last n lines need no ROWS arithmetic.
  • Follows the data: behind SORT or FILTER the block resizes by itself when the data grows.

How Excel evaluates it

Excel first evaluates array, so a nested SORT or FILTER runs before TAKE sees anything. It then reads rows: a positive number selects that many rows counted from the top, a negative number that many counted from the bottom, and an empty argument selects all rows.

If the number is larger than the number of rows available, Excel takes everything instead of returning an error. It applies the same logic to columns, and the block that remains spills from the formula cell. If a cell in the spill area is occupied, you get #SPILL! instead of the block.

Watch out: TAKE counts positions, not values: without a SORT in front it returns the first or last lines of the source, not the largest or newest.

Mental model

A guillotine at one corner of a printed table: a positive number cuts from the top or left edge, a negative number from the bottom or right edge, and what falls off the other side is left out of the copy.

Terms used on this page: spill, array, argument, range, cell

Syntax

Official TAKE documentation
TAKE(array, rows, [columns])
TAKE(array; rows; [columns])
ArgumentTypeMeaning
arrayRequiredThe range or array to take rows or columns from.
rowsRequiredHow many rows to keep; negative counts from the end.
columnsOptionalHow many columns to keep; negative counts from the end.

Return value:The first or last rows and columns of array, as a spilled block

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Show the last three lines of a bank journal

You are at Veldhuis Installatie BV and have the extract of a few lines from the XAF auditfile. The extract is in entry order, so the newest postings are at the bottom. Before you start the cut-off test you want to see only the last three lines, without scrolling to the end of the sheet.

ABCDEFGH
1EntryJournalAmount
225-1201BNK€1,250.00€ 1.250,0025-1205=TAKE(A2:C8, -3)=TAKE(A2:C8; -3)BNK€2,750.00€ 2.750,00
325-1202INK€3,400.00€ 3.400,0025-1206INK€640.00€ 640,00
425-1203VRK€920.00€ 920,0025-1207VRK€1,980.00€ 1.980,00
525-1204MEM€15,600.00€ 15.600,00
625-1205BNK€2,750.00€ 2.750,00
725-1206INK€640.00€ 640,00
825-1207VRK€1,980.00€ 1.980,00
9
Formula cell (E2)Spilled result

Formula in E2

=TAKE(A2:C8, -3)
=TAKE(A2:C8; -3)

Result: 25-1205 | BNK | €2,750.00 / 25-1206 | INK | €640.00 / 25-1207 | VRK | €1,980.0025-1205 | BNK | € 2.750,00 / 25-1206 | INK | € 640,00 / 25-1207 | VRK | € 1.980,00

Step by step
  1. Excel evaluates array, A2:C8: 7 rows (25-1201 to 25-1207) and 3 columns.
  2. It reads rows: -3. The minus sign means counting from the bottom, so Excel keeps the last 3 of the 7 rows: the 5th, 6th and 7th, which are rows 6, 7 and 8 of the sheet.
  3. There is no columns argument, so all 3 columns stay.
  4. The block spills from E2 to G4: 25-1205 BNK 2,7502.750; 25-1206 INK 640; 25-1207 VRK 1,9801.980. The rows keep their source order, so 25-1207 is still at the bottom.
  5. Add a new line in row 9 and extend the range to A2:C9: the formula then shows 25-1206, 25-1207 and the new line, because it always takes the last 3 of whatever the range holds.

Watch out: The result is only the newest lines if the source is sorted by entry or date. If the export is in another order, TAKE still returns the last three rows, and they are no longer the newest.

More on this example: why it works, 2 variations

Why it works

TAKE counts positions, and the sign chooses the side to count from. -3 means the last three rows, whatever they contain, so the answer depends on the order of the source and not on any value in it.

Variations

=TAKE(A2:C8, 3)
=TAKE(A2:C8; 3)

The first three lines instead of the last three: a positive number counts from the top.

=TAKE(A2:C8, -3, 1)
=TAKE(A2:C8; -3; 1)

Only the entry numbers of the last three lines: add a columns argument of 1 to keep the first column.

Intermediate

First assets of the register without the depreciation column

For the additions test at Veldhuis Installatie BV you copy the first assets of the fixed asset register into your workpaper. You need asset, year and cost, but not the accumulated depreciation, which you recalculate yourself. The number of rows sits in F1, and you want to be able to type a number larger than the register without an error.

ABCDEFGH
1AssetYear acquiredCostAccumulated depreciationRows44
2FA-10120232023€32,500.00€ 32.500,00€9,750.00€ 9.750,00
3FA-10220222022€18,400.00€ 18.400,00€11,040.00€ 11.040,00FA-101=TAKE(A2:D7, F1, 3)=TAKE(A2:D7; F1; 3)20232023€32,500.00€ 32.500,00
4FA-10320252025€14,400.00€ 14.400,00€4,800.00€ 4.800,00FA-10220222022€18,400.00€ 18.400,00
5FA-10420212021€9,600.00€ 9.600,00€5,760.00€ 5.760,00FA-10320252025€14,400.00€ 14.400,00
6FA-10520252025€27,800.00€ 27.800,00€3,475.00€ 3.475,00FA-10420212021€9,600.00€ 9.600,00
7FA-10620242024€6,200.00€ 6.200,00€2,480.00€ 2.480,00
8
Formula cell (E3)InputSpilled result

Formula in E3

=TAKE(A2:D7, F1, 3)
=TAKE(A2:D7; F1; 3)

Result: FA-101 | 2023 | €32,500.00 / FA-102 | 2022 | €18,400.00 / FA-103 | 2025 | €14,400.00 / FA-104 | 2021 | €9,600.00FA-101 | 2023 | € 32.500,00 / FA-102 | 2022 | € 18.400,00 / FA-103 | 2025 | € 14.400,00 / FA-104 | 2021 | € 9.600,00

Step by step
  1. Excel evaluates array, A2:D7: 6 rows and 4 columns (asset, year, cost, accumulated depreciation).
  2. rows is F1, which holds 4. The number is positive, so Excel keeps the first 4 of the 6 rows: FA-101, FA-102, FA-103 and FA-104.
  3. columns is 3, so from those rows only the first 3 columns stay: asset, year and cost. The fourth column, accumulated depreciation, is cut off.
  4. The block of 4 rows by 3 columns spills from E3 to G6: FA-101, 2023, 32,50032.500; FA-102, 2022, 18,40018.400; FA-103, 2025, 14,40014.400; FA-104, 2021, 9,6009.600.
  5. Change F1 to 10. The register has only 6 rows, so TAKE returns all 6 instead of an error. Without that rule you would have to check the count with ROWS before every change.

Watch out: If F1 is empty, Excel reads it as 0 and TAKE returns #CALC! because nothing is left to show. Give F1 a default number.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

The optional third argument cuts the width, the second argument cuts the height, and both are applied to the same array in one step. Because asking for too many rows returns everything, an input cell with a number is safe to change.

More to watch out for

  • Because the columns are counted from the left, inserting a column in front of the register changes what the 3 columns contain. Check the headers after structural changes.

Variations

=TAKE(A2:D7, -F1, 3)
=TAKE(A2:D7; -F1; 3)

The last F1 assets instead of the first, which is how you reach the most recent additions in a register sorted by acquisition.

=TAKE(A2:D7, F1, -2)
=TAKE(A2:D7; F1; -2)

Keep the last two columns, cost and accumulated depreciation, for the first F1 assets.

Advanced

The three largest receivables for confirmation requests

From the receivables aging list of Veldhuis Installatie BV you select the largest balances as key items for confirmation requests. The list is in invoice order, not in order of size. The number of items to confirm sits in F1, so you can raise it when the coverage turns out too low.

ABCDEFGH
1InvoiceCustomerBalanceItems33
225-1301Kaya Design BV€8,450.00€ 8.450,00
325-1302Molenaar Bouw BV€23,100.00€ 23.100,0025-1303=TAKE(SORT(A2:C9, 3, -1), F1)=TAKE(SORT(A2:C9; 3; -1); F1)Van Dijk Transport BV€61,250.00€ 61.250,00
425-1303Van Dijk Transport BV€61,250.00€ 61.250,0025-1306Smits Bouw BV€38,900.00€ 38.900,00
525-1304Hendriks Metaal BV€15,800.00€ 15.800,0025-1308De Boer Groep BV€27,750.00€ 27.750,00
625-1305Mulder Horeca BV€4,200.00€ 4.200,00
725-1306Smits Bouw BV€38,900.00€ 38.900,00
825-1307Jansen Techniek BV€12,600.00€ 12.600,00
925-1308De Boer Groep BV€27,750.00€ 27.750,00
10
Formula cell (E3)InputSpilled result

Formula in E3

=TAKE(SORT(A2:C9, 3, -1), F1)
=TAKE(SORT(A2:C9; 3; -1); F1)

Result: 25-1303 | Van Dijk Transport BV | €61,250.00 / 25-1306 | Smits Bouw BV | €38,900.00 / 25-1308 | De Boer Groep BV | €27,750.0025-1303 | Van Dijk Transport BV | € 61.250,00 / 25-1306 | Smits Bouw BV | € 38.900,00 / 25-1308 | De Boer Groep BV | € 27.750,00

Step by step
  1. Excel works from the inside out, so SORT(A2:C9, 3, -1) comes first. It sorts the 8 rows on column 3, balance, in descending order (sort_order -1).
  2. The sorted block has the balances in this order: 61,25061.250 (25-1303), 38,90038.900 (25-1306), 27,75027.750 (25-1308), 23,10023.100 (25-1302), 15,80015.800 (25-1304), 12,60012.600 (25-1307), 8,4508.450 (25-1301), 4,2004.200 (25-1305). It is still 8 rows by 3 columns.
  3. TAKE receives that block and reads rows from F1, which holds 3. The number is positive, so it keeps the top 3 rows. Because the block is sorted, the top rows are the largest balances.
  4. The result spills from E3 to G5: 25-1303 Van Dijk Transport BV 61,25061.250; 25-1306 Smits Bouw BV 38,90038.900; 25-1308 De Boer Groep BV 27,75027.750.
  5. Coverage: 61,25061.250 + 38,90038.900 + 27,75027.750 = 127,900127.900 out of a total of 192,050192.050, which is 66.6%. Set F1 to 4 and the next balance, 23,10023.100 for Molenaar Bouw BV, joins the list and the coverage rises to 151,000151.000 / 192,050192.050 = 78.6%.

Watch out: If you leave out the -1 and write SORT(A2:C9, 3), the sort is ascending and TAKE returns the three smallest balances: 4,2004.200, 8,4508.450 and 12,60012.600. The list looks plausible, but it holds the wrong items.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

SORT puts the rows in the order you care about, and TAKE then cuts a fixed number from the top. Neither function knows about the other: TAKE receives a block like any other, which is why the pair works for any ranking, and why the second argument of SORT refers to a column of the block it sorts.

More to watch out for

  • When two balances are equal at the cut, TAKE keeps one and drops the other without any warning. Check the first value below your selection before you finalize the list.

Variations

=TAKE(SORT(A2:C9, 3, 1), F1)
=TAKE(SORT(A2:C9; 3; 1); F1)

The smallest F1 balances, in ascending order: 4,2004.200, 8,4508.450, 12,60012.600. Use it to look for understated or credit balances.

Master

Largest manual entries, with a clean message for an empty journal

In journal entry testing at Veldhuis Installatie BV you select the largest postings of one journal as key items. Journal and number of items sit in H1 and H2.

The workpaper needs entry, account and amount, not the journal code that is already on the tab. The formula must still behave when you type a journal without postings or a number above the number of postings.

ABCDEFGHIJ
1JournalEntryAccountAmountJournalMEM
2MEM25-091145004500€14,200.00€ 14.200,00Items33
3VRK25-091280008000€9,400.00€ 9.400,00
4MEM25-091311001100€5,600.00€ 5.600,0025-0914=LET( journal, A2:A9, hits, COUNTIF(journal, H1), keep_rows, H2, keep_cols, -3, IF(hits = 0, "No postings in this journal", TAKE(TAKE(SORT(FILTER(A2:D9, journal = H1), 4, -1), keep_rows), , keep_cols)) )=LET( journal; A2:A9; hits; COUNTIF(journal; H1); keep_rows; H2; keep_cols; -3; IF(hits = 0; "No postings in this journal"; TAKE(TAKE(SORT(FILTER(A2:D9; journal = H1); 4; -1); keep_rows); ; keep_cols)) )40004000€26,800.00€ 26.800,00
5MEM25-091440004000€26,800.00€ 26.800,0025-091145004500€14,200.00€ 14.200,00
6INK25-091545004500€17,350.00€ 17.350,0025-091616001600€11,900.00€ 11.900,00
7MEM25-091616001600€11,900.00€ 11.900,00
8BNK25-091711001100€22,000.00€ 22.000,00
9MEM25-091840004000€7,300.00€ 7.300,00
10
Formula cell (G4)InputSpilled result

Formula in G4

=LET(
  journal, A2:A9,
  hits, COUNTIF(journal, H1),
  keep_rows, H2,
  keep_cols, -3,
  IF(hits = 0, "No postings in this journal", TAKE(TAKE(SORT(FILTER(A2:D9, journal = H1), 4, -1), keep_rows), , keep_cols))
)
=LET(
  journal; A2:A9;
  hits; COUNTIF(journal; H1);
  keep_rows; H2;
  keep_cols; -3;
  IF(hits = 0; "No postings in this journal"; TAKE(TAKE(SORT(FILTER(A2:D9; journal = H1); 4; -1); keep_rows); ; keep_cols))
)

Result: 25-0914 | 4000 | €26,800.00 / 25-0911 | 4500 | €14,200.00 / 25-0916 | 1600 | €11,900.0025-0914 | 4000 | € 26.800,00 / 25-0911 | 4500 | € 14.200,00 / 25-0916 | 1600 | € 11.900,00

Step by step

  1. LET stores four names:

    • journal is A2:A9
    • hits is COUNTIF(journal, H1)
    • keep_rows is H2 (3)
    • keep_cols is -3

    COUNTIF finds MEM in rows 1, 3, 4, 6 and 8 of the data, so hits is 5.

  2. IF tests hits = 0. It is 5, so the FALSE branch runs and the text is not used.
  3. Inside out: FILTER(A2:D9, journal = H1) keeps the 5 MEM rows: 25-0911 (14,20014.200), 25-0913 (5,6005.600), 25-0914 (26,80026.800), 25-0916 (11,90011.900) and 25-0918 (7,3007.300).
  4. SORT(..., 4, -1) sorts those rows on their 4th column, amount, descending: 26,80026.800 (25-0914), 14,20014.200 (25-0911), 11,90011.900 (25-0916), 7,3007.300 (25-0918), 5,6005.600 (25-0913).
  5. The inner TAKE with keep_rows (3) keeps the top 3 of those 5 rows. The outer TAKE has an empty rows argument, so all 3 rows stay, and keep_cols (-3) keeps the last 3 of the 4 columns: entry, account and amount. The journal column is dropped.
  6. The result spills from G4 to I6: 25-0914, 4000, 26,80026.800; 25-0911, 4500, 14,20014.200; 25-0916, 1600, 11,90011.900. Together 52,90052.900. Set H2 to 9 and you get all 5 MEM rows; set H1 to KAS and hits is 0, so G4 shows No postings in this journal.

Watch out: The mirror-image mistake is a positive keep_cols: TAKE(..., , 3) keeps the first three columns, journal, entry and account, and drops the amount. The list has the right length and looks fine, but the amounts you meant to test are missing.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Two TAKE calls separate the two jobs: the inner one limits the rows after sorting, the outer one trims the columns with the rows argument left empty. TAKE returns all rows when you ask for more than exist, so no MIN against hits is needed. The explicit hits = 0 test is needed because FILTER without if_empty gives #CALC! for an unknown journal.

Do not use this for a statistically supported sample: the largest items are a judgmental selection and say nothing about the rest of the population. For a sample, use monetary unit sampling or a selection based on RANDARRAY.

For a quick one-off check in a file that must open in Excel 2021 or older, the AutoFilter Top 10 on a copy of the data gives the same rows without new functions, but it does not update.

More to watch out for

  • SORT's 4 refers to the fourth column of the filtered block, which here is also column D of the sheet. If you filter fewer columns first, the number changes with it.
  • LET names cannot look like cell references. A name such as hits is fine, but tax1 reads as the address of cell TAX1 and Excel refuses the formula.

Variations

=TAKE(TAKE(SORT(FILTER(A2:D9, A2:A9=H1), 4, -1), H2), , -3)
=TAKE(TAKE(SORT(FILTER(A2:D9; A2:A9=H1); 4; -1); H2); ; -3)

Without LET and the hits test: shorter, but an unknown journal gives #CALC! instead of a message.

Common mistakes

SymptomCauseFix
The first lines appear, not the largest amountsYou wrote TAKE(A2:C20, 3) to get the three largest amounts and received the first three lines of the sheet.Sort first: TAKE(SORT(A2:C20, 3, -1), 3) puts the largest amount on top before TAKE counts.
The last rows come back, not the last columnsYou wanted the last 3 columns, wrote TAKE(A2:D9, -3), and got the last 3 rows with all four columns.The second argument is always rows. Leave it empty and give the number in the third position: TAKE(A2:D9, , -3).
The last five lines instead of the first fiveThe sign is the wrong way round: -5 returns the last five lines when you meant the first five, and the list looks complete because it has the right length.Positive means start, negative means end. Check the first entry of the result against the source before you rely on the list.
#NAME? or #SPILL!The cell shows #NAME? in a colleague's file, or #SPILL! in yours.TAKE needs Excel 2024 or Microsoft 365; older versions do not know it. For #SPILL!, clear the cells the block needs: click the error and choose Select Obstructing Cells.

Used in combinations