What a spill actually is
A dynamic array formula returns more than one value, and Excel needs a clean grid of empty cells to drop those results. Functions like =UNIQUE, =SORT, =FILTER, and =SEQUENCE are spilling by design. The Sales table on my dashboard uses =UNIQUE(Products[Category]) to list every product category, and that spills into 6 cells below. When those cells are not empty, Excel raises #SPILL! instead of overwriting your data, which is the safe behavior. Understanding this saves you from the wrong fix of wrapping everything in a single-cell formula.
How to read the spill marker
Click the cell showing #SPILL!. Excel draws a light blue border around the full range it wanted to fill. That border is your target area to clear.
A small yellow triangle appears at the cell. Hover it and Excel tells you the reason, for example Spill range isn't blank or Table formula can't spill. Note the exact text before fixing.
Press F2 to see the formula and which range it references. If it points at Orders[Amount], the spill size equals the number of matching rows, so you know how many blank cells you need.
The blue border Excel draws is not decoration. It shows exactly how many rows and columns the formula tried to return, which tells you the size of the blockage. When =SORT(Orders[Amount]) wants 1,200 rows but only 3 are clear, you know 1,197 cells are occupied. I select the whole bordered area with one drag and press Delete, then watch the results fill in. This single habit fixes most spills in under ten seconds.
Fix 1: the spill range is not blank
Select every cell inside the blue border except the formula cell, right-click, and choose Clear Contents. The formula spills immediately once the blockage is gone.
A single space or a stray comment in the range counts as data. Use Ctrl+G, Special, Constants to highlight every non-empty cell in the area so you do not miss an invisible blocker.
In my experience the most common blocker is a totals row or a handwritten note placed one row under the formula for later reference. I recommend keeping spilled results on their own tab so nothing drifts into the range. A common mistake is typing a label in the cell below, which looks empty but still blocks the spill.
A blank-looking cell is still data if it holds a formula that returns an empty string. Those cells block spills too. I learned this when a =IF(A2="","","x") helper sat under my =UNIQUE formula and raised #SPILL!. Use Ctrl+G, Special, Formulas to find real formula cells, or just clear the entire column below your spill. Clean space is what dynamic arrays need to expand.
Fix 2: a merged cell sits in the range
Go to Home, Find & Select, Go To Special, choose Blanks is not enough here. Instead scan the blue border visually; merged cells are usually wider or taller than neighbors.
Select the merged area, click Merge & Center to unmerge, then Clear Contents on the extra cells so the grid is uniform. The spill now has a regular rectangle to fill.
Merged cells also break spill when they sit at the edge of the range. Excel cannot drop a rectangular result into a region that is not a clean grid. I see this most when a title is merged across A1:D1 and the formula starts in A2; the first spill row collides with the merge. Unmerge the title row and the spill proceeds. Keep headers unmerged and your arrays will expand without complaint.
Fix 3: you passed a constant array by accident
If your formula looks like =SORT({1,2,3;4,5,6}) and you meant a range, the constant array forces a fixed shape that may not match the destination. Replace it with a real range reference.
Change the constant to something like =SORT(A2:B10). Excel then spills exactly as many rows as the source holds, and the error clears because the shape is no longer fixed.
A constant array like {1;2;3} forces a fixed shape, which often does not match the destination grid. If you meant to sort a column but typed a hardcoded list, the result is a fixed three-cell block that cannot spill. Replace the braces with a live range such as A2:A so the output tracks the data. Live references are the point of dynamic arrays; constants defeat them.
Fix 4: the formula lives inside a table
Calculated columns inside an Excel table cannot spill into other rows. If you typed =UNIQUE(Orders[Region]) inside the Orders table, it raises #SPILL! because a table column must stay one value per row.
Cut the formula and paste it into a normal range below or beside the table. The same =UNIQUE(Orders[Region]) then spills freely outside the table structure.
I recommend building spill formulas in a plain range next to your table, then referencing the table columns. This will break if you keep the formula inside the table, because structured references expect a single result per row.
Fix 5: you wanted one value, not a range
If you only need the first result, prefix the function with @, like =@SORT(Orders[Amount]). The @ tells Excel to return the single value at the formula's row instead of spilling.
Write =INDEX(SORT(Orders[Amount]),1) to grab just the top item. This is cleaner when you feed the result into another single-cell formula such as =SUM of a lookup.
The @ operator is your escape hatch when you truly need one cell. I use =@XLOOKUP(...) patterns inside tables where a spill would violate the one-value-per-row rule. Without @, Excel raises #SPILL! because the table cannot hold a vertical array. Add the @ and you get the first match only, which is often what the row needs. Reach for it sparingly, only when a single value is correct.
Worked example: a filtered East region list
I had a 1,200-row Orders table and wanted every East region row sorted by Amount. I entered =SORT(FILTER(Orders,Orders[Region]="East"),3,-1) in cell D2. It returned 341 rows, but a #SPILL! appeared because cell D40 held a total label. I selected D3:D400, pressed Delete, and the 341 rows filled instantly. The top row showed an Amount of $9,820.00, the highest East sale. Total East revenue from =SUM(FILTER(Orders[Amount],Orders[Region]="East")) was $142,660.50, and the spill let me copy that range straight into a chart.
If a spill still will not resolve, use the spill marker color as a clue: a blue border means blocked, while a red border means the formula itself errored. I trace red spills with Formulas, Evaluate Formula to watch each step return its array. A #REF! inside usually means a deleted column the function pointed at. Rebuild the reference and the spill returns. Keeping the source columns stable is the quiet fix that prevents most recurring #SPILL! cases on shared workbooks.
How to prevent spill errors
Before writing a spilling formula, make sure at least 50 blank rows sit beneath it. A dynamic report that grows each month needs headroom so new results never hit an old note.
Keep the destination area free of merged headers and totals. Put those summaries to the side of the spill range, not inside it.
I also keep spilled formulas on a dedicated Results tab. That single habit removed almost every #SPILL! my team used to hit during the monthly close. The approach pairs well with the Excel formula errors fixes guide when other error types show up.
When a spill formula references a growing table, point it at the whole table object, not a fixed range like A2:A500. Excel tables auto-expand, so =SORT(Orders) keeps working as rows are added. I also use Ctrl+T to turn a raw range into a table the moment I paste new data, which removes the fixed-range problem at the source. Pair that with the @ operator only where a single value is truly needed and your sheets stay error-free through every monthly load.
Next steps
When you see #SPILL!, read the marker first, clear the blue range, and only then consider changing the formula. Use @ or INDEX when you truly need one value, and move spill formulas out of tables. Pair these fixes with =UNIQUE, =SORT, and =FILTER from our dynamic arrays guides, and review the XLOOKUP replacement article when your lookups also spill. Keep output zones clean, leave blank rows, and refresh your model without fear of breaking the grid.
