Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsA #SPILL! error means Excel calculated more than one result but cannot place the results in the cells it needs. Select the error cell: Excel outlines the intended spill range with a dashed border. Open the error menu, choose Select Obstructing Cells, and clear or move whatever is in that range. Dynamic-array formulas normally recalculate and fill the range as soon as the obstruction is removed.
What the #SPILL! error means
Modern Excel formulas can return an array rather than one value. The formula is entered in the top-left cell, and Excel writes the remaining values into neighboring cells. This automatic expansion is called spilling. Only the top-left cell contains the editable formula; the other cells display the spilled results.
As an Amazon Associate I earn from qualifying purchases.
Microsoft describes the condition this way: “Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells.” A #SPILL! error appears when Excel knows the result needs several cells but cannot claim the complete output area. The same message can appear when a PivotTable needs to expand but its destination area is blocked.
Use Excel’s outline to find the obstruction
- Select the cell displaying
#SPILL!. - Look for Excel’s dashed outline. It marks the cells the formula is trying to use.
- Open the error menu beside the cell and choose Select Obstructing Cells.
- Inspect every selected cell and the outlined area for text, numbers, formulas, objects, merged cells, or a table boundary.
- Delete or relocate the obstruction, or move the formula to a clear area.
Do not clear the formula’s top-left cell. The goal is to make the entire intended output range available. If a value is important, copy it elsewhere before deleting it. When the range is clear, Excel usually spills the result without any further change.
#1 Best Overall
- Classic Office Apps | Includes classic desktop versions of Word, Excel, PowerPoint, and OneNote for creating documents, spreadsheets, and presentations with ease.
- Install on a Single Device | Install classic desktop Office Apps for use on a single Windows laptop, Windows desktop, MacBook, or iMac.
- Ideal for One Person | With a one-time purchase of Microsoft Office 2024, you can create, organize, and get things done.
- Consider Upgrading to Microsoft 365 | Get premium benefits with a Microsoft 365 subscription, including ongoing updates, advanced security, and access to premium versions of Word, Excel, PowerPoint, Outlook, and more, plus 1TB cloud storage per person and multi-device support for Windows, Mac, iPhone, iPad, and Android.
Cause 1: A nonblank cell is in the spill range
Any existing content can block a dynamic array. A typed label, a number, another formula, or an object positioned in the outlined area is enough to stop expansion. A cell that appears visually empty can still contain a formula returning an empty string, so inspect the formula bar rather than relying only on what is visible.
Fix a single blocking cell
- Use Select Obstructing Cells from the error menu.
- Decide whether each selected entry should be deleted or moved.
- Clear the cell contents, or cut and paste the content outside the dashed outline.
- Return to the formula cell and confirm that the complete result now appears.
If the obstruction is part of a report layout, moving the formula is often safer than deleting report content. Leave enough blank rows and columns for the largest result the formula can reasonably return.
Objects can block a spill too
Shapes, controls, pictures, and other worksheet objects occupying the output area can prevent expansion even when the cells themselves are blank. Move the object away from the outlined range, then recalculate or edit the formula if Excel does not refresh immediately.
Cause 2: The formula is inside an Excel table
Spilled-array formulas are not supported inside an Excel table’s body. A table can be an excellent source for a dynamic-array formula, but the formula that produces the spill must be placed outside the table body.
Rank #2
![Microsoft Office Home & Business 2024 | Classic Desktop Apps: Word, Excel, PowerPoint, Outlook and OneNote | One-Time Purchase for 1 PC/MAC | Instant Download [PC/Mac Online Code]](https://m.media-amazon.com/images/I/41H8B5iqO4L._SL160_.jpg)
- [Ideal for One Person] — With a one-time purchase of Microsoft Office Home & Business 2024, you can create, organize, and get things done.
- [Classic Office Apps] — Includes Word, Excel, PowerPoint, Outlook and OneNote.
- [Desktop Only & Customer Support] — To install and use on one PC or Mac, on desktop only. Microsoft 365 has your back with readily available technical support through chat or phone.
Move the formula outside the table
- Identify the table’s boundary and choose a clear worksheet area outside its body.
- Move or re-enter the dynamic-array formula there.
- Use the table’s structured references as the source if appropriate.
- Leave an unobstructed area for the result to expand.
This preserves the table for sorting, filtering, and source data while putting the spill in a range that supports dynamic arrays.
Convert the table to a normal range when necessary
If the output must physically occupy the former table area, convert the table to a normal range with Excel’s table conversion command, then place the formula in the resulting range. You lose table behavior such as automatic structured-reference expansion, so use this option only when that trade-off is acceptable. For a workbook shared with people using older Excel releases, a conventional range with formulas copied down may be more compatible.
Cause 3: Merged cells intersect the output
Dynamic arrays cannot spill into merged cells. A merged heading or formatting block anywhere in the outlined range is therefore a hard obstruction.
- Select the merged area that overlaps the dashed outline.
- Use Excel’s Unmerge Cells control.
- Reapply alignment or borders without merging, if the layout requires them.
- Alternatively, move the formula to an unmerged section of the worksheet.
Unmerging may change the appearance of a report, so moving the formula can be the least disruptive fix when the merged layout is intentional.
Rank #3
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- 1 TB Secure Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Easy Digital Download with Microsoft Account | Product delivered electronically for quick setup. Sign in with your Microsoft account, redeem your code, and download your apps instantly to your Windows, Mac, iPhone, iPad, and Android devices.
Cause 4: A full-column reference requests too many results
A lookup or other array formula can accidentally ask Excel to evaluate an entire column. Microsoft’s example describes a lookup that requests 1,048,576 values, the number of rows in a worksheet column. The spill then reaches the bottom edge of the worksheet and cannot fit.
Use a bounded range
Replace an open-ended reference with the rows that actually contain data. For example, use a range such as A2:A7 when those are the relevant records, rather than asking Excel to process the entire column. A bounded dynamic range still resizes automatically within the range you specify, but it does not attempt to fill a million rows.
Copy a single-row lookup down
If you need one result per record, use a single-cell lookup value and copy the formula down. A pattern such as =VLOOKUP(A2,A2:C7,2,FALSE) returns one value for the row containing A2; fill the formula through the required records instead of asking one formula to return a full-column array.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Restore implicit intersection with @
When the intended behavior is one value from the current row, the @ implicit-intersection operator can prevent an unwanted dynamic spill. Microsoft gives this example: =VLOOKUP(@A:A,A:C,2,FALSE). The operator tells Excel to use the value relevant to the current row rather than returning a result for every row in the column.
Rank #4
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- Up to 2 TB Shared Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Share Your Family Subscription | You can share all of your subscription benefits with up to 6 people for use across all their devices.
Cause 5: Volatile sizing never stabilizes
Some functions recalculate to new values and can change how many results a formula needs between calculation passes. Microsoft specifically identifies RAND, RANDARRAY, and RANDBETWEEN as examples. If the requested output dimensions keep changing, Excel may report #SPILL! because it cannot settle on a stable spill range.
Make the output dimensions deterministic
- Replace volatile sizing logic with a stable range whose row and column counts are known.
- Separate random-value generation from the part of the formula that determines the output size.
- Use fixed limits when a random result is required, then generate values within those limits.
- Recalculate after editing and verify that the dashed outline remains the same size.
The important distinction is between a formula whose values change and a formula whose required dimensions change. Volatility is most problematic when it controls the number of cells Excel must reserve.
Cause 6: A PivotTable cannot expand
A PivotTable can also display #SPILL! when Excel needs to enlarge it. This may occur while inserting or moving the PivotTable, refreshing it, or changing fields, filters, or totals. The expansion area can be blocked by ordinary data, formulas, merged cells, worksheet objects, or the edge of the sheet.
Recommended Free Tools
Clear the PivotTable’s expansion area
- Select the cell showing the error and inspect the area Excel is trying to use.
- Look beyond the visible PivotTable for values, formulas, merged cells, or objects in the planned layout.
- Move or clear those items, taking care not to delete source data accidentally.
- If the PivotTable is too close to the bottom or right edge, move it to a larger open area.
- Refresh the PivotTable and repeat the field, filter, or totals change.
When a PivotTable is part of a dashboard, reserve a larger blank rectangle than the current view occupies. A later refresh can add rows or columns even if the original layout fitted perfectly.
Best Value
![SoftMaker Office Standard 2021 (5 users) for Windows, Mac and Linux [PC/Mac Download]](https://m.media-amazon.com/images/I/311keJjtTfL._SL160_.jpg)
- Alternative office suite: Word processor TextMaker, Spreadsheet program PlanMaker, Presentation software Presentations, Automation tool BasicMaker
- Licensed for 5 users / household or 1 user / organization, perpetual lifetime license for Windows, Mac and Linux
- User interface with modern ribbons or classical menus
- Compatible with all modern Microsoft Office documents including DOCX, XLSX, PPTX
- The complete office suite can be installed on a USB flash and used without installation
Cause 7: Excel version or linked-workbook limitations
Dynamic arrays were introduced for Microsoft 365 in September 2018. Current Channel subscriber support was released in January 2020. Older Excel versions may treat these formulas as legacy CSE (Ctrl+Shift+Enter) arrays, which have different resizing behavior and do not automatically provide the same spill workflow.
Dynamic-array formulas that reference another workbook are supported only while both workbooks are open. If the source workbook is closed, Excel can return #REF! instead of maintaining the linked spill. Open the source file before troubleshooting the worksheet layout, and verify that both workbooks use Excel editions that support dynamic arrays.
Sharing with older Excel installations
When a workbook must work across mixed versions, consider a single-cell formula copied down or a legacy array approach rather than relying on a spill. The choice depends on whether automatic resizing is essential. A copied formula requires more maintenance when records are added, but it avoids asking an older installation to interpret a dynamic result it does not support.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →A complete decision path for any #SPILL! error
- Start at the error cell. Select it and read the dashed outline.
- Use the built-in locator. Choose Select Obstructing Cells and inspect every selected item.
- Remove ordinary blockers. Clear or move values, formulas, objects, and merged cells.
- Check the formula location. If it is inside a table body, move it outside or convert the table to a normal range.
- Inspect references. Replace full-column inputs with bounded ranges, copy a one-row formula down, or add
@when implicit intersection is intended. - Check calculation stability. Look for
RAND,RANDARRAY, orRANDBETWEENcontrolling the output size. - Identify PivotTable behavior. If the error belongs to a PivotTable, inspect its planned expansion area after layout or refresh changes.
- Check workbook compatibility. Open linked source workbooks and confirm that the Excel versions involved support dynamic arrays.
Choosing the least disruptive fix
| Fix | Scope of change | Dynamic resizing | Table-body compatibility | Space requirement |
|---|---|---|---|---|
| Clear or move the obstruction | Localized worksheet change | Preserved | Does not make a table body support spills | Requires the outlined range to remain open |
| Use a bounded dynamic range | Formula rewrite | Preserved within the chosen bounds | Formula still belongs outside a table body | Requires room for the bounded result |
| Copy a single-cell formula down | Formula and fill strategy change | Automatic array expansion removed | More compatible with table layouts | Requires one formula row per needed result |
| Add @ implicit intersection | Small formula change | Single result instead of a dynamic spill | Useful when one row value is intended | Uses the current cell or row rather than a spill area |
Choose the localized fix when the formula is correct and the obstruction is accidental. Rewrite the range when a full-column reference is the underlying problem. Use copied formulas or @ when the workbook’s layout, table structure, or older Excel compatibility matters more than automatic resizing.
How to prevent future spill errors
- Place dynamic-array formulas in deliberately empty worksheet zones.
- Keep report titles, merged headings, shapes, and controls outside expected spill areas.
- Use bounded references when the data set has a known working range.
- Reserve expansion space beside and below PivotTables.
- Avoid putting spill formulas in table bodies; use tables as sources instead.
- Document whether a formula is expected to return one value or an array, especially when using
VLOOKUP. - Test linked-workbook formulas with both files open before distributing the workbook.
- For shared workbooks, decide explicitly whether all users have dynamic-array support.
What a successful repair looks like
After the fix, the top-left formula cell contains the formula, the neighboring cells contain the returned values, and the #SPILL! message is gone. Selecting the formula should show the full result area rather than an obstruction warning. If the error returns after a refresh or data change, compare the new dashed outline with the previous one: a larger result, a newly merged cell, a table boundary, or a PivotTable layout change usually identifies what changed.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
