Excel #SPILL! error: how to fix it

Since Excel introduced dynamic arrays (Microsoft 365, Excel 2021 and later), a single formula can return multiple results that automatically spread into neighbouring cells. That spreading is called a spill. If it can’t, you see the #SPILL! error and the rest of your calculation stays blank. This guide explains exactly why that happens and how to solve it, situation by situation.

What the #SPILL! error means

The #SPILL! error (called #OVERLOOP! in Dutch versions) appears when a formula produces several values but the area where those values should land isn’t completely empty. Excel calls that area the spill range.

A concrete example: if you put =A2:A100 in cell D2, Excel wants to place 99 values in D2 through D100. If D50 already contains something, the formula cannot fully spill and D2 shows the #SPILL! message instead of the list of numbers.

Click the error cell and Excel draws a dotted blue border around the area where the values should have appeared. You immediately see how large the spill range is and exactly where the blockage sits. The error message is therefore not a bug in your formula – it’s a collision with something that’s already in the way.

Important to know: only the first cell of the range holds the real formula. The remaining cells are an echo of that one formula; you can recognise them by the light-grey appearance in the formula bar. Delete the top-left cell and the whole range disappears. This behaviour explains why you can only edit a spilling formula in its top-left corner.

The error occurs the moment Excel calculates the formula – immediately after you enter it or after data in the source changes. If your list grows and the array bumps into a cell that was previously free, a working formula turns into a #SPILL!. That makes the error tricky to reproduce: the file worked yesterday, but today someone has typed a note underneath the formula.

Functions that cause most spill errors

The #SPILL! error belongs to functions that by definition return multiple values. Recognise these functions and you’ll know exactly what to look for.

• FILTER returns every row that meets a condition. The number of rows varies with the data, so the spill range grows and shrinks along with it.
• UNIQUE extracts the unique values from a list. When new categories appear, the result gets longer.
• SORT and SORTBY return an entire list in sorted order.
• SEQUENCE generates a sequence of numbers, for instance =SEQUENCE(10) for 1 through 10.
• XLOOKUP can return multiple columns at once and spills to the right.

Because the size of these functions’ results isn’t known in advance, keeping exactly enough space free is harder – which is precisely why they clash with an occupied cell most often. If you regularly work with lookup formulas that return an array, VLOOKUP vs XLOOKUP explains when XLOOKUP delivers a whole range.

Removing a blocking cell solves most cases

The most common cause is simple: something sits inside the spill range. Excel helps you find that cell straightaway.

1. Select the cell showing the #SPILL! message.
2. Click the yellow warning triangle that appears to the left of the cell.
3. From the menu choose Select Obstructing Cells. Excel jumps directly to the cell – or cells – blocking the spill.
4. Delete or move the content of those cells. As soon as the area is empty, the formula fills itself in.

Watch out for invisible content. A space, a manual line break, or white text still counts as occupied. If you’re unsure, select the whole spill range and press Delete before re-entering the formula.

Sometimes the blockage isn’t ordinary text but formatting – for example, a cell that once held a formula and now returns an empty string (""). Such a cell looks empty but doesn’t count as empty. Check with =ISBLANK(D50): if it returns FALSE, something is there that you need to clear first.

Every #SPILL! error falls under one of six causes

Excel tells you the exact reason in the text next to the warning triangle. This table matches each reason to its solution.

Referring to an entire column makes the spill range run off the worksheet

A frequent mistake is pointing a dynamic function at a whole column. Take =UNIQUE(A:A) in C1. Excel wants to list the unique values below C1, but because the source runs to over a million rows, the result would extend past the last row of the worksheet. That isn’t possible, so you see #SPILL! with the message that the range extends beyond the worksheet.

The solution is to use a bounded range. If you know your data stops at row 5000, write:

=UNIQUE(A2:A5000)

If you still want to work with the whole column so that new rows are automatically included, turn the source data into an Excel Table (Ctrl+T) and refer to the table column instead. The table only grows to its last populated row, not to the end of the worksheet, so the error stays away.

Merged cells and Excel tables always block spill

Two causes from the table appear so often they deserve separate attention.

Merged cells. A spilling formula cannot write into a merged cell. Select the spill range, go to Home and turn off the Merge & Center button. If you still want the visual layout, use Format Cells (Ctrl+1) with the Center Across Selection alignment instead of merging. That keeps the centred look without physically merging cells.

Excel Tables. Inside a table you’ve created with Ctrl+T, a dynamic array formula cannot spill. You have two options: click inside the table, go to Table Design and choose Convert to Range, or place the array formula in a cell outside the table. Never put the formula in a table column if you expect multiple results. A table is a fine source for a spilling formula, but not a place for the result to land.

Using @ to force a single value and # to reference the whole spill range

Sometimes you don’t want a spill at all. The @ sign (the implicit intersection operator) limits a formula to a single value. Place @ in front of the reference and Excel picks only the value on the same row:

=@A2:A100

This approach is useful if you want to mimic the old, non-spilling behaviour from earlier Excel versions – for example, when converting an existing file where the formula was supposed to return one result per row.

The other way around, you can refer to a successful spill range in one go with the hash symbol (the spill reference). If your array sits in D2, then D2# points to the entire area the formula fills. Handy for follow-up calculations that automatically grow:

=SUM(D2#)
=COUNT(D2#)

When you later add rows to the source, the sum and count grow with them, without adjusting the range. This spill reference also works in charts and data validation, so a drop-down list automatically shows the latest items.

Restoring a FILTER formula: a practical example

Take a concrete case. Cell F2 contains =FILTER(A2:C500, B2:B500="North") to show every row for the North region. The formula gives #SPILL! because columns G and H within the spill range still hold old notes.

1. Click F2, then the yellow triangle, and choose Select Obstructing Cells. Excel selects the populated cells in G and H.
2. Move those notes to an empty area further along, say column M.
3. Once the range is free, FILTER fills all the ‘North’ rows starting at F2, to the right and downwards.

If you’re unsure whether the result will become too large, first count how many rows meet the condition with =COUNTIF(B2:B500, "North"). Knowing that number lets you reserve exactly enough empty rows beneath F2 and avoid the error repeating.

Preventing #SPILL! errors by keeping space free

The easiest way to prevent the error is to structure your worksheet so that array formulas always have room.

• Give every spilling formula its own column and keep the cells below empty.
• Reserve at least as many empty rows as your source range has.
• Don’t refer to entire columns (A:A) next to a spilling formula; use a bounded range or a table column.
• Avoid volatile functions inside an array when a fixed size matters.
• Don’t place multiple spilling formulas directly below one another; leave space for the longest variant.
• Make sure colleagues who share the workbook don’t type into the spill range – a short note or coloured marking beneath the formula helps.

If you still get an error that looks like a reference problem rather than a space shortage, see the steps in How to fix the #REF! error: cause and repair. For more background on the spill range and the exact messages, have a look at the official Microsoft explanation.