Excel like a finance pro.
TAKE & DROP
Dynamic arrays (Microsoft 365)Top ten customers, or everything except the header row, without hardcoding a range.
Difficulty
1What is it?
TAKE keeps the first or last N rows or columns of an array; DROP discards them. Negative counts work from the end, which is how you get "the last 12 months" from a range that grows. Together they replace the hardcoded ranges that quietly go wrong when the data changes length — the ones that still say A2:A50 six months after the export started producing 80 rows. Combined with SORT, TAKE is a top-N list that never needs re-pointing.
2What it looks like
TAKE(array, rows, [columns]) · DROP(array, rows, [columns])- array
- The range or spilled result to slice.
- rows
- How many rows. POSITIVE takes from the top, NEGATIVE from the bottom.
- [columns]
- Same idea across. Omit it to keep every column.
3When you use it
- Top 10 customers: TAKE(SORT(data, 2, -1), 10).
- The last 12 rows of a monthly table, however long it has grown.
- Drop a header or a totals row before feeding a range to something else.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Change N and the list resizes. Nothing here is a hardcoded range.
=TAKE(SORT(A2:B6, 2, -1), 3)Globex $98,000| A | B | C | |
|---|---|---|---|
| 1 | Customer | Revenue | Result |
| 2 | Globex | 98,000 | Globex $98,000 |
| 3 | Acme | 42,000 | Umbrella $63,000 |
| 4 | Initech | 27,500 | Acme $42,000 |
| 5 | Umbrella | 63,000 | Positive takes from the top. |
| 6 | Soylent | 15,400 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#VALUE!rows is 0 — there's no such thing as taking nothing.Fix: Use a positive or negative count. If the count comes from a cell, guard it with MAX(1, …).
Fewer rows than asked forThe array is shorter than the count. That's not an error — it returns what exists.Fix: Correct behaviour, and usually what you want. Wrap in ROWS() if you need to know how many came back.
#SPILL!Something blocks the output range.Fix: Clear the cells below and to the right.
Took from the wrong endSign confusion — positive is from the TOP.Fix: Negative for the most recent rows at the bottom of a growing table.
6Better functions & alternatives
- INDEX with a computed range — The pre-dynamic-array way, and far harder to read six months later.
- FILTER — When you want rows meeting a CONDITION rather than a position.
Want TAKE & DROP already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.