Working with dynamic arrays, sorted tables, and imported report ranges in Microsoft Excel often requires extracting specific subsets of rows or columns—such as retrieving the top 10 sales performers, skipping table headers, or dropping trailing summary rows. Traditionally, users constructed complex formulas combining INDEX, OFFSET, SEQUENCE, and COUNTA. Microsoft Excel simplifies array manipulation with two modern dynamic array functions: TAKE and DROP. These functions allow users to slice, trim, and extract precise sections of a range using intuitive row and column counts.
Understanding TAKE vs DROP
- TAKE: Returns a contiguous subset of rows or columns from the start or end of an array. You specify how many rows or columns to keep.
- DROP: Removes a contiguous subset of rows or columns from the start or end of an array. You specify how many rows or columns to exclude.
Function Syntaxes and Directional Arguments
TAKE Syntax
=TAKE(array, rows, [columns])
array:The continuous cell range, table, or dynamic formula spill (e.g.,A2:D50).rows:The number of rows to return. Positive numbers take from the top; negative numbers take from the bottom.columns:Optional. The number of columns to return. Positive numbers take from the left; negative numbers take from the right.
DROP Syntax
=DROP(array, rows, [columns])
array:The continuous cell range, table, or dynamic formula spill.rows:The number of rows to drop. Positive numbers drop from the top; negative numbers drop from the bottom.columns:Optional. The number of columns to drop. Positive numbers drop from the left; negative numbers drop from the right.
Practical Example 1: Extracting Top 5 Performers with TAKE and SORT
Suppose you have an employee performance table in range A2:C50 where column A is Name, column B is Department, and column C is Sales.
To extract the top 5 highest earners across the organization:
=TAKE(SORT(A2:C50, 3, -1), 5)
How it works:
SORT(A2:C50, 3, -1)sorts the entire table in descending order based on column 3 (Sales).TAKE(..., 5)extracts the first 5 rows of that sorted result and spills the data into a clean 5-row summary table.
Practical Example 2: Extracting the Last 3 Transactions Using Negative Numbers
When working with chronological logs where new entries are appended to the bottom of the table, passing negative values to TAKE extracts the latest rows:
=TAKE(A2:D100, -3)
The -3 argument instructs Excel to extract the bottom three rows from range A2:D100.
Practical Example 3: Stripping Table Headers and Totals with DROP
When nesting a formatted Excel table into an array calculation that requires pure raw data without table headers or trailing total rows:
=DROP(DROP(Table1, 1), -1)
Alternatively, if you only want to remove the header row and the leftmost index column:
=DROP(A1:E50, 1, 1)
This single formula drops row 1 (the header) and column 1 (e.g., Row IDs), returning a clean matrix spanning B2:E50.
Combining TAKE and DROP to Extract Slices from the Middle
By nesting TAKE inside DROP, you can extract any arbitrary slice from a dataset. For example, to retrieve rows 11 through 20 of a sorted report:
=TAKE(DROP(A2:C100, 10), 10)
DROP(A2:C100, 10)discards the first 10 rows.TAKE(..., 10)takes the first 10 rows of the remaining array, cleanly yielding rows 11 to 20.
Best Practice Tips for Excel Modeling
- Combine with Dynamic Spill References: Use the pound operator (
#) to feed spilled results dynamically (e.g.,=TAKE(A2#, 10)). If the source formula expands, the sliced view adjusts automatically. - Pair with CHOOSEROWS and CHOOSECOLS: While
TAKEandDROPare designed for contiguous slices, useCHOOSEROWSorCHOOSECOLSwhen you need non-contiguous subsets (e.g., columns 1, 3, and 5). - Avoid Hardcoded Ranges: Reference official Excel Tables (e.g.,
SalesTable[#Data]) to ensure newly added data rows are instantly included in calculations.