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
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.
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.
