Excel 2024 or laterDynamic arrays

DROP

WEGLATEN in Dutch Excel

Cuts rows or columns off the start or end of a range and returns what is left, as a live spilled block.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCDEFG
1ReferenceCounterpartyAmountB-2501=DROP(A1:C7, 1)=DROP(A1:C7; 1)Kaya Design BV€12,100.00€ 12.100,00
2B-2501Kaya Design BV€12,100.00€ 12.100,00B-2502Hendriks BV-€4,235.00€ -4.235,00
3B-2502Hendriks BV-€4,235.00€ -4.235,00B-2503Mulder BV€6,050.00€ 6.050,00
4B-2503Mulder BV€6,050.00€ 6.050,00B-2504Belastingdienst-€8,470.00€ -8.470,00
5B-2504Belastingdienst-€8,470.00€ -8.470,00B-2505Smits Bouw BV€2,420.00€ 2.420,00
6B-2505Smits Bouw BV€2,420.00€ 2.420,00B-2506Van Dijk BV-€1,815.00€ -1.815,00
7B-2506Van Dijk BV-€1,815.00€ -1.815,00
=DROP(A1:C7, 1)
=DROP(A1:C7; 1)

What DROP does and when to use it

What it does: it removes rows from the top or bottom of a block and columns from its left or right, and returns the rest, for example a bank export without its header row.

When to use it: when an export arrives with a header, title line, total row or line-number column that must not end up in your analysis. It works on pasted data and on the output of other formulas. If you only need the first or last rows, use TAKE.

Two main gains:

  • Works on formula output: it needs no cell addresses, so it also trims the result of another formula.
  • Rows and columns in one formula: a positive number cuts from the top or left, a negative one from the bottom or right.

How Excel evaluates it

Excel first reads array and counts its rows and columns. It then looks at the sign of rows:

  • positive: that many rows are cut off the top
  • negative: that many rows are cut off the bottom
  • 0: no rows are cut

It does the same for columns, counting from the left for positive and from the right for negative numbers. What remains is a rectangle in the original order, and it spills from the formula cell.

If the cuts would leave no rows or no columns, the result is #CALC!, and if something already sits in the spill area, it is #SPILL!.

Watch out: The second argument is rows, not columns, and its sign decides the end: a positive number cuts from the top, a negative number from the bottom.

Mental model

A paper cutter with two blades per direction: a positive number trims the top or left edge, a negative number trims the bottom or right edge, and what stays on the table is the result.

Terms used on this page: spill, array, cell

Syntax

Official DROP documentation
DROP(array, rows, [columns])
DROP(array; rows; [columns])
ArgumentTypeMeaningDefault
arrayRequiredThe range or array to remove rows or columns from.
rowsRequiredRows to remove: positive from the top, negative from the bottom.
columnsOptionalColumns to remove: positive from the left, negative from the right.0

Return value:The array without the removed rows or columns

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

Remove the header row from a bank statement export

You are matching the bank statement of Veldhuis Installatie BV to the ledger. The bank's export sits in A1:C7 and starts with a header row. For your workpaper you want only the six statement lines, so the header must not be part of the list you copy or calculate on.

ABCDEFGH
1ReferenceCounterpartyAmountB-2501=DROP(A1:C7, 1)=DROP(A1:C7; 1)Kaya Design BV€12,100.00€ 12.100,00
2B-2501Kaya Design BV€12,100.00€ 12.100,00B-2502Hendriks BV-€4,235.00€ -4.235,00
3B-2502Hendriks BV-€4,235.00€ -4.235,00B-2503Mulder BV€6,050.00€ 6.050,00
4B-2503Mulder BV€6,050.00€ 6.050,00B-2504Belastingdienst-€8,470.00€ -8.470,00
5B-2504Belastingdienst-€8,470.00€ -8.470,00B-2505Smits Bouw BV€2,420.00€ 2.420,00
6B-2505Smits Bouw BV€2,420.00€ 2.420,00B-2506Van Dijk BV-€1,815.00€ -1.815,00
7B-2506Van Dijk BV-€1,815.00€ -1.815,00
8
Formula cell (E1)Spilled result

Formula in E1

=DROP(A1:C7, 1)
=DROP(A1:C7; 1)

Result: B-2501 | Kaya Design BV | €12,100.00 / B-2502 | Hendriks BV | -€4,235.00 / B-2503 | Mulder BV | €6,050.00 / B-2504 | Belastingdienst | -€8,470.00 / B-2505 | Smits Bouw BV | €2,420.00 / B-2506 | Van Dijk BV | -€1,815.00B-2501 | Kaya Design BV | € 12.100,00 / B-2502 | Hendriks BV | € -4.235,00 / B-2503 | Mulder BV | € 6.050,00 / B-2504 | Belastingdienst | € -8.470,00 / B-2505 | Smits Bouw BV | € 2.420,00 / B-2506 | Van Dijk BV | € -1.815,00

Step by step
  1. array is A1:C7: 7 rows and 3 columns. Row 1 is the header (Reference, Counterparty, Amount), rows 2 to 7 are the six statement lines.
  2. rows is 1. A positive number cuts from the top, so one row goes: the header row.
  3. columns is left out, so no column is removed and all 3 stay.
  4. What remains is 7 - 1 = 6 rows by 3 columns. The block spills from E1 to G6: E1 shows B-2501, Kaya Design BV, 12,10012.100 and the last line is B-2506, Van Dijk BV, -1,8151.815.
  5. Change the 1 to -1 and the cut moves to the bottom: the header stays and B-2506 disappears, which is the mistake to look for if your list starts with text instead of a reference.

Watch out: If the bank puts a title line above the header, one dropped row is not enough and the header stays in your list. Look at the first cell of the result: it must be a reference, not a column name.

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

Why it works

DROP counts from the start for a positive number and from the end for a negative one, and always returns a rectangle. Pointing a formula at A2:C7 would give the same lines, but DROP works on the whole export as it arrives, and also on a block produced by another formula, where there is no row 2 to point at.

More to watch out for

  • Anything typed in E1:G6 makes the formula show #SPILL! instead of the lines.

Variations

=DROP(A1:C7, -1)
=DROP(A1:C7; -1)

Remove the last row instead of the first, for an export that ends with a total.

=DROP(A1:C7, 1, 1)
=DROP(A1:C7; 1; 1)

Remove the header and the reference column as well, leaving counterparty and amount.

Intermediate

Trial balance export without the total row and line numbers

The client's trial balance for Veldhuis Installatie BV arrives as an export. The first column holds line numbers added by the export tool and the last row is a total row. You want only account, debit and credit, because a SUM over a block that still contains the total row counts every amount twice.

ABCDEFGHI
1LineAccountDebitCredit
21111001100€45,200.00€ 45.200,00€0.00€ 0,0011001100=DROP(A2:D7, -1, 1)=DROP(A2:D7; -1; 1)€45,200.00€ 45.200,00€0.00€ 0,00
32213001300€38,650.00€ 38.650,00€0.00€ 0,0013001300€38,650.00€ 38.650,00€0.00€ 0,00
43316001600€0.00€ 0,00€29,400.00€ 29.400,0016001600€0.00€ 0,00€29,400.00€ 29.400,00
54440004000€21,800.00€ 21.800,00€0.00€ 0,0040004000€21,800.00€ 21.800,00€0.00€ 0,00
65580008000€0.00€ 0,00€76,250.00€ 76.250,0080008000€0.00€ 0,00€76,250.00€ 76.250,00
7Total€105,650.00€ 105.650,00€105,650.00€ 105.650,00
8
Formula cell (F2)Spilled result

Formula in F2

=DROP(A2:D7, -1, 1)
=DROP(A2:D7; -1; 1)

Result: 1100 | €45,200.00 | €0.00 / 1300 | €38,650.00 | €0.00 / 1600 | €0.00 | €29,400.00 / 4000 | €21,800.00 | €0.00 / 8000 | €0.00 | €76,250.001100 | € 45.200,00 | € 0,00 / 1300 | € 38.650,00 | € 0,00 / 1600 | € 0,00 | € 29.400,00 / 4000 | € 21.800,00 | € 0,00 / 8000 | € 0,00 | € 76.250,00

Step by step
  1. array is A2:D7: 6 rows and 4 columns, the five accounts in rows 2 to 6 and the total row in row 7.
  2. rows is -1. A negative number cuts from the bottom, so the last row goes: row 7, with Total, 105,650105.650 and 105,650105.650.
  3. columns is 1. A positive number cuts from the left, so column A goes: the line numbers 1 to 5. This is the optional argument at work.
  4. What remains is 6 - 1 = 5 rows by 4 - 1 = 3 columns: account, debit, credit. The block spills from F2 to H6, starting with 1100, 45,20045.200, 0 and ending with 8000, 0, 76,25076.250.
  5. Check that nothing real was lost: debit is 45,20045.200 + 38,65038.650 + 21,80021.800 = 105,650105.650 and credit is 29,40029.400 + 76,25076.250 = 105,650105.650. Both equal the total row that was dropped, and debit equals credit.
  6. Without the optional third argument, DROP(A2:D7, -1) would keep the line numbers, so the block stays 4 columns wide. With rows 1 instead of -1, account 1100 would disappear and the total row would stay.

Watch out: DROP cuts by position, not by content. If the export one day ends with an extra blank row after the total, -1 removes the blank row and the total row stays in your block, without any error. Compare the sums of the result with the client's total.

More on this example: why it works, 2 variations

Why it works

The two arguments cut independently: rows trims one edge, columns trims another, and each has its own sign. That is why a single DROP handles a total row at the bottom and a numbering column at the left in one go, without helper columns or copying.

Variations

=DROP(A2:D7, -1)
=DROP(A2:D7; -1)

Keep the line numbers and remove only the total row.

=DROP(A2:D7, , 1)
=DROP(A2:D7; ; 1)

Remove only the line numbers and keep the total row, for when you want to compare the lines to the total.

Advanced

Stack two payables exports into one list without headers

For a duplicate invoice test at Veldhuis Installatie BV you received the purchase invoices of October (A1:C4) and November (D1:F5) as two exports side by side, each with its own header row. You want one list of all invoices.

If you stack the blocks as they are, the November header lands in the middle of the data, so each header has to go first.

ABCDEFGHIJK
1Invoice OctRelation OctAmount OctInvoice NovRelation NovAmount Nov25-3101=VSTACK(DROP(A1:C4, 1), DROP(D1:F5, 1))=VSTACK(DROP(A1:C4; 1); DROP(D1:F5; 1))C2001€4,235.00€ 4.235,00
225-3101C2001€4,235.00€ 4.235,0025-3201C2002€3,025.00€ 3.025,0025-3102C2004€1,210.00€ 1.210,00
325-3102C2004€1,210.00€ 1.210,0025-3202C2001€605.00€ 605,0025-3103C2001€9,680.00€ 9.680,00
425-3103C2001€9,680.00€ 9.680,0025-3203C2004€7,260.00€ 7.260,0025-3201C2002€3,025.00€ 3.025,00
525-3204C2003€1,815.00€ 1.815,0025-3202C2001€605.00€ 605,00
625-3203C2004€7,260.00€ 7.260,00
725-3204C2003€1,815.00€ 1.815,00
8
Formula cell (H1)Spilled result

Formula in H1

=VSTACK(DROP(A1:C4, 1), DROP(D1:F5, 1))
=VSTACK(DROP(A1:C4; 1); DROP(D1:F5; 1))

Result: 25-3101 | C2001 | €4,235.00 / 25-3102 | C2004 | €1,210.00 / 25-3103 | C2001 | €9,680.00 / 25-3201 | C2002 | €3,025.00 / 25-3202 | C2001 | €605.00 / 25-3203 | C2004 | €7,260.00 / 25-3204 | C2003 | €1,815.0025-3101 | C2001 | € 4.235,00 / 25-3102 | C2004 | € 1.210,00 / 25-3103 | C2001 | € 9.680,00 / 25-3201 | C2002 | € 3.025,00 / 25-3202 | C2001 | € 605,00 / 25-3203 | C2004 | € 7.260,00 / 25-3204 | C2003 | € 1.815,00

Step by step
  1. Excel evaluates the inner functions first. DROP(A1:C4, 1): A1:C4 is 4 rows by 3 columns, a header and three October invoices. One row is cut from the top, which leaves 25-3101, 25-3102 and 25-3103 (3 rows).
  2. DROP(D1:F5, 1): D1:F5 is 5 rows by 3 columns, a header and four November invoices. One row is cut from the top, which leaves 25-3201, 25-3202, 25-3203 and 25-3204 (4 rows).
  3. VSTACK now receives two blocks of 3 columns each and puts the second under the first: 3 + 4 = 7 rows by 3 columns.
  4. The result spills from H1 to J7. The seven invoices run from 25-3101 (C2001, 4,2354.235) to 25-3204 (C2003, 1,8151.815), with no header row in between.
  5. Tie-out: October is 4,2354.235 + 1,2101.210 + 9,6809.680 = 15,12515.125 and November is 3,0253.025 + 605 + 7,2607.260 + 1,8151.815 = 12,70512.705, so the amount column of the combined list adds up to 15,12515.125 + 12,70512.705 = 27,83027.830. That is the figure to agree with the payables listing.

Watch out: If one of the exports arrives without a header row, DROP(..., 1) silently removes a real invoice and the list is one short, with no error. Count the rows of the result against the number of invoices in each export.

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

Why it works

VSTACK takes any blocks that are equally wide and does not know which row is a header, so the cleaning has to happen before the stacking. Because DROP returns an array and not a range, it can sit directly inside VSTACK, which is the pattern for adding one more month: put another DROP(...) in the list.

More to watch out for

  • Blocks of different widths are padded with #N/A in the missing columns. Make sure every export has the same columns in the same order.

Variations

=VSTACK(A2:C4, D2:F5)
=VSTACK(A2:C4; D2:F5)

Without DROP, when you can point straight at the lines. Shorter, but it breaks as soon as an export gets a different number of lines or a title row.

=SORT(VSTACK(DROP(A1:C4, 1), DROP(D1:F5, 1)), 3, -1)
=SORT(VSTACK(DROP(A1:C4; 1); DROP(D1:F5; 1)); 3; -1)

Largest invoice first, for a list you want to test from the top.

Master

Receivables specification: clean lines only if they agree with the ledger

The client's receivables specification of Veldhuis Installatie BV is in A1:C7: a header, five open invoices and a total row. You want the five invoices as a clean list for the confirmation selection, but only when their sum agrees with the balance of account 1300 in the ledger (F1).

If not, the sheet should say so instead of showing a list nobody should rely on. A reviewer must be able to read each step.

ABCDEFGH
1InvoiceRelationAmountLedger balance 13003327533275
225-4101D1001€12,100.00€ 12.100,00
325-4102D1003€6,050.00€ 6.050,0025-4101=LET( raw, A1:C7, lines, DROP(DROP(raw, 1), -1), amounts, DROP(lines, 0, 2), IF(SUM(amounts) = F1, lines, "Lines do not agree with the ledger") )=LET( raw; A1:C7; lines; DROP(DROP(raw; 1); -1); amounts; DROP(lines; 0; 2); IF(SUM(amounts) = F1; lines; "Lines do not agree with the ledger") )D1001€12,100.00€ 12.100,00
425-4103D1001€3,630.00€ 3.630,0025-4102D1003€6,050.00€ 6.050,00
525-4104D1002€9,075.00€ 9.075,0025-4103D1001€3,630.00€ 3.630,00
625-4105D1004€2,420.00€ 2.420,0025-4104D1002€9,075.00€ 9.075,00
7Total€33,275.00€ 33.275,0025-4105D1004€2,420.00€ 2.420,00
8
Formula cell (E3)InputSpilled result

Formula in E3

=LET(
  raw, A1:C7,
  lines, DROP(DROP(raw, 1), -1),
  amounts, DROP(lines, 0, 2),
  IF(SUM(amounts) = F1, lines, "Lines do not agree with the ledger")
)
=LET(
  raw; A1:C7;
  lines; DROP(DROP(raw; 1); -1);
  amounts; DROP(lines; 0; 2);
  IF(SUM(amounts) = F1; lines; "Lines do not agree with the ledger")
)

Result: 25-4101 | D1001 | €12,100.00 / 25-4102 | D1003 | €6,050.00 / 25-4103 | D1001 | €3,630.00 / 25-4104 | D1002 | €9,075.00 / 25-4105 | D1004 | €2,420.0025-4101 | D1001 | € 12.100,00 / 25-4102 | D1003 | € 6.050,00 / 25-4103 | D1001 | € 3.630,00 / 25-4104 | D1002 | € 9.075,00 / 25-4105 | D1004 | € 2.420,00

Step by step
  1. LET stores raw as A1:C7: 7 rows by 3 columns, with the header in row 1, five invoices in rows 2 to 6 and the total row in row 7.
  2. lines is calculated inside out. DROP(raw, 1) removes the header and leaves 6 rows. DROP of that with -1 removes the last row, the total row, and leaves 5 rows: the invoices 25-4101 to 25-4105.
  3. amounts is DROP(lines, 0, 2). rows is 0, so no row is cut. columns is 2, so the first two columns (invoice and relation) go and the amount column stays: 12,10012.100, 6,0506.050, 3,6303.630, 9,0759.075 and 2,4202.420.
  4. SUM(amounts) = 12,10012.100 + 6,0506.050 + 3,6303.630 + 9,0759.075 + 2,4202.420 = 33,27533.275. F1 holds 33,27533.275, so the comparison is TRUE.
  5. IF takes the TRUE branch and returns lines. The five invoices spill from E3 to G7.
  6. Change F1 to 33000. SUM(amounts) is still 33,27533.275, which is 275 more than 33,00033.000, so the comparison is FALSE and E3 shows Lines do not agree with the ledger. The difference of 275 is what you take to the client.

Watch out: The mirror-image mistake is dropping only the end, DROP(raw, -1). The header stays in lines, SUM ignores its text, the check still passes, and the list starts with a row of column names. Always read the first row of the result.

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

Why it works

DROP cannot cut both ends in one call, so two nested calls strip the header and the total row, and DROP(lines, 0, 2) isolates a column by position, which is the same as INDEX(lines, 0, 3). LET gives each stage a name, so the reviewer reads raw, lines, amounts instead of a nested block.

Do not use this when the layout of the export is not fixed (a title line one month, no total row the next): DROP cuts by position and cannot tell a total from an invoice. Then FILTER on a real condition is the better choice, at the cost of relying on a column that is filled for every invoice.

More to watch out for

  • Cutting too much is caught by the check: DROP(DROP(raw, 1), -2) also removes invoice 25-4105, the sum becomes 30,85530.855 and the sheet shows the message. The check compares only the total, so two errors that cancel out would go unnoticed.

Variations

=FILTER(A2:C7, B2:B7<>"")
=FILTER(A2:C7; B2:B7<>"")

Without LET and without counting rows: keep only rows that have a relation, which also drops the total row. Prefer it when the export length varies; it has no ledger check.

Common mistakes

SymptomCauseFix
The total stays, the header is goneYou wanted the last row (a total) gone, but DROP(A1:C7, 1) removed the header and left the total in your list.A positive number trims from the start, a negative one from the end. Use DROP(A1:C7, -1) for the last row. Check the first and last row of the result before you build on it.
The first row is gone, not the columnYou wanted to drop the first column, wrote DROP(A1:C7, 1), and lost the first row instead.Columns come third: DROP(A1:C7, 0, 1) or DROP(A1:C7, , 1).
#CALC! although the formula looks rightThe result is #CALC! although the formula looks right.You removed as many rows or columns as the array has, or more. This happens when the source range is shorter than you thought, for example one row. Compare the number you drop with ROWS and COLUMNS of the source.
#SPILL! although the formula is correctThe result is #SPILL! even though the formula is correct.Something already sits in the cells the block needs. Click the error, choose Select Obstructing Cells, and clear them.