What a Dynamic Array Actually Is
Before dynamic arrays, a formula returned one value to one cell. Now a formula can return a whole array, and Excel places the extra values in the cells around it automatically. That behavior is the spill. The formula cell holds the calculation; the cells it fills are the spill range, shown with a light blue border when you select the formula.
The Spill Range and the # Spill Operator
The spill range is the set of cells a formula fills. You can reference the entire spill with the hash operator: if your formula is in B2, type B2# to mean all the spilled cells. This is how a chart or a SUM points at a result that changes size. Learn the # and you can build reports that resize themselves.
Click the cell with the dynamic array formula, like B2. Only this cell holds the calculation. The filled cells below are outputs you should not edit directly.
In another cell type =SUM(B2#) to total the spilled block. The # tells Excel to grab every cell the formula spilled into, even as the count changes.
Point a chart's data range at B2#. As the spill grows or shrinks, the chart follows. This avoids the old problem of a fixed range that cuts off new rows.
Select the formula cell and look for the light blue outline around the results. That border is your visual proof of the current spill range and helps you spot what a report covers.
Common Ways to Reference a Spill
Once a formula spills, you rarely want to retype the range it filled. The # operator lets a total, a lookup, or a chart track the spill as it grows or shrinks. I keep every downstream formula pointed at the spill anchor so the report resizes on its own.
Write =SUM(B2#) beneath or beside a spilled block starting at B2. The # grabs all spilled cells, so the total updates the moment a row is added or removed from the source.
Use =XLOOKUP("West", B2#, C2#) to find West in the spilled key column and return the matching value from the spilled result column. Both ranges use # so they stay aligned as the spill changes size.
Set a chart's data range to B2#. As the spill grows with new data, the chart expands to include the extra points, which removes the old fixed-range cutoff problem completely.
The Functions That Spill
Several functions return arrays by design. FILTER returns matching rows, SORT reorders a range, SORTBY sorts by a hidden key, UNIQUE returns distinct values, SEQUENCE builds number and date series, and RANDARRAY makes random values. Each one can feed the next, so a single chain can replace a whole helper-column workflow.
Write =FILTER(Sales, Sales[Region]="West", "none") to spill every West row. The returned block is a live spill you can sort or sum on top of.
Wrap with =SORT(FILTER(Sales, Sales[Region]="West","none"), 5, -1) to sort the West rows by the fifth column, Amount, descending. The inner spill becomes the input.
Write =UNIQUE(Sales[Region]) to spill one row per region. Chain it with SORT when the list feeds a dropdown so the order reads cleanly.
Write =SEQUENCE(30,1,DATE(2026,1,1),1) to spill a 30-day date column. It feeds headers and test tables without a single typed cell.
I recommend building reports as one chained formula rather than ten helper columns, because a chain has a single source of truth and is easier to audit. A common mistake is mixing a spilled result with manual values typed into the same area, so the spill cannot expand and throws #SPILL!. Keep spills in their own clean zone.
Why #SPILL! Happens and How to Fix It
A #SPILL! error means Excel cannot place the results because something occupies the cells it needs. The fix is almost always to clear the blocker. Excel even shows a selector on the error that jumps to the first blocked cell, which saves you hunting row by row.
Select the #SPILL! cell and use the small button that appears to go straight to the cell blocking the spill. This is faster than scanning the area by eye.
Remove any number, text, or formula sitting in the spill path. Even one value stops the whole block. After deletion the result fills in immediately.
Merged cells in the path also cause #SPILL!. Unmerge them or move the formula elsewhere. Merged cells reserve multiple grid slots that the spill cannot share.
If the area must stay occupied, cut the formula to an empty column with room below. The spill then has a clear rectangle and completes without error.
Implicit Intersection and the @ Operator
Implicit intersection is the old rule where Excel picked one value from an array based on your row. Dynamic arrays turned that off, but you can force it with the @ operator. Knowing @ stops confusing results when an old formula meets a new array function.
When you open a legacy workbook, Excel may add @ before a function to keep the old one-value behavior. For example =@SUM(A2:A10) means take the row's single result, not the whole array.
Write =@FILTER(...) when you truly want a single cell result from a spilled function. The @ returns just the first item and prevents a spill into the neighbors.
Delete the @ to let the function spill again. If you inherited a formula that returns only one row and you expected many, a stray @ is usually the cause.
A common mistake is copying a spilled formula into the cells it already fills, which creates a circular block and a #SPILL!. This will break the report until you clear those copies. Treat the blue-bordered area as read-only and edit only the formula cell.
Spill Ranges vs Excel Tables
Both keep reports live, but they solve different problems. An Excel Table grows when you add rows and supports structured names like Sales[Amount]. A spill range is the output of a formula. The strongest setup uses a Table as the source and a dynamic array function as the output, so both ends stay automatic.
Press Ctrl+T on your data to turn it into a Table named Sales. Formulas can then reference Sales[Region] instead of C2:C100, and new rows are included with no edit.
Write =SORT(Sales, 5, -1) to spill the whole table sorted by Amount. Because the source is a Table, the spill resizes when rows are added or removed.
Do not also apply a ribbon Sort to a Table that feeds a spill, or the manual sort fights the formula. Pick one mechanism per range and stick to it.
Other Errors You Will Meet
Beyond #SPILL!, dynamic array formulas can return #CALC!, #NAME?, and #REF!. Each points to a specific cause, and once you map the message to the fix, debugging takes seconds instead of minutes.
FILTER with no matches and no empty message returns #CALC!. Add a third argument like "none" or 0 so the function shows a clean value instead of an error.
If a function name is unknown, the file is open in an old Excel. Dynamic arrays need Excel 365 or 2021. Open it in a current version or rewrite with legacy methods.
If the source range was deleted, the spill shows #REF!. Restore the range or repoint the formula. A Table reference avoids this because the name stays valid as rows change.
Dynamic Arrays vs the Old One-Cell World
The old model forced you to copy a formula down every row and hope the range matched. Dynamic arrays compute once and fill as needed. For a team that edits data weekly, the new model removes an entire class of copy-paste mistakes and stale totals.
When you move from legacy formulas, start with one FILTER on a Table and a SUM over its spill. That single pattern delivers most of the payoff before you touch the advanced chains.
Next Steps and a Quick Checklist
Turn your source into a Table, write one spilled formula, and reference its spill with # in a total and a chart. Fix any #SPILL! by clearing the path. Then chain FILTER, SORT, and UNIQUE to replace helper columns one report at a time.
Practice order that works well: SEQUENCE for sample data, FILTER to select, SORT to order, UNIQUE to dedupe, then wrap the chain in SUM. Each guide covers one function in depth so you can build the habit piece by piece.
