In Excel, a spill error is the standard #SPILL! error: a dynamic-array formula cannot put all of its results into the cells it needs. A PivotTable does not normally generate this error itself. It can cause one when its report occupies cells where a formula needs to spill, or when someone tries to put a spilling formula inside a PivotTable. The safe fix is to identify the formula’s intended output range, then move the formula or clear a genuine obstruction without deleting PivotTable results.
What does spill error mean in Excel?
Spill is Excel’s term for a dynamic-array formula returning several results into neighboring cells. Instead of entering a formula separately in each cell, you enter one formula in a top-left cell and Excel fills the required range. That range is called the spill range. When Excel cannot place the complete result, it displays #SPILL! in the formula cell.
The error name is standard Excel terminology, not slang or an informal name for a PivotTable problem. The ordinary word spill describes results flowing into adjacent cells; Excel uses it for this dynamic-array behavior and its related error. In a sentence, a user might say, “My UNIQUE formula returns a spill error because its output overlaps the PivotTable.” That is a useful description, but it does not mean the PivotTable has a special error type called a spill error.
Dynamic arrays are available in Microsoft 365 and supported newer Excel versions. Formulas such as UNIQUE and SORT can return multiple results. Excel places the formula in one cell and shows the results in the cells below or beside it. The formula cell is the anchor; the surrounding results are not separate formulas that can be edited independently.
Why a PivotTable can be involved
A PivotTable is a report that Excel manages and refreshes from source data. Its cells are occupied by report labels, values, subtotals, or other PivotTable output, even though the report may look like an ordinary grid. If a dynamic-array formula’s intended spill range crosses those cells, Excel cannot put the results there and may show #SPILL!.
The reverse situation is also important: dynamic-array formulas cannot spill from within an Excel table, and a PivotTable is not a place to insert a formula that should fill neighboring report cells. A PivotTable controls its own report area. Put separate calculations outside that area, or use a PivotTable feature intended for calculations, such as a calculated field where appropriate. The exact options available depend on the type of source and the calculation required.
For example, suppose a PivotTable fills cells D3:D20. A formula in D2 may need to return results in D2:D5. Although D2 is outside the report, the required output also includes D3:D5, which the PivotTable occupies. Excel cannot partly spill the result, so it reports the obstruction at the formula’s anchor cell. The error is about the required output range, not necessarily the cell containing the formula.
How to identify the cause
Start by selecting the cell showing #SPILL!. Excel may show a warning icon beside it with an explanation and, for some obstruction types, an option to select the obstructing cells. You can also estimate the intended spill range from the formula and check every cell in that area. Do not assume that the first visible cell is the only one that matters.
Look for these common causes, especially around a PivotTable:
- PivotTable output in the spill range: labels, numbers, blanks in the displayed layout, or subtotals may be part of the report area that Excel controls.
- Other cell contents: a value, space, or formula can block a result. A cell that displays nothing may still contain a formula, such as one returning an empty string.
- Merged cells: a spill range cannot pass through merged cells. Formatting alone does not usually block a spill, but merging does.
- An Excel table: dynamic-array results cannot spill within a structured Excel table. This is distinct from a PivotTable, although both may appear as report-like grids.
- A spill range that changes size: the results may need more cells after source data changes or a formula recalculates, bringing the range into a PivotTable.
When you inspect the range, distinguish a truly empty cell from a cell that only looks empty. Also check whether the PivotTable’s displayed size changed after a refresh. Refreshing can add or remove report rows, which may create or remove a collision with a nearby formula.
Example: a formula blocked by a PivotTable
Assume A2:A6 contains the entries North, South, North, East, and South. Enter the following formula in D2, outside the PivotTable, to list the distinct entries in alphabetical order. This example requires Microsoft 365 or a supported newer Excel version with UNIQUE and SORT.
With source entries in A2:A6 and the formula entered in D2, use =SORT(UNIQUE(A2:A6)). If the cells below D2 are clear, the formula returns East, North, and South in D2:D4.
If a PivotTable occupies D3 or D4, Excel cannot place all three results in D2:D4. The formula therefore shows #SPILL!, even though D2 itself is not part of the PivotTable. Move the formula to a location with enough clear cells, or move or resize the PivotTable so its report no longer overlaps the spill range. Do not delete D3 or D4 merely to make the error disappear: those cells may contain report results that will return on refresh.
How to fix it without damaging the report
Use a method that addresses the actual obstruction rather than clearing cells at random. On Excel for Microsoft 365 on Windows, try these steps:
- Select the
#SPILL!formula cell and read Excel’s error message. If a warning icon offers Select Obstructing Cells, use it to inspect the highlighted cells. - Determine whether the obstructing cells belong to the PivotTable. Check the PivotTable’s full report area, not just the cells containing visible numbers. If they do, leave the report intact.
- Move the dynamic-array formula to a clear area with enough room for its results. Leave space for the spill range to grow if the source data may change.
- If a PivotTable is placed too close to the formula, move the PivotTable or the formula to separate areas. You can reposition a PivotTable using PivotTable Analyze > Move PivotTable.
- If a non-PivotTable cell is genuinely blocking the output, confirm that its contents can be removed before clearing it. If merged cells are responsible, unmerge them only if doing so is appropriate for the worksheet layout.
- Refresh or recalculate as needed, then confirm that the formula returns the complete result and that the PivotTable still displays its intended data.
On Mac, the basic diagnosis is the same: select the error cell and inspect Excel’s error message and intended output area. Ribbon names and the availability or placement of individual warning options can vary by Excel version. In Excel for the web, some commands and details may also differ from the Windows desktop app; if a specific move or diagnostic option is unavailable, open the workbook in desktop Excel rather than altering report cells blindly.
Choose a better layout for PivotTables and formulas
The most reliable approach is to give PivotTables and dynamic-array formulas separate, predictable spaces. A PivotTable can grow when refreshed, especially if new categories or items appear. A formula placed immediately beside it may work today but spill into the report after that change. Leave a buffer of empty columns or rows, or place supporting formulas on a separate worksheet.
If the formula needs to analyze PivotTable results, reference the report only when its layout is suitable for that purpose. Ordinary cell references may point to different items after a PivotTable expands or changes its arrangement. For a report that must preserve a stable relationship to PivotTable values, consider whether a PivotTable-specific reference or a calculation based on the original source data better matches the task. The right choice depends on whether the result should follow the displayed report or the underlying records.
For a calculation that belongs inside the PivotTable, investigate its built-in value summaries, show-values-as options, or calculated fields rather than placing an array formula over the report. These options do not replace every worksheet formula, and their suitability depends on the data model and the calculation. The key distinction is that PivotTable features calculate within the report; a dynamic-array formula spills into worksheet cells outside the report.
Common misconceptions and related errors
#SPILL! does not mean that the PivotTable source data is invalid or that refreshing will always fix the problem. Refreshing may actually make a collision appear if the report grows. Likewise, deleting a PivotTable obstruction is not a durable fix: a refresh can restore the report output, and clearing it may remove information a reader needs.
Not every formula next to a PivotTable is a dynamic-array formula. A regular formula that returns a single value generally does not need a spill range, so a #SPILL! message points to an array-returning formula or another dynamic-array situation. Other PivotTable issues, such as an unexpected subtotal, a missing source item, or a failed refresh, are separate problems and should not be diagnosed as spill errors just because they occur in the same worksheet.
Formatting, such as a cell’s fill color or number format, is not ordinarily an obstruction by itself. Conversely, a cell that looks blank can still contain content that blocks a spill. Check the formula bar or inspect the selected cells before removing anything. This matters particularly in workbooks where the PivotTable is refreshed or edited by other people.
Frequently Asked Questions
Can a PivotTable itself show a #SPILL! error?
A PivotTable does not normally produce #SPILL! as one of its own report values. The error usually belongs to a dynamic-array formula whose output range is blocked by the PivotTable, or to a formula placed where it cannot spill.
Why does my formula show #SPILL! when the cells look empty?
A cell can appear empty while containing a formula or other content, and merged cells can also prevent a spill. The intended output might also cross into PivotTable cells that you did not recognize as part of the report. Inspect the entire expected spill range.
Should I delete the PivotTable cells that are blocking the formula?
Usually not. Those cells may be managed report output and can reappear when the PivotTable refreshes. Move the formula or the PivotTable instead, unless you have confirmed that the report cells are no longer needed and can safely be changed.
Can I use a dynamic-array formula inside a PivotTable?
No, not as a formula that spills into neighboring PivotTable cells. Place the formula outside the PivotTable in a clear range, or use a PivotTable calculation feature if the calculation belongs in the report.
Will refreshing the PivotTable fix the spill error?
Not necessarily. A refresh does not automatically clear a formula’s obstruction, and an expanded PivotTable can create a new overlap. Check the formula’s required spill range after refreshing and keep the two output areas separate.
Related
- Excel Superscript Shortcut: Apply Superscript to Text
- Subscript vs Superscript in Excel: What’s the Difference?
- Subscript in Excel: Shortcut Key and How to Apply It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel X-Axis Text Instead of Numbers: How to Fix ItTo show text instead of numeric values along an Excel chart’s horizontal axis, use a category chart such…
- Excel Trendline in a Cell: Formulas and Practical MethodsYes. To calculate a linear trendline in a worksheet cell, use Excel’s TREND function: with known x-values in…
- Excel Yin Yang Dashboard: Build a Balanced ViewAn Excel yin yang dashboard is a custom workbook that uses the yin-yang idea of balance to present…
- Quadrant Chart in Google Sheets: How to Build OneGoogle Sheets does not have a dedicated quadrant-chart type, but you can make one with a scatter chart…
- Excel Trendline for a Specific Range: How to Set It UpExcel does not let you tell a trendline to use an arbitrary subset of points within a chart…
- Google Sheets Line Chart Maker: Create and Format a ChartGoogle Sheets has a built-in line chart maker: select your data, choose Insert > Chart, then select Line…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.