TL;DR
- A dynamic array formula goes in one cell (the top-left one) and spills its results into the cells below and to the right, resizing when the source data changes.
- Functions such as FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP are built around this behavior, and any formula can spill if it returns more than one value.
- A #SPILL! error means something is blocking the output range. Clear the blockage or move the formula.
- Dynamic arrays reached Microsoft 365 in January 2020. Older versions show your formulas as legacy array formulas, so check who opens the file.
- The fastest way to learn them is to type them yourself, not to watch someone else do it.
Table of Contents
What a dynamic array actually is
In classic Excel, one formula returned one value in one cell. If you wanted ten results, you wrote ten formulas or dragged one down ten rows. Dynamic arrays change that rule. According to Microsoft's documentation on spilled array behavior, a formula that returns multiple values sends them to neighboring cells automatically. That behavior is spilling.
Here is the smallest example you can try. In an empty sheet, type this in cell A1:
=SEQUENCE(5)
Press Enter. Cells A1 through A5 fill with 1, 2, 3, 4, 5. You typed one formula. Only A1 holds it. A2 to A5 display results but contain no formula of their own, which you can confirm by clicking A3 and looking at the formula bar, where the formula appears grayed out.
Now change the 5 to 8 in A1. The list grows to eight rows. Nothing was dragged, and nothing needs to be deleted when the number shrinks again.
The spill range and its border
The block of cells a formula fills is the spill range. When you select any cell inside it, Excel draws a highlighted border around the whole block, and that border disappears when you select a cell outside the range, as Microsoft describes. Use the border as a quick visual check of how big the result is and where it ends.
To refer to the whole spill range from another formula, add a hash sign after the anchor cell. =SUM(A1#) totals everything that A1 currently spills, however many rows that is. This is what makes dynamic arrays useful in reports: a chart or total pointed at A1# follows the data as it grows.
Enter it in the top-left cell
The rule for practical work is simple: enter the formula in the cell where you want the top-left corner of the result. Excel populates the rest. Leave room below and to the right. If you put a formula in row 2 of a sheet and plan to have a header above it, nothing is lost. If you put it in a cell with data right beneath, you are heading for an error (more on that below).
Dynamic arrays versus legacy array formulas
Excel has had array formulas for decades. They worked, but they were awkward. Microsoft's comparison of dynamic and legacy CSE formulas lists the differences. The ones that matter day to day:
| Legacy array (CSE) formula | Dynamic array formula |
| How you enter it | Ctrl+Shift+Enter | Enter |
| Output size | Fixed when you create it | Resizes to fit the result |
| Editing | You manage the whole output range | You edit the one source cell |
| Curly braces in the formula bar | Yes | No |
The same page notes that legacy formulas could give inconsistent results under what it calls CSE breakup, while dynamic array formulas do not break up. In practice that means one fewer way to damage a model by accident: you cannot overwrite half of an array and leave the rest behind, because only the anchor cell can be edited.
There is also a difference from copy-down formulas. When you drag a formula down a column, each row holds its own formula with its own relative references. A spilled formula is one formula evaluated once over a whole range. If you rely on locking cells with dollar signs, the logic still applies, so it is worth refreshing how absolute references work and how formulas are copied and locked before you start replacing copy-down columns with spills.
The functions that return dynamic arrays
Any formula can spill if its result has more than one value, even a plain =A1:A10*2. But a group of newer functions were designed for it. Here are the ones you will use most, with examples you can paste into a practice sheet.
| Function | What it does | Example |
| FILTER | Returns the rows that meet a condition | =FILTER(A2:C100, C2:C100>500, "None") |
| SORT | Sorts a range | =SORT(A2:C100, 3, -1) |
| SORTBY | Sorts one range by another | =SORTBY(A2:A100, C2:C100, -1) |
| UNIQUE | Returns distinct values | =UNIQUE(B2:B100) |
| SEQUENCE | Builds a series of numbers | =SEQUENCE(12, 1, 1, 1) |
| XLOOKUP | Looks up and can return several columns | =XLOOKUP(F2, A2:A100, B2:D100) |
| VSTACK / HSTACK | Stacks ranges on top of or beside each other | =VSTACK(A2:B10, D2:E10) |
| TAKE / CHOOSECOLS / CHOOSEROWS | Pulls chosen rows or columns from an array | =TAKE(A2:C100, 5) |
Function availability differs by version, so confirm each one in your own copy of Excel before building a report that depends on it.
FILTER: pull matching rows
FILTER replaces the old routine of applying an AutoFilter, copying the visible rows and pasting them somewhere else. Microsoft's FILTER page describes it as filtering a range of data based on criteria you define. The third argument, if_empty, is the text to show when nothing matches. Without it, an empty result gives a #CALC! error.
You can combine conditions. Multiply them for AND, add them for OR:
=FILTER(A2:D500, (B2:B500="West")*(D2:D500>1000))
That returns every row where the region is West and the amount is above 1,000. Because the output is live, a new row added inside the source range appears in the result without any action from you.
UNIQUE and SORT: build clean lists
UNIQUE gives you a live list of distinct values from a column. SORT puts them in order. Nest them to get a sorted list of departments, customers or product codes:
=SORT(UNIQUE(B2:B500))
This is the standard way to feed a drop-down list or the row labels of a summary table. When someone adds a new department to the source data, the list picks it up.
SEQUENCE: generate rows, dates and counters
SEQUENCE creates numbers by rows, columns, a start value and a step. Combine it with dates to create a calendar column:
=DATE(2027,1,1)+SEQUENCE(31)-1
This lists the 31 days of January 2027. Format the spill range as dates, and the format follows the result.
XLOOKUP and returning more than one column
A lookup that returns a whole row used to need one formula per column. With a multi-column return range, one XLOOKUP spills across. If you still use older lookups, our explainer on LOOKUP, INDIRECT and OFFSET shows what they do and where they struggle, which helps you decide what to replace first.
Chaining functions into one formula
The real payoff comes when you chain these. A single cell can produce a sorted, filtered, de-duplicated report:
=SORT(UNIQUE(FILTER(B2:B500, D2:D500>1000)))
Read it from the inside out: keep rows where the amount is over 1,000, remove duplicates, then sort. Write chains in steps, checking each layer spills as expected before wrapping the next function around it. For a wider reference of what each function does, keep our Excel functions cheat sheet open in another tab.
Which Excel versions support dynamic arrays
Microsoft released dynamic array formulas to Microsoft 365 subscribers in the Current Channel in January 2020. If you use Excel for Microsoft 365 today, you have the core behavior. Newer functions such as VSTACK or TAKE arrived later and can depend on your version and update channel, so test before you share.
The risk is on the other side of the file. The same Microsoft page explains that when a workbook with dynamic array formulas opens in a version that is not dynamic-aware, the formulas may appear as legacy CSE formulas. They can show braces, and they may no longer resize. If you send a model to a client or a colleague on an older install, do one of three things: ask which version they have, paste the results as values for a static copy, or avoid the newest functions in the shared file.
The @ operator, tables and linked workbooks
Open an old workbook in a current version and you may notice an @ sign inside formulas that did not have one before. That is the implicit intersection operator. Microsoft explains that it was introduced as part of the upgrade to the formula language that supports dynamic arrays, and it returns a single value where a formula could otherwise return several.
Why it matters: in older Excel, a formula that referenced a range where a single value was expected quietly picked one value, usually the one in the same row or column. Excel now lets such a formula spill instead, so it adds @ to old formulas to keep their original behavior. If you want the new behavior, delete the @ and press Enter. If the result then collides with other cells, you will see the spill error below.
Two practical notes:
- Tables: a spilled formula cannot live inside an Excel table, because tables expect one value per cell. Put the formula outside the table and point it at the table's columns. Structured references inside tables often show an @ for row-level calculations, and that is normal.
- Linked workbooks: if a formula in one file refers to a spilled range in another, keep the source file in a stable location and test the link after the source data changes. Spill sizes can change, and a closed source file may behave differently from an open one.
If you are unsure how Excel evaluates references in the first place, our
guide to how Excel works covers the calculation model from the ground up.
Fixing the #SPILL! error
The #SPILL! error is the one every dynamic array user meets in the first hour. Microsoft states that it appears when something blocks the output range, and the fix is to remove the blockage or move it elsewhere.
Work through it in this order:
- Click the cell with the error. A small warning icon appears. Its menu names the cause and offers to select the obstructing cells.
- Look for visible data in the path. Even a single value in one cell of the spill range blocks the whole result.
- Look for invisible data. A space, an empty-text formula or a formatted blank cell can block a spill. Select the range and clear contents.
- Check merged cells. Spills cannot pass through merged cells. Unmerge them or move the formula.
- Check for a table. Move the formula outside any Excel table.
- Check the size. A formula that tries to return an enormous range, for example a whole column times a whole column, can run out of room. Narrow the references.
The pattern behind most spill errors is a worksheet that mixes calculation and manual notes in the same area. Keep results in their own region with empty space beneath. When a sheet has grown into a tangle of formulas, notes and hand-entered rows, it can be a sign to consider
moving spreadsheet data into an app with proper forms and structured records, rather than patching the grid again.
Practical patterns worth learning first
You do not need every function. These four patterns cover a large share of reporting work.
A live top-five list. =TAKE(SORT(A2:C500, 3, -1), 5) returns the five highest rows by the third column. It updates when the numbers change.
A dependent summary. Put =SORT(UNIQUE(B2:B500)) in F2, then in G2 use =SUMIF(B2:B500, F2#, D2:D500). The totals spill beside each department, because the criteria are an array.
A conditional extract for a manager. =FILTER(A2:E500, E2:E500<TODAY()) lists items past their due date. Pair it with formatting rules, and you can highlight the worst rows; see how conditional formatting formulas and formatting based on another cell work.
A conditional result in a spill. Wrap logic around arrays with IF, as in =IF(C2:C100>500, "High", "Normal"). If IF is still shaky, practice it with IF function exercises first, since almost every dynamic array pattern leans on it.
Where to practice
Spilling is something your hands have to learn. Reading that =FILTER(...) returns a block of rows is easy. Predicting where the block ends, and what blocks it, takes repetition. On LogicExcel you type formulas into an Excel-like grid and get feedback right away, with no account and no software to install. Start with 95 free Excel exercises to build the formula habits dynamic arrays rely on, or follow the structured Excel courses if you want a path from the basics upward.
If you are preparing for a job test, the free Excel test for interviews and assessments lets you rehearse under realistic conditions.
Our perspective
We build LogicExcel around one belief: you learn Excel by entering formulas, not by watching someone else enter them. Dynamic arrays are a good test of that belief. Video walkthroughs make FILTER and SORT look obvious, but the first time you build a chain yourself, the spill lands on a note you forgot about, or you wrap one function too many. Those mistakes are where the understanding forms.
Our advice to analysts, managers and career changers is to learn dynamic arrays after the foundations, not instead of them. Know what a reference is, know how a formula copies, and know what each lookup does. Then the spilled result feels like a shortcut and not a mystery. We also advise building any shared model in the simplest form your audience can open. A clever formula that breaks on a colleague's older install is not clever.
When you hit an error, read it as information. Every #SPILL! tells you where the formula wanted to go and what stood in the way. That is why we explain every mistake in the exercises instead of only marking an answer wrong.
> — Thomas Houza
FAQ
Do I need Microsoft 365 to use dynamic arrays?
Dynamic array formulas first shipped to Microsoft 365 subscribers in January 2020, according to Microsoft. Whether a given function is available depends on your version, so type its name in a cell and see whether Excel recognizes it.
Do I still need Ctrl+Shift+Enter?
No. In a dynamic-array-aware version, press Enter and the formula spills on its own. The legacy method required Ctrl+Shift+Enter and a fixed output range, as Microsoft's comparison lays out. You only meet it today when opening older files.
Why do I see @ in my formula?
Excel adds the implicit intersection operator to formulas from older workbooks so they keep returning a single value, as Microsoft explains. If you want the formula to spill, remove the @ and press Enter, then make sure the cells below are empty.
Can I edit just one cell inside a spill range?
No. Only the anchor cell holds the formula, so you change the result by editing that one cell. If you need to keep part of the result, copy the spill range and paste it as values somewhere else.
Sources
Recommended
