What it does
TAKE returns part of an array. DROP removes rows or columns from an array.
Syntax or pattern
=TAKE(array, rows, [columns]) / =DROP(array, rows, [columns])5 practical examples
Take first 10 rows
Return the first 10 rows of a table.
=TAKE(Sales,10)Useful for previews.
Take last 5 rows
Return recent rows.
=TAKE(Sales,-5)Negative rows count from the end.
Drop header row
Remove the first row from an imported array.
=DROP(A1:D100,1)Useful for cleaning imported ranges.
Drop first column
Remove an ID column.
=DROP(A1:D100,,1)The column argument removes columns.
Top 5 after sorting
Sort by sales and return top five.
=TAKE(SORTBY(Sales,Sales[Amount],-1),5)Great for top-N reports.
Common mistakes to avoid
- Make sure spill ranges have empty space below or beside them.
- Use Excel Tables where possible for expanding source data.
- Older Excel versions may not support all dynamic array functions.
Related Excel examples
FAQ
What is a spill formula?
A spill formula returns multiple cells from one formula entered in a single cell.
Here are some ideas for you
Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.
- Excel formula booksSee ideas
Practice formulas with structured examples you can keep beside your desk.
- Excel shortcut guidesSee ideas
Build speed with keyboard shortcuts for selection, formatting and navigation.
- Numeric keypadsSee ideas
Helpful if you enter many numbers on a laptop or compact keyboard.
- External monitorsSee ideas
Useful for viewing large worksheets, formulas and reference tables side by side.
- Desk notebooksSee ideas
Sketch formula logic, report ideas and table structures before building.
- Laptop standsSee ideas
Make long spreadsheet sessions more comfortable and ergonomic.
Some links in this section may be affiliate links. Choose only what is useful for your own work.