To fix a #SPILL! error near an Excel PivotTable, select the error cell, use its warning menu to find obstructing cells, and either clear those cells or move the formula so its results can spill into an empty area. In Microsoft 365 for Windows, the error usually belongs to a dynamic array formula next to the PivotTable—not to the PivotTable calculation itself. Before changing anything, save the workbook or make a copy, especially if the PivotTable is refreshed from important source data.
A spill error means Excel cannot place all the results of a formula into the cells it needs. A PivotTable can cause that problem when its displayed range occupies part of the spill area, or when a refresh makes the PivotTable grow into that area. The steps below help you identify the formula, protect the PivotTable, and choose a lasting fix.
What You’ll Need
- A copy of the workbook, or a saved version you can restore if a layout change affects a report.
- Microsoft Excel for Microsoft 365 on Windows for the exact ribbon paths below. The same general checks apply in Excel for Mac and Excel for the web, although tab names and some error-menu options can differ.
- Permission to edit the worksheet and, if necessary, refresh or move the PivotTable.
- If the PivotTable uses an external connection or shared data model, access to the source and permission to refresh it.
Step-by-Step
- Identify the actual error cell. Click the cell that displays #SPILL!, not just the nearby PivotTable. Read the formula in the formula bar. If it begins with a function that can return multiple results, such as FILTER, SORT, UNIQUE, or SEQUENCE, it is a dynamic array formula. Those functions are available in Microsoft 365 and Excel 2021 or later. A PivotTable value cell normally displays a PivotTable result rather than a dynamic array formula. If the formula bar shows a different formula, use the error description and formula to diagnose that specific cell.
- Read Excel’s error explanation. Select the #SPILL! cell and click the warning icon beside it, if shown. Excel may state why the array cannot spill, such as a blocked range or a merged cell. Choose Select Obstructing Cells if that option appears. Excel will highlight a cell or range that is in the way. If the warning icon is not visible, select the cell and inspect its error indicator or use the formula bar; menu options vary slightly by Excel build.
- Check the dotted spill-range outline. When you select a dynamic array formula cell, Excel may show a border indicating where its results are intended to appear. Compare that outline with the PivotTable’s visible area. It may also overlap cells that look blank but contain a space, an apostrophe, a formula returning an empty string, or other content. A cell does not have to look occupied to block a spill.
- Decide whether the blocking content can be removed. If an obstructing cell contains accidental text or an obsolete formula, confirm what it is before clearing it. Select only the confirmed obstruction and press Delete. Do not delete cells within the PivotTable simply to make room: PivotTable output is managed as a block, and changing a value in its body is not a safe way to edit the report. If the obstruction is a PivotTable cell, keep the PivotTable intact and move the formula or the PivotTable instead.
- Move the dynamic array formula to open worksheet space. The most reliable fix is often to put the formula in a blank area with enough rows and columns for its largest expected result. Select the formula cell, cut it with Ctrl+X, click the new starting cell, and paste with Ctrl+V. Verify that the formula now returns the complete list or array. Do not merely move it one cell if the PivotTable can expand into the new spill range after a refresh.
- Move the PivotTable if its current location is the problem. Click inside the PivotTable to display the contextual PivotTable Analyze tab. Choose PivotTable Analyze > Move PivotTable, select a suitable destination, and confirm. Leave a clear buffer around the PivotTable for extra fields, subtotals, and additional rows after refresh. If the workbook layout must remain fixed, moving the dynamic array formula may be safer than relocating the report.
- Check for merged cells and other obstructions. A merged cell anywhere in the intended spill range can prevent the results from appearing. Select the relevant area and use Home > Merge & Center to inspect or undo merging where appropriate; do not unmerge cells if that would damage the report’s intended layout. Also check for values, formulas, hidden rows or columns, and cells occupied by another dynamic array. Clear only content you recognize and are willing to remove.
- Refresh the PivotTable and test the layout. Once the formula and PivotTable no longer compete for cells, select the PivotTable and choose PivotTable Analyze > Refresh. For a workbook with several reports, use Data > Refresh All only if you intend to update all connections and PivotTables. Check whether refreshed data makes the PivotTable larger. If it does, increase the gap or relocate the formula so the next refresh will not recreate the collision.
- Confirm that the error is gone and results are complete. Check the first result cell and the full expected spill range. Compare the returned items or values with the source data, and verify that no results are hidden behind the PivotTable or cut off by the worksheet edge. Save the workbook after confirming the PivotTable still refreshes and the report remains readable.
Common Mistakes
- Assuming the PivotTable itself is broken. A nearby #SPILL! often comes from a separate formula trying to use the same cells as the PivotTable. Inspect the formula in the error cell before changing PivotTable fields or source data.
- Deleting the PivotTable’s displayed values. PivotTable results are generated from its source and layout. Clearing individual cells is not a dependable fix and may be blocked or undone when the report refreshes. Move the PivotTable or formula instead.
- Clearing a cell because it looks empty. A blank-looking formula, a space, or a hidden value can block an array. Use Excel’s obstruction selection when available, and examine the cell before pressing Delete.
- Fixing the current spill area but not allowing for growth. A PivotTable may gain rows or columns after a refresh, especially when new categories appear. Leave room for likely expansion or put the formula on a separate worksheet.
- Confusing an Excel Table with a PivotTable. A structured Excel Table and a PivotTable are different features. Dynamic array formulas cannot spill inside an Excel Table, while a PivotTable can obstruct spill cells when its displayed range overlaps them. Identify which object is involved before choosing a fix.
- Overwriting the formula without checking its purpose. Replacing a dynamic array formula with a fixed list may hide the error but leave the report out of date. Preserve the formula when it is meant to update automatically; change its location or the layout instead.
Tips
- Keep dynamic array formulas and PivotTables in separate sections or on separate worksheets. This makes the layout easier to understand and reduces collisions during refreshes.
- Leave several blank rows and columns between a PivotTable and any formula that can return a changing number of results. A single blank row may not be enough if the report grows.
- If you need a formula-based report based on PivotTable output, consider placing that formula in a separate area and checking it after every refresh. A PivotTable’s size can change as filters, fields, or source records change.
- Use the PivotTable’s own filters and field layout when you want to change which data the PivotTable displays. Use a dynamic array formula for a separate calculated list, rather than typing into the PivotTable body.
- In Excel for Mac, select the error cell and look for the same spill warning and obstruction details; contextual tabs and menu placement may differ from Windows. Excel for the web supports many dynamic arrays, but some diagnostic options can be more limited. If you cannot identify the obstruction in the web version, open the workbook in desktop Excel.
Troubleshooting
The warning menu does not show Select Obstructing Cells. That option may not appear for every spill cause or Excel build. Select the formula cell and look for the outlined spill range, then inspect the cells within it. Check for hidden rows and columns, merged cells, formulas returning blank text, and other spill formulas. If the range is large, temporarily move the formula to a clearly empty worksheet area to confirm whether the original layout is the cause.
The formula still shows #SPILL! after moving it. Make sure the new location has enough unobstructed cells in every direction the formula needs. Confirm that the formula is not inside an Excel Table, where dynamic arrays cannot spill. Also check whether a merged cell or another formula occupies part of the destination. A formula may return different numbers of rows as its source changes, so leave more room than the current result requires.
The error comes back after refreshing. The PivotTable may be expanding into the spill range again. Move the formula farther away, give the PivotTable more room, or use a separate worksheet for the formula results. Refresh once more and check the largest likely report size. If the PivotTable is refreshed by a connection, remember that refreshing can alter its dimensions even when you have not changed its field layout.
Excel will not let you move or edit the PivotTable. Check whether the worksheet is protected or the workbook is shared with restricted editing. If you have permission, use Review > Unprotect Sheet when that command is available, then use PivotTable Analyze > Move PivotTable. Do not remove protection on a workbook you do not own or have authority to change; ask its owner for an editable copy or a new destination.
The cell is not actually showing #SPILL!. If it shows #REF!, #VALUE!, or another error, the cause is different. Read the exact error and inspect the formula rather than applying spill fixes. If it shows a PivotTable refresh warning instead, check the PivotTable’s data source and connection, then refresh after addressing that separate issue.
Frequently Asked Questions
Can a PivotTable itself produce a #SPILL! error?
A PivotTable is not normally a dynamic array formula. In many cases, the error is from a separate formula beside or over the space the PivotTable uses. Select the error cell and inspect its formula to tell which object produced it.
Will refreshing the PivotTable fix the spill error?
Usually not by itself. Refreshing updates the PivotTable and can make the collision worse if the report grows into the spill range. First clear the confirmed obstruction or move one of the objects, then refresh to verify the layout holds.
Can I put a FILTER or UNIQUE formula next to a PivotTable?
Yes, if the formula’s entire expected spill range is clear and not part of an Excel Table. Leave room for the PivotTable and the formula results to grow. If the PivotTable changes size often, separate worksheets are more reliable.
Does a blank-looking cell block a spill?
Yes. A space, hidden value, formula returning an empty string, merged cell, or another array can block the result even when the cell appears blank. Use the error details where available and inspect the full outlined range.
Is the fix different in Excel for Mac or Excel for the web?
The underlying cause and solutions are the same: remove a genuine obstruction or change the layout. Ribbon names, contextual tab placement, and diagnostic options can differ. For full obstruction diagnostics, open the workbook in desktop Excel if the web version does not expose the needed option.
Can I stop the PivotTable from covering the formula after future refreshes?
Leave a generous gap, move the formula to a separate worksheet, or place the PivotTable where it can expand without reaching the formula. Test the workbook with a refresh, because a layout that works with today’s results may fail when new categories or records are added.
Related
- How to Extract a Filtered List in Excel
- How to Extract Data From a Picture in Excel
- Export Tab Names in Excel: Two Practical Ways to Save Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Workbook Keeps Freezing? Troubleshooting GuideIf an Excel workbook keeps freezing, first give it a minute to finish, then save a copy if…
- Excel Page Breaks: How to Insert, Remove, and Fix ThemAn Excel page break determines where worksheet content moves onto the next printed page. In Microsoft Excel for…
- Outlook Spell Check Not Working? Fix the Main CausesIf Outlook spell check is not working, first check whether you are using classic Outlook or the new…
- Excel Formula Error Handling: Fix and Prevent ErrorsIn Excel, the quickest way to handle a formula error is to wrap the calculation in IFERROR, which…
- Where to Find an Excel File After a Crash on WindowsAfter Excel crashes on Windows, reopen Excel and check the Document Recovery pane first; open the newest recovered…
- SUMIFS Not Working With Multiple Criteria? Common FixesWhen SUMIFS is not working with multiple criteria, first check that its arguments are in the right order…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.