A dynamic array formula in Excel returns multiple results that spill automatically into neighboring cells, so one formula can fill a whole range. In Microsoft 365 for Windows, enter the formula in the top-left cell of the intended output area and press Enter; Excel expands the result when space is available.
What a dynamic array formula is
Traditional Excel formulas usually return one result to the cell where you enter them. To calculate the same kind of result for several rows, you might copy a formula down, fill a range, or use an older array formula. A dynamic array formula can instead return a collection of results from a single formula. Excel calls the cells occupied by those results the spill range.
Some functions are designed to return arrays. FILTER can return matching rows, SORT can return a reordered range, UNIQUE can list distinct values, and SEQUENCE can generate a series of numbers. Other formulas can become dynamic when their calculation produces more than one value. For example, multiplying two same-sized ranges element by element produces a result for each corresponding pair.
Dynamic arrays are useful because the output can adjust when the source data changes. If a FILTER formula finds more matching rows after new data is added, its spill range can grow. If fewer rows match, the output can shrink. This means you generally maintain the formula in one cell rather than copying it down a column.
How spilling works
The cell containing the formula is the anchor cell. Excel displays the first result there and places the remaining results in adjacent cells as needed. The output range is not a set of independent formulas: it is controlled by the formula in the anchor cell. Selecting a cell within the spill range usually shows the formula in the formula bar, but that cell cannot be edited separately.
For a multiplication example, suppose A2:A4 contains prices 12, 8, and 5, while B2:B4 contains quantities 3, 4, and 2. Enter this formula in C2:
With prices in A2:A4 and quantities in B2:B4, enter =A2:A4*B2:B4 in C2. It spills 36, 32, and 10 into C2:C4, because 12 × 3 = 36, 8 × 4 = 32, and 5 × 2 = 10.
Excel indicates a spill range in a formula reference with the hash symbol. If the anchor formula is in C2, a reference such as C2# means the entire current spill range, not just the value in C2. This can be helpful in later calculations or charts: the reference can expand or contract as the original formula returns a different number of results.
Common dynamic array formulas
FILTER is useful when you want a live list of records that meet a condition. Suppose a small table has names in A2:A5, departments in B2:B5, and status in C2:C5. The rows are Ana / Sales / Open, Bo / Operations / Closed, Cy / Sales / Open, and Di / HR / Pending. Enter this in E2:
With the four records in A2:C5 and status values in C2:C5, enter =FILTER(A2:C5,C2:C5="Open","No matches") in E2. The result is two rows: Ana / Sales / Open and Cy / Sales / Open. The third argument supplies text to display if no rows meet the condition.
UNIQUE and SORT can be combined to make a sorted list without duplicates. If A2:A8 contains Pear, Apple, Pear, Lime, Apple, Orange, and Lime, enter the following in C2:
With those seven fruit names in A2:A8, enter =SORT(UNIQUE(A2:A8)) in C2. The spill range displays Apple, Lime, Orange, and Pear, in ascending order. If the source list changes, Excel recalculates the distinct sorted results.
SEQUENCE creates a series using a specified number of rows, columns, starting value, and step. For example:
With an empty output area beginning at A1, enter =SEQUENCE(4,1,10,5) in A1. The result is 10, 15, 20, and 25 down A1:A4: four rows, one column, starting at 10 and increasing by 5.
Where dynamic arrays are useful
Dynamic arrays can reduce repetitive maintenance in reports and working spreadsheets. They are particularly useful when the number of results is not fixed in advance or when the source data changes regularly. Common examples include:
- Extracting matching orders, names, or products with FILTER rather than manually applying and copying a formula.
- Creating an up-to-date list of categories or customers with UNIQUE.
- Sorting a result list with SORT without changing the order of the source data.
- Building a sequence for a schedule, test dataset, or set of period numbers with SEQUENCE.
- Returning several columns from a lookup or calculation in one formula, where the chosen function supports array results.
These formulas do not replace every spreadsheet tool. A PivotTable may be a better fit for interactive grouping and summarizing, while a regular formula may be clearer for a single calculation. Dynamic arrays are most valuable when a live, formula-driven output list is the goal.
Spill ranges and Excel tables
Dynamic array formulas work well alongside Excel tables, but there is an important distinction: the formula that produces a spill range cannot be placed inside a table. Put the formula in a normal worksheet cell outside the table, and it can refer to the table’s data. This keeps the spill output free to expand into neighboring cells. Make sure the cells it may need are not occupied by other values, formulas, or merged cells.
Tables can still be a useful source for dynamic formulas because their structured references adjust as table rows are added or removed. If you expect output to grow, leave enough clear space below or beside the formula. A spill range may extend farther than it does today, and any content in its way can stop the formula from returning its full result.
To refer to a whole current spill range in another formula, use the anchor cell followed by #, such as C2#. Use the anchor cell, not a cell somewhere in the middle of the spill. The reference then tracks the size of the output as it changes.
Common problems and how to fix them
The most recognizable spill problem is the #SPILL! error. It means Excel cannot place the complete result in the required cells. Check the destination area for existing content, merged cells, or a formula entered inside a table. Clear or move the obstruction, and the result may spill normally. Excel may outline the intended spill area to help identify the blocked cells.
A formula may return fewer results than expected without showing an error. For FILTER, check that the include range has the same number of rows as the data range and that the criteria match the actual cell contents. For example, a trailing space in a status cell can make it different from the text you are testing. Also check whether the formula’s condition is designed to include all the records you expect.
Another common misunderstanding is trying to type into one of the output cells. Edit the anchor formula instead; the other cells are controlled by it. If you need a fixed snapshot rather than a live result, copy the spill range and use Paste Special > Values to replace formulas with their current results. Remember that the pasted values will no longer update when the source changes.
Some older workbooks use array formulas entered with Ctrl+Shift+Enter. Those are called legacy array formulas and behave differently: they may occupy a range selected in advance and generally cannot be resized in the same way. Do not press Ctrl+Shift+Enter for an ordinary dynamic array formula in current Microsoft 365 Excel; Enter is the usual way to confirm it.
Compatibility and version differences
Dynamic array behavior is available in Microsoft 365 Excel and Excel 2021 or later, though individual functions have their own availability requirements. FILTER, SORT, UNIQUE, and SEQUENCE are available in Microsoft 365 and Excel 2021 or later. Excel 2019 and earlier do not provide the same modern dynamic array functionality, so a workbook using these formulas may show a compatibility problem or an unsupported-function error when opened there.
In current Excel for Mac and Excel for the web, dynamic arrays and spill ranges are also supported, though the interface and some editing details can differ from Windows. The examples here use Microsoft 365 for Windows. If you share a workbook, consider the recipient’s Excel version: a formula that depends on a newer function may not calculate in an older installation. Also, dynamic array links to another workbook can have limitations when the source workbook is closed; keeping related workbooks open or storing the needed source data in the same workbook can avoid that issue.
Dynamic arrays versus copying formulas
Copying a formula down remains appropriate when each row needs its own editable formula, or when the calculation is intended to be independent in each row. A dynamic array is different: the anchor formula controls the whole result, which makes it easier to manage a list whose height varies. With a dynamic output, there is no need to guess how many rows to fill in advance.
There are trade-offs. A spill range needs unobstructed cells, and users who expect to edit each result individually may find the single-anchor design surprising. Dynamic arrays can also make a workbook harder to use in older Excel versions. Choose based on the workbook’s audience and the purpose of the results, rather than using a dynamic formula simply because it is available.
Frequently Asked Questions
What does the # symbol mean in an Excel formula?
The # spill reference operator means the full spill range created by the formula in the referenced anchor cell. For example, C2# refers to all current results spilling from C2.
Why does my Excel formula show #SPILL!?
Excel cannot fit the formula’s full output into the necessary cells. Clear the obstructed cells, unmerge cells in the output area, or move the formula outside a table so its results can spill.
Can I edit one result in a dynamic array?
No. The results belong to the formula in the anchor cell. Change that formula or its source data; to edit individual values, copy the output and paste it as values first.
Do I need Ctrl+Shift+Enter for dynamic array formulas?
No. In current Microsoft 365 Excel, enter a dynamic array formula normally with Enter. Ctrl+Shift+Enter is associated with legacy array formulas.
Can a dynamic array formula go inside an Excel table?
A formula that needs to spill cannot be placed inside a table. Enter it in a regular worksheet cell outside the table, and refer to the table as the source if needed.
Related
- Daily Cleaning Checklist Template Excel: Setup Guide
- Daily Cash Flow Template Excel: How to Build and Use It
- Daily Cash Flow Report Template in Excel: Setup and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Oracle PL/SQL String Functions: A Practical GuideOracle PL/SQL string functions are built-in routines for measuring, searching, extracting, combining, and changing text. Common choices include…
- Excel DATEDIF Age Formula: Calculate Years, Months, DaysTo calculate someone’s age in completed years in Excel, use =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2.…
- Excel XLOOKUP Function Examples for Everyday LookupsIn Excel for Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from…
- What Column Number Is H in Excel? Column Index ExplainedIn Excel, column H is column number 8. The letters A through H count upward from 1, so…
- DATEDIF in Excel: Calculate Age in Years, Months, and DaysTo calculate someone’s age in completed years in Excel, enter their date of birth in A2 and use…
- Excel Date Plus 1 Day: Formula, Examples, and FixesTo add one day to an Excel date, add 1 to the date cell. If the date is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.