When your PivotTable can’t expand because something is in the way—such as data, merged cells, or the worksheet’s edge—you’ll see the #SPILL! error. This error replaces the previous pop-up dialog, allowing you to resolve the error on your own time, and aligns PivotTable errors with the familiar experience of dynamic array formulas and spilled arrays. To learn more, see Dynamic array formulas and spilled array behavior.
When might PivotTable #SPILL! happen?
PivotTable #SPILL! can occur in several common scenarios:
-
On insert or move – when you add or move a PivotTable into an area that already contains data or objects
-
On refresh or auto refresh – when the PivotTable refreshes with new data and expands. To learn more about PivotTable Auto Refresh, see Refresh PivotTable data
-
When updating the layout – for example, adding filters, fields, or grand totals that expand the PivotTable range
PivotTable auto refresh
When a PivotTable is refreshed (for example, manually or on file open), it updates to reflect changes in the source data. If the expanded data encounters blocking content during the refresh, a #SPILL! error may appear. To control when updates occur, you can refresh the PivotTable manually from the PivotTable Analyze tab or configure refresh settings in PivotTable Options. You can also temporarily suspend updates by setting Excel to manual calculation mode.
Why am I seeing #SPILL! with PivotTables?
A PivotTable shows #SPILL! when it cannot fully expand due to something in its way. Your PivotTable may look hidden, but your data is safe—the PivotTable still exists in the cell you placed it. To resolve the error, clear or move the blocking data, or move your PivotTable to an area with enough space.
Note: When you select the #SPILL! cell, you’ll see the PivotTable outline, the PivotTable tab in the Ribbon, and you can still access the PivotTable Fields pane.
Note: Slicers and Timelines can filter a PivotTable and reduce its displayed size, which may temporarily prevent overlap issues, but they do not resolve underlying errors or structural problems.
Common causes of PivotTable #SPILL! errors:
|
Cells containing data or formulas: The PivotTable needs enough space to expand. If any cell in its expansion area contains data or a formula, you’ll see #SPILL!. |
 |
|
Merged cells: PivotTables can’t expand if merged cells are blocking the area. Unmerge these cells or move the PivotTable. |
 |
|
PivotTable extends beyond the worksheet edge: If the PivotTable would overflow the grid, you’ll see #SPILL!. Move or resize the PivotTable to fit within the worksheet. |
 |
How to fix the PivotTable #SPILL! error
-
Locate the obstructing data
-
Manually select the cells blocking the PivotTable, or
-
Use the Select Obstructing Cells option in the error menu
-
-
Clear or move the obstructing data
-
Clear or move the obstructing cells, or
-
If the blockage is a table or another PivotTable, you may need to move or delete them
-
-
Alternatively, move or resize your PivotTable to an area with enough space
Note: Dependent objects, such as PivotCharts or formulas using PivotTable results, will show errors if the PivotTable is in a #SPILL! state. Resolve the error state to restore them.
