What is a spill range in Excel? Definition and the #SPILL! error

Exceljet's definition is the shortest accurate one: "the term 'spill range' refers to the range of values returned by an array formula that spills results onto a worksheet." The formulas that behave this way are dynamic arrays; a spill range is what one of them leaves on the grid, and it is the thing you have to reference, protect and occasionally unblock.

Reading a spill range

The visual cue does the explaining. Ablebits: "When you select any cell in the spilled area, the entire range is highlighted with a blue border indicating that everything inside it is calculated by the formula in the topmost cell." Exceljet describes the same border, and it is the only thing on screen distinguishing a spilled block from twenty cells someone filled down by hand.

Only one cell is editable. Put =UNIQUE(B5:B200) in D5 and D5 holds the formula while D6 downwards hold values; click D6 and, in Ablebits' words, "the formula is still displayed in the formula bar but is greyed out". Delete D5 and the whole block goes with it — there was never anything in the other cells to keep.

The block also has no fixed size. Add rows to the source and it grows; filter the source down and it contracts. That is the feature, and the reason the next section exists: an address like D5:D40 describes the block only until the data changes.

The spill range operator

Excel's answer is a one-character operator: "it's possible to reference the entire spill range by placing the spilled range operator (#) after the range."

Microsoft's worked example is as clear as it gets. With =SEQUENCE(10) in A2, spilling down to A11, =SUM(A2#) is equivalent to =SUM(A2:A11) and returns 55. Change A2 to =SEQUENCE(20) and "the SUM formula will automatically reflect that" — no downstream edit required. The operator composes with anything: =SORT(C2#) sorts a whole spilled array, =FILTER(C2#,LEN(C2#)>3) filters one, and Exceljet adds =COUNTA(D5#) and =INDEX(D5#,3).

One documented limit is worth knowing before you build a workbook around it: "The spilled range operator does not support references to closed workbooks. Instead it will return a #REF! error value." Ablebits also notes you need not type the hash at all: "select all the cells inside the blue box using the mouse. Excel will create an appropriate spill reference automatically."

The #SPILL! error

When the block cannot be written, nothing is overwritten and the top cell reports the problem instead: "#SPILL errors are returned when a formula returns multiple results or a PivotTable range is not blank, and Excel cannot return the results to the grid."

Microsoft documents seven causes, and the fix depends entirely on which one you have.

Three are worth expanding. The blocked-range case is the common one, and the obstruction is almost never visible — a stray space typed into a cell years ago counts as content. Do not hunt by eye; the cell's error alert has a Select Obstructing Cells option that highlights exactly what is in the way.

The table case is not a bug to work around. A column in an Excel table already has a rule for what every cell in it contains, and a formula that decides its own height contradicts it. A dynamic array formula may take a table as its input; it may not live inside one.

The size-is-volatile case explains the baffling =SEQUENCE(RANDBETWEEN(1,10)) failure. Excel needs to know how big the block will be before writing it, and a formula that answers differently on each calculation pass cannot give that guarantee.

Excel and Google Sheets

The behaviour is shared; the vocabulary is not. Both applications let one formula fill neighbouring cells, but "spill range", the # operator and the #SPILL! value are Excel terms documented by Microsoft, and a formula leaning on E5# to address a growing result is one of the constructs that has to be rewritten rather than pasted when a workbook moves to Sheets. Formula errors covers what each error value means on each side.

How Formula Foundry handles this

The # operator is exactly the kind of construct that does not survive a copy-paste between applications, and that is where Formula Foundry has something concrete to offer. Set the editor's dialect to Microsoft Excel, paste the Excel formula, and the Excel to Sheets translator rewrites it into working Sheets syntax — remapping function names, collapsing IF(ISERROR()) into IFERROR, stripping the implicit-intersection @, wrapping implicit-array operations in ARRAYFORMULA(), and alerting you when a construct cannot be transferred rather than producing something that looks right and returns the wrong shape. It is in beta, and that last alert is the honest part: some things do not convert, and being told beats finding out three columns later.

Be equally clear about the limit. Formula Foundry does not evaluate your workbook — it parses, formats, explains and translates, with no calculation engine of its own. It cannot show you the blue border, cannot tell you that D9 holds a space that will block your spill, and does not know how many rows UNIQUE will return. Every #SPILL! diagnosis starts and ends in Excel.

What the add-on adds is legibility around the formula that produced the block. A FILTER inside a SORT inside a UNIQUE is three ranges and three sets of brackets on one line in the formula bar; in the sidebar it opens across multiple lines, indented along the nesting, with functions, ranges, constants and named references colour-coded and matching parentheses highlighted, so an argument in the wrong bracket stops being invisible. The AI assistant explains an existing formula step by step in plain English and offers debug hints for the usual suspects. The Excel to Sheets translation walkthrough runs the conversion on a real formula.

FAQ