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
1
Take first 10 rows
Return the first 10 rows of a table.
=TAKE(Sales,10)Useful for previews.
2
Take last 5 rows
Return recent rows.
=TAKE(Sales,-5)Negative rows count from the end.
3
Drop header row
Remove the first row from an imported array.
=DROP(A1:D100,1)Useful for cleaning imported ranges.
4
Drop first column
Remove an ID column.
=DROP(A1:D100,,1)The column argument removes columns.
5
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.