- Get link
- X
- Other Apps
This article explains how to use the Excel TAKE and DROP dynamic array functions to build flexible, dynamic views of your data that automatically resize for dashboards, rolling reports, and interactive analysis without manual range adjustments.
1. Why TAKE and DROP change how you build reports in Excel
Dynamic arrays in Excel allow a single formula to return multiple values that spill into adjacent cells. The TAKE and DROP functions extend this concept by letting you extract or remove specific portions of a range while keeping everything fully dynamic. Instead of copying formulas down or redefining named ranges, you can write a single formula that always returns “the latest 7 days,” “the first 10 rows,” or “all rows except the header.”
For dashboard designers, analysts, and power users, TAKE and DROP make it possible to build reusable templates where source tables can grow or shrink and views update automatically. Combined with functions such as FILTER, SORT, UNIQUE, XLOOKUP, and other dynamic array functions, they form a foundation for truly dynamic reports.
2. Quick overview of dynamic arrays and spilled ranges
Dynamic array formulas are available in Microsoft 365 Excel and recent Excel versions that support the new calculation engine. When a formula returns multiple results, Excel automatically spills them into a range of cells. That spilled range resizes whenever the underlying data size changes.
Key points for dynamic arrays:
- A single formula in the top-left cell can populate an entire table of results.
- When the source data grows or shrinks, the spilled range expands or contracts automatically.
- You must leave enough empty cells around the formula; otherwise Excel returns a spill error.
- TAKE and DROP both require a source array, which can be a range, a structured table reference, or the result of another dynamic formula (for example FILTER, SORT, UNIQUE, or XLOOKUP).
Note : TAKE and DROP only work in Excel versions that support dynamic arrays. In older desktop versions or many legacy corporate environments, these functions are not available.
3. Understanding the Excel TAKE function
The TAKE function returns a subset of an array by extracting a specific number of rows and/or columns from the beginning or the end of that array.
3.1 TAKE syntax and arguments
=TAKE(array, rows, [columns]) | Argument | Description | Typical examples |
|---|---|---|
array | Source range or dynamic array from which to take rows/columns. | SalesTable, A2:D500, FILTER(...) |
rows | Number of rows to return. Positive for top rows, negative for bottom rows. | 5 (top 5 rows), -10 (last 10 rows) |
[columns] | Optional. Number of columns to return. Positive from the left, negative from the right. | 3 (first 3 columns), -2 (last 2 columns) |
3.2 Basic TAKE examples
Assume you have a data table in A2:E101 where row 2 is the header and rows 3:101 contain transaction records.
- First 10 data rows (including headers):
=TAKE(A2:E101, 11) Because the header is one row, the first 11 rows include both the header and the first 10 data rows.
- Last 7 rows of data (excluding header):
=TAKE(A3:E101, -7) Here, the -7 indicates that TAKE should work from the bottom of the data range A3:E101 and return the last seven rows.
- Only the first two columns of a table named
Sales:
=TAKE(Sales, , 2) When you omit the rows argument and supply only columns, TAKE returns all rows for the specified columns.
3.3 Real-world use: last N days, last N transactions
A common pattern is to compute metrics based on the most recent entries in a log or fact table. Suppose column Date is sorted ascending in a table named Sales. To calculate the average of the last 30 values in the [Amount] column:
=AVERAGE(TAKE(Sales[Amount], -30)) If more rows are added to Sales, the range for the last 30 values updates automatically and the average is recalculated without any range editing.
4. Understanding the Excel DROP function
The DROP function returns a subset of an array by excluding a specified number of rows and/or columns from the beginning or end of the array.
4.1 DROP syntax and arguments
=DROP(array, rows, [columns]) | Argument | Description | Typical examples |
|---|---|---|
array | Source range or dynamic array from which to drop rows/columns. | DataTable, A1:F200, SORT(...) |
rows | Number of rows to drop. Positive drops from the top, negative drops from the bottom. | 1 (drop header row), -5 (drop last five rows) |
[columns] | Optional. Number of columns to drop. Positive from the left, negative from the right. | 1 (drop first column), -1 (drop last column) |
4.2 Basic DROP examples
Using the same data in A2:E101 with headers in row 2:
- Remove header row, keeping only data:
=DROP(A2:E101, 1) - Remove the last two columns from a table named
LogTable:
=DROP(LogTable, , -2) - Drop the first two rows and first column:
=DROP(A2:E101, 2, 1) This is especially useful to remove header sections, footers, totals rows, or technical columns that you do not want to expose in a final report.
Note : If you use zero for both rows and columns, DROP returns an error because there is nothing left to return. At least one of the arguments must remove something from the array.
5. Comparing TAKE and DROP: when to use which
TAKE and DROP are complementary. TAKE focuses on what you want to keep, while DROP focuses on what you want to remove. In many designs, either approach can work, but there are practical differences that encourage you to choose one over the other.
| Scenario | Prefer TAKE | Prefer DROP |
|---|---|---|
| Return the last N rows | =TAKE(Data, -N) | Possible with DROP(Data, ROWS(Data)-N) but more complex |
| Remove header rows | Possible but indirect | =DROP(Data, HeaderRows) |
| Return a fixed window from the top | =TAKE(Data, N) | Less readable |
| Remove trailing totals or notes | Possible but indirect | =DROP(Data, -FooterRows) |
| Remove technical columns | Can work with TAKE on desired columns | =DROP(Data, , ColumnsToDrop) or use CHOOSECOLS |
6. Building dynamic views using TAKE and DROP
The real power of TAKE and DROP emerges when you combine them with other dynamic functions. This section walks through common patterns for “dynamic views” that automatically resize and respond to changes in your data or parameters.
6.1 Dynamic “last N rows” view for dashboards
Suppose you maintain a transaction table named Sales sorted by date. You want a dashboard view that always shows the last N rows, where N is controlled by the user via an input cell G1.
=TAKE(Sales, -G1) When the user changes G1 from 10 to 30, the displayed view automatically expands from the last 10 rows to the last 30 rows. This is ideal for KPI tiles, small multiples, and chart sources that track only the most recent entries.
6.2 Removing headers and footers for clean chart ranges
Charts often require pure numeric data ranges without headers, blank rows, or subtotal lines. DROP is particularly helpful here. If you have a header row and a final grand total row in A2:D102:
=DROP(DROP(A2:D102, 1), -1) The first DROP removes the header row; the second removes the final total row. You can feed this result into a chart’s data range or into further formulas such as AVERAGE, SUM, or MEDIAN.
6.3 Combining FILTER with TAKE for dynamic filtered views
Filtering and then taking the last or first N rows is a common requirement in reporting. Assume Sales has columns [Region], [Date], and [Amount], and you want the last 5 transactions for a region selected in H1.
=LET( rgn, H1, filtered, FILTER(Sales, Sales[Region]=rgn), TAKE(filtered, -5) ) The LET wrapper defines intermediate variables for clarity and performance. When the user changes region in H1, the view instantly shows the last five matching rows for that region.
6.4 Combining SORT with TAKE for top-N and bottom-N rankings
Another classic pattern is the top-N report. Suppose Sales has a numeric column [Revenue] and you want the top 10 customers by revenue.
=LET( sorted, SORT(Sales, Sales[Revenue], -1), TAKE(sorted, 10) ) To create a bottom-10 view instead, change the sort order or combine TAKE with negative row values:
=LET( sorted, SORT(Sales, Sales[Revenue], 1), TAKE(sorted, 10) ) 6.5 Using DROP to hide technical columns for end users
Raw data often includes helper columns used for calculations, indexing, or temporary flags that you do not want to display in a final output. Rather than duplicating the table without those columns, apply DROP to the array feeding the report.
For example, if RawTable has six columns and the last two are technical helpers, you can create a presentation-ready view:
=DROP(RawTable, , -2) This drops the last two columns while keeping the table fully dynamic as new rows are added.
7. Advanced patterns: TAKE and DROP with other dynamic array functions
Dynamic views become particularly flexible when you combine TAKE and DROP with other newer functions such as BYROW, BYCOL, MAKEARRAY, MAP, REDUCE, and SCAN. Here are a few patterns that scale well for more complex models.
7.1 Rolling window metrics with TAKE
For a rolling 90-day sum using a daily data series in Daily[Amount], sorted by date, you can build a dynamic window:
=SUM(TAKE(Daily[Amount], -90)) When new days are appended, TAKE always selects the last 90 records, and the sum tracks the latest window without any manual updates.
7.2 Creating dynamic “page views” from a large dataset
You can use DROP and TAKE together to implement paging logic, where the user navigates through a large dataset page by page. Suppose Data is your full dataset, RowsPerPage is in K1, and PageIndex (starting at 0) is in K2.
=LET( rpp, K1, page, K2, startOffset, rpp*page, dropped, DROP(Data, startOffset), TAKE(dropped, rpp) ) When the user increments PageIndex, the view shows the next block of rows, creating a dynamic paged view without VBA or Power Query.
7.3 SELECT-like behavior with TAKE, DROP, and CHOOSECOLS
While TAKE and DROP control contiguous blocks of rows and columns, CHOOSECOLS and CHOOSEROWS let you pick individual rows or columns by index. Combining them allows you to replicate SQL-like views in Excel.
Example: select the last 20 rows, but only chosen columns 1, 3, and 5 from a table Data:
=LET( last20, TAKE(Data, -20), CHOOSECOLS(last20, 1, 3, 5) ) 8. Best practices and common pitfalls when using TAKE and DROP
Although TAKE and DROP are powerful, a few design rules help keep your workbooks robust, readable, and performant.
8.1 Use structured tables for stable dynamic arrays
Whenever possible, convert your source ranges into Excel Tables (Ctrl+T). Structured references such as Sales[Amount] expand automatically as new rows are added and work cleanly with TAKE and DROP. This reduces the need for manual range updates and prevents subtle errors when rows are appended beyond a hard-coded range.
8.2 Guard against small datasets
If you request more rows than exist in the array, TAKE simply returns all available rows. While this is usually helpful, it can sometimes hide data quality issues, such as missing records.
Note : For operational reports, consider wrapping your TAKE or DROP logic in IF checks that warn when the dataset is smaller than expected, or display a message when the requested window size exceeds the available data.
8.3 Avoid unnecessary nesting where a single function suffices
It is possible but inefficient to replicate TAKE with a combination of DROP and other dynamic functions, or to simulate DROP by calculating array sizes and subtracting N. In most cases, using the native TAKE or DROP function directly is more readable and less error-prone.
8.4 Document parameter cells clearly
Many “dynamic view” patterns rely on driver cells (for example, “Rows to show,” “Page index,” or “Number of latest days”). Use clear labels, consistent formatting, and data validation to prevent users from entering invalid values, such as negative page indices or extremely large row counts that slow down calculation.
8.5 Think in arrays, not single cells
When designing dynamic views, mentally step away from traditional cell-by-cell formula thinking. Instead, focus on the entire array you want to return, then decide how TAKE, DROP, and related functions should transform that array. This mindset leads to simpler, more elegant formulas.
9. Integrating TAKE and DROP into a reusable reporting template
One of the most effective ways to use TAKE and DROP is to embed them in a standardized template that you reuse across projects. A typical structure looks like this:
- Raw data sheet: Tables loaded from Power Query, CSV imports, or connectors.
- Model sheet: Dynamic formulas that clean and reshape the data using FILTER, SORT, UNIQUE, TAKE, DROP, and other array functions.
- Dashboard sheet: Charts and KPIs that reference only the model sheet’s clean dynamic views.
Example of a model-layer formula that powers a dashboard view showing the latest 30 records, filtered by region and sorted by date descending:
=LET( rgn, Dashboard!B2, filtered, FILTER(Sales, Sales[Region]=rgn), sorted, SORT(filtered, FILTERED[Date], -1), TAKE(sorted, 30) ) This formula encapsulates the entire logic for selecting, filtering, sorting, and limiting the dataset. The dashboard sheet then references this spilled range for tables and charts, while the raw data sheet can be refreshed or replaced without any changes to chart series or named ranges.
FAQ
Which Excel versions support the TAKE and DROP functions?
The TAKE and DROP functions are part of the dynamic array function family and are available in Microsoft 365 Excel and other recent versions that include the new dynamic array engine. They are not available in older perpetual versions such as many builds of Excel 2016 and earlier.
How can I check if my Excel supports dynamic arrays?
A quick test is to enter a formula such as =SEQUENCE(3) into a single cell. If the result spills into three cells vertically, your Excel supports dynamic arrays. If you see an error, the dynamic array features, including TAKE and DROP, are likely not available in your version.
Can I use TAKE and DROP in defined names or named formulas?
Yes. You can create named ranges that use TAKE and DROP to define dynamic views without referencing fixed ranges. This is useful for setting chart sources, data validation lists, or reusable calculation blocks that automatically resize as the underlying data changes.
What happens if I request more rows than exist with TAKE?
If you ask TAKE to return more rows or columns than the array contains, Excel simply returns all available rows or columns. No error is raised. This behavior makes formulas more resilient when the data is smaller than expected, but you should still validate minimum record counts for critical reports.
How do TAKE and DROP interact with FILTER, SORT, and UNIQUE?
TAKE and DROP accept any array as input, including the result of FILTER, SORT, UNIQUE, XLOOKUP, or other dynamic functions. A common pattern is to first build a filtered or sorted view and then use TAKE to cap that view at the first or last N rows, or DROP to remove unwanted headers, totals, or technical columns.