Excel Pivot Table Spill Error: Causes and Fixes

An Excel PivotTable spill error usually means a dynamic-array formula cannot place all its results because the PivotTable output or other occupied cells block the spill range. Move the formula outside that area, or clear or relocate the obstruction; then check whether refreshing the PivotTable makes it expand into the formula’s space again.

Why a PivotTable can cause a spill error

In Microsoft 365 for Windows, a formula such as SORT or UNIQUE can return multiple results. Excel places the first result in the formula cell and spills the remaining results into neighboring cells. If any cell in the required destination range is unavailable, Excel may show #SPILL! rather than display a partial result.

A PivotTable can create that conflict because it controls a block of worksheet cells. Its displayed headings, values, subtotals, and layout may occupy the space where a formula wants to spill. A PivotTable refresh can also change the size of that block, so a formula that worked yesterday may fail after the source data grows or the pivot is refreshed.

One distinction matters: a PivotTable is not the same thing as an Excel Table, which is a range created with Insert > Table. Excel specifically does not allow dynamic-array formulas to spill from inside an Excel Table. A PivotTable is a different feature, but its output can still block a spill range, and cells in its report are managed by the PivotTable rather than treated like ordinary worksheet cells. Avoid placing a spill formula inside either kind of table.

What Excel means by a spill range

The spill range is the full area a dynamic-array formula needs for its results. For example, with names in A2:A7 listed as Ana, Omar, Ana, Lee, Omar, and Mia, enter this formula in C2, outside any PivotTable output: =SORT(UNIQUE(A2:A7)). In Microsoft 365 or Excel 2021, the result is Ana, Lee, Mia, and Omar in C2:C5. The formula exists in C2; the other displayed results are part of the same spill.

If D4 contains an unrelated value, that value would obstruct the example only if the spill needed D4. In this example it does not: the result occupies C2:C5. But if the formula were in C2 and its results needed C2:C5, a value, merged cell, or other obstruction in C4 could prevent the whole array from appearing. Excel does not simply overwrite that cell to make room.

Cells that look empty can sometimes still cause trouble. A formula returning an empty string is still a formula in that cell, and a PivotTable can control cells that appear blank as part of its layout. Check the actual spill outline and the specific error message rather than assuming a visually blank cell is available.

How to identify the obstruction

Select the cell showing #SPILL!. Read the error message or warning icon; Excel may identify the problem as a nonblank spill range, a merged cell, or another limitation.
Look for the dashed outline around the intended spill range. This shows how much worksheet space the formula is trying to use. Compare that outline with the PivotTable’s visible footprint and nearby formulas, values, merged cells, or objects.
If Excel offers Select Obstructing Cells in the error menu, use it to jump to a blocking cell. Inspect the cell before deleting anything: it may contain data or a formula you need.
Check the formula cell itself. If it is part of a PivotTable report, do not treat it as an ordinary place to store a worksheet formula. Put the formula in a separate area instead.
Refresh the PivotTable after checking the current layout if the problem appeared following a source-data change. A refresh may have enlarged the report and changed which cells it occupies.

If the dashed outline is not visible or the message points to a different cause, the formula may have another spill limitation. Merged cells in the destination, an insufficient worksheet boundary, or a formula attempting an unsupported array operation can also matter. The PivotTable may be nearby without being the actual obstruction.

Fixes that preserve your PivotTable

The safest fix is usually to give the formula its own clear area. Move it several columns to the right of the PivotTable, place it above or below the report with enough room for the maximum expected results, or use a separate worksheet. Leave a buffer if the PivotTable can grow after refresh; moving the formula just one cell beyond its current edge may only postpone the conflict.

Move the formula: Cut and paste it into an ordinary worksheet area where the entire spill range is clear. Verify that the formula’s references still point to the intended source cells.
Move the PivotTable: If the formula belongs beside the report, move the PivotTable to another location or sheet. Select the PivotTable, then use PivotTable Analyze > Move PivotTable where that command is available in the Windows ribbon.
Clear a genuine obstruction: Remove or move the blocking value or formula only after confirming it is not needed. Do not clear PivotTable cells individually to make space; the report is managed as a unit.
Unmerge destination cells: If a merged cell lies in the spill area, unmerge it or choose a different location. A dynamic array needs a normal rectangular range to display its results.
Use a fixed-size result when appropriate: If you need a single value rather than a changing list, revise the calculation to return one result. Do not add an arbitrary implicit-intersection operator just to hide an error; first confirm that a single result is what the analysis calls for.

After making a change, confirm that every intended result appears and that no other data was displaced. Excel does not automatically choose a new destination for a blocked spill range, so the formula will continue to show an error until the obstruction or formula location changes.

Why the error can return after a refresh

PivotTables often change size as their source data or layout changes. Adding records can introduce new row labels; adding a field can add columns; expanding or collapsing groups can alter the visible report. If a dynamic-array formula sits just beyond the current edge, a refresh can cause the PivotTable and the spill range to compete for the same cells.

To make the layout more reliable, decide how much the PivotTable could realistically grow and reserve that space. Alternatively, keep calculations on a separate worksheet and refer to the PivotTable there. If the PivotTable uses a changing source range, using an Excel Table as the source can make newly added source rows easier to include when refreshing, but it does not prevent the PivotTable report itself from expanding into nearby cells.

For a repeatable check, refresh the PivotTable after adding representative new records, then inspect the report’s edges and confirm that the formula still spills completely. Refreshing a PivotTable updates its displayed results; it does not automatically move worksheet formulas out of the way.

Using formulas alongside PivotTable results

Dynamic arrays are useful for creating a distinct list, sorting labels, or preparing a separate summary from source data. Keep those calculations outside the PivotTable’s managed range. If a formula reads the original source data, its result may not match the PivotTable if the PivotTable has filters, grouping, or a different aggregation. Check that the formula uses the same records and logic before comparing the two outputs.

When the goal is to calculate within the PivotTable, use its built-in features where they fit. Value Field Settings can change how a value field is summarized, and PivotTable Analyze provides options for working with PivotTable fields and layout. A calculated field is another PivotTable feature for supported calculations, but it is not a general replacement for every worksheet formula. For a calculation that needs to spill as a list, a worksheet area outside the report remains the more suitable location.

Dynamic-array functions such as SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later. A workbook opened in an older Excel version may not handle those functions the same way. If coworkers use older versions, check compatibility before relying on a spilled formula as part of a shared reporting process.

Preventing repeat problems

Keep PivotTables and spill formulas in separate, clearly labeled worksheet zones.
Allow extra rows and columns around a PivotTable that changes as data is refreshed.
Use a separate calculation sheet when a report’s dimensions are unpredictable.
Before deleting an obstruction, inspect its contents and preserve any needed data.
After changing source data, refresh the PivotTable and verify both the report and any nearby formulas.

On Mac, the same underlying issue applies: a spill formula needs an unobstructed output range, and a PivotTable refresh can change the report’s footprint. Ribbon tab names and locations can differ somewhat by Excel version, so use the PivotTable’s context menu or the corresponding PivotTable ribbon tab to refresh or move it. Excel for the web also supports many dynamic-array formulas, though available PivotTable controls can vary; moving the formula to a clear worksheet area remains a reliable first step.

Frequently Asked Questions

Can a PivotTable itself return a #SPILL! error?
A PivotTable is not a dynamic-array formula, so the error generally belongs to a worksheet formula trying to spill into cells the PivotTable occupies or controls. If the error appears near a PivotTable, select the error cell and inspect the spill outline to confirm the cause.

Why did my formula work before I refreshed the PivotTable?
The refresh may have expanded or rearranged the PivotTable, placing its output in cells the formula needs. Move the formula farther away or to another sheet, then refresh again to confirm the layout has room.

Can I put a dynamic-array formula inside a PivotTable?
Do not use a PivotTable report cell as the home for a worksheet spill formula. The PivotTable manages its report cells, and a formula that needs neighboring output cells cannot reliably spill there. Put it in a normal worksheet range instead.

Is a PivotTable the same as an Excel Table for spill errors?
No. An Excel Table is a structured data range, while a PivotTable is a summarized report. Excel blocks dynamic-array spilling from within an Excel Table; a PivotTable causes a spill problem when its report cells obstruct the formula’s required destination or otherwise control the cells.

Will deleting the PivotTable fix the error?
It might remove an obstruction, but it is rarely necessary and could destroy a report you still need. First move the formula or PivotTable, or clear only a confirmed, unnecessary obstruction. Preserve a copy before making broad changes to important workbook data.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.