A PivotTable spill error usually means a dynamic-array formula cannot place its results because the intended spill range is blocked; it does not usually mean the PivotTable itself is broken. In Excel for Microsoft 365 on Windows, select the #SPILL! cell, inspect the outlined output range and remove or move whatever is blocking it—or move the formula to an area where its results can expand.
What a PivotTable spill error means
#SPILL! is an Excel error for a formula that should return multiple results but cannot put those results into the worksheet grid. Functions such as FILTER, SORT and UNIQUE can return arrays. Excel places the first result in the formula cell and spills the remaining results into neighboring cells. If even one required destination is unavailable, the formula may return #SPILL!.
A PivotTable and a spill range are different things. A PivotTable is an interactive report whose output can change when you refresh it, filter it or rearrange its fields. A spill range is the output area of a dynamic-array formula. The error often appears near a PivotTable because its report occupies cells the formula needs, or because a refresh has made the report larger. The error is reported by the formula that cannot spill, not necessarily by the PivotTable.
This distinction matters because the remedies differ. Clearing a formula’s destination cells may fix a spill error, but clearing PivotTable results can damage the report or fail because those cells belong to the PivotTable. Likewise, a PivotTable that cannot expand may produce an overlap warning rather than a #SPILL! error.
How to find the obstruction
Start with the cell that displays #SPILL!. Select it and look for a dashed outline showing the intended spill area. If the error indicator offers the option, click the warning icon and choose Select Obstructing Cells. Excel will select a cell or cells that prevent the output. The exact obstruction can be hard to spot if the range is wide, contains blank-looking cells or extends beyond the visible part of the sheet.
Check the whole outlined range, not just the first cell beside the formula. A single value, a formula returning an empty string, or a space typed into a cell can block a result. Also check whether the intended output crosses a PivotTable, another report, or the edge of the worksheet. A cell that looks empty may still contain content; select it and inspect the formula bar before deleting anything.
To see the formula responsible, select the error cell and read the formula bar. If it contains a dynamic-array function, the spill explanation is likely. If the error is inside the PivotTable’s own displayed area, verify whether the cell is actually part of the report or whether a separate formula was entered next to it. This prevents changing the wrong object while troubleshooting.
Common causes near a PivotTable
- The PivotTable occupies the spill area. A formula beside or above a report may have room today, but its output may reach cells used by the report.
- A refresh expanded the report. More categories, fields or source records can increase the PivotTable’s size. A spill formula that previously fit may then collide with the expanded output.
- Cells that appear blank are not empty. Existing values, formulas, spaces or other content can obstruct the spill. A formula returning an empty string still occupies a cell.
- The formula is inside an Excel Table. Dynamic-array formulas cannot spill from within a formatted Excel Table. A PivotTable is not the same thing as an Excel Table, even though both display data in rows and columns.
- Merged cells intersect the output area. A spill range cannot expand through merged cells. Unmerge the affected cells if appropriate, or place the formula elsewhere.
- The spill would run off the worksheet. If the formula needs more rows or columns than remain on the sheet, Excel cannot display the complete result.
Not every problem involving a PivotTable is a spill error. If refreshing produces a message that the report cannot overlap another PivotTable or existing data, the problem is the PivotTable’s destination area. If a cell displays #REF!, #VALUE! or a different error, investigate that error rather than assuming a blocked spill range.
Fix the error without damaging the report
- Inspect before clearing. Use the spill outline and obstruction selection to identify the cells in conflict. Confirm whether they hold source data, formulas, notes or part of the PivotTable.
- Move the formula when the report needs its space. Put the dynamic-array formula in an unused area, a separate worksheet or a dedicated analysis sheet. Leave enough empty rows and columns for the result to grow.
- Move or resize the PivotTable if its layout can change. You can move a PivotTable to another location or worksheet so its changing report does not overlap a spill area. Consider how refreshes may change its dimensions before choosing a new location.
- Remove only unintended blockers. If a stray value or space is genuinely unnecessary, clear that cell. Do not clear the PivotTable’s output or important worksheet data just to make the error disappear.
- Check for merged cells or an Excel Table. Unmerge cells only if doing so is acceptable for the layout. If the formula is within an Excel Table, put it outside the table; making the table wider will not create a valid spill area inside it.
- Refresh and test again. After arranging the report and formula, refresh the PivotTable and confirm that the spill still has room. A fix that works only before refresh may not be durable.
If the cells are in a PivotTable, avoid typing over them or deleting them individually. PivotTable output is managed as a report, and a refresh may rebuild that area. When the report needs more room, change the report’s location or the formula’s location rather than trying to reserve individual PivotTable cells.
Example: a formula beside a changing report
Suppose a PivotTable begins in A3 and currently ends in C15. A separate list formula is placed in D4, and its results extend down column D. It may work while its output stops before the PivotTable’s next refreshed boundary. If the formula instead starts in B4 and needs to spill through cells occupied by the report, Excel cannot place the results there. The formula’s intended output range, not the fact that it is near a PivotTable, determines whether it can spill.
For a concrete example, assume the source category names are in A2:A100 and an unused area starts at E2. Enter =SORT(UNIQUE(A2:A100)) in E2. Excel 365 returns a sorted list of distinct names downward from E2. If the source includes North, South and North, the result is North and South, one of each. If any required destination below E2 contains content, the formula can return #SPILL!. Put the formula in an area with room, or clear only confirmed unwanted blockers.
For a report that must remain adjacent to a PivotTable, leave a buffer of several columns or rows between the report and the formula’s spill area. The right buffer depends on how much the PivotTable can grow; there is no universal number that guarantees separation. If the PivotTable’s categories change frequently, a separate worksheet is often more reliable than relying on a narrow gap.
Preventing the error after refreshes
Plan for the report’s future size, not only its current appearance. A PivotTable may change height or width when new source records introduce categories, when fields are added, or when its layout changes. A dynamic-array formula can also return more results as its source data grows. These two changing areas are more likely to conflict over time than a fixed report and a fixed formula.
- Keep analytical formulas in a clearly separate area or on a separate worksheet.
- Leave room for both the PivotTable and the largest plausible spill output.
- Refresh the PivotTable during checks of the workbook, especially after adding source categories or changing fields.
- Do not treat a currently blank-looking gap as a permanent boundary if either output can grow into it.
- Document the intended locations of reports and formulas in shared workbooks so collaborators do not place content in a spill range.
When possible, use a stable layout: keep the PivotTable in one region and formula-driven summaries in another. This makes it easier to distinguish report output from worksheet formulas and reduces accidental overwriting during later edits.
When to use another approach
If the goal is to summarize a changing list of records, a PivotTable may already provide the grouping and totals you need. Use its field layout, filters and refresh controls rather than building a second report that duplicates the same summary. If you need a separate list of distinct items or a custom formula-driven result, keep that result outside the PivotTable’s changing footprint.
For a fixed calculation beside a PivotTable, a conventional formula that returns one value per cell may be more suitable than a dynamic array, provided you understand how the formula behaves when the PivotTable changes. Another option is to place the calculation on a separate sheet and reference the report there. The best choice depends on whether the result should grow automatically and whether it must stay beside the PivotTable.
Do not convert a PivotTable to ordinary cells simply to hide a spill error unless you no longer need PivotTable features such as refreshing, field rearrangement or interactive filtering. Converting or copying a report changes how it can be maintained. First determine whether moving one formula or relocating the PivotTable solves the actual conflict.
Frequently Asked Questions
Can a PivotTable itself show #SPILL!?
A standard PivotTable does not use dynamic-array spilling to build its report. A nearby dynamic-array formula is often the source of #SPILL!. Check the formula bar and the spill outline to identify which cell is generating the error.
Why did the spill error appear after I refreshed the PivotTable?
The refresh may have changed the PivotTable’s size, so its output now occupies cells the formula needs. Compare the spill outline with the refreshed report and move either the formula or the PivotTable to a location with enough room.
Can I clear the cells inside the spill outline?
Only clear cells after confirming they contain unwanted content. If they belong to the PivotTable or hold useful data, do not delete them; move the formula or report instead. A blank-looking cell can also contain a space or a formula.
Does a PivotTable count as an Excel Table?
No. An Excel Table is a structured data range, while a PivotTable is a separate report object. A dynamic-array formula cannot spill from inside an Excel Table, but a PivotTable conflict usually occurs when the report occupies cells the formula needs.
Will the error go away if I hide the obstructing column?
No. Hiding a row or column does not make its cells available for a spill range. The formula still needs unobstructed destination cells, so move the formula or remove the actual conflict.
Related
- Excel Data Validation Techniques for Reliable Sheets
- Data Validation Shortcut Key in Excel: Windows and Mac
- Data Validation in Excel: Steps for Reliable Entries
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Dynamic Title: Link It to a CellTo make an Excel chart title dynamic in Microsoft 365 for Windows, put the title text or a…
- Balance Graph in Excel: Make One With a Running TotalTo make a balance graph in Excel, put dates and running balances in adjacent columns, select those columns,…
- Excel Add Horizontal Line to Chart: Steps and TipsTo add a horizontal target or reference line to an Excel chart, add a data series whose value…
- Excel Graph Reverse Data Order: Flip an Axis or SeriesTo reverse the order of categories in an Excel graph, select the chart, right-click its category axis, choose…
- Excel Change Pivot Table Data Range: Steps and FixesTo change a PivotTable’s source range in Excel for Microsoft 365 on Windows, click inside the PivotTable, choose…
- Pivot Chart Axis Options in Excel: What You Can ChangeIn Excel for Microsoft 365 on Windows, PivotChart axis options let you control what appears on the category…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.