How to Avoid Spill Range References in Excel Formulas
Question details
The user wants to understand why Excel adds the # spill operator in formulas that return dynamic arrays and how to avoid spill range references so workbooks remain compatible with older Excel versions.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is editing a workbook that contains dynamic-array formulas and needs to make it compatible with older Excel versions or avoid spilled range references such as =SUM(C86#).
- Observed behavior
- Excel automatically adds the # spill operator to reference the entire spill range created by dynamic-array formulas; older Excel versions may not support these formulas.
Open the workbook in WPS Office Spreadsheets and display formulas by clicking the Formulas tab or pressing Ctrl+` so you can quickly locate # spill references before editing them.
Replace a Spill Reference with a Fixed Range in WPS Office Spreadsheets
Use this method to remove an individual # spill reference such as =SUM(C86#) and replace it with a fixed range like =SUM(C86:Q86).
The # operator is not a setting; it is created automatically when a formula refers to the entire spill range. Changing the reference to a fixed range avoids the spill operator, but the source dynamic-array formula may still be unsupported in older Excel versions.
Double-click the Excel file to open it in WPS Office Spreadsheets, or click Menu > Open and select the workbook.
Click the cell with the formula, such as the cell containing =SUM(C86#), and view the formula in the formula bar.
In the formula bar, replace C86# with the actual range that the spill covers, for example =SUM(C86:Q86).
Press Enter to apply the change and confirm that the formula returns the expected total without a # spill reference.

Convert Dynamic-Array Formulas to Traditional Formulas for Older Excel Compatibility
Use this method when the workbook must be shared with users who use older Excel versions and you need the most reliable compatibility.
Fix Spill Range References in WPS Office Spreadsheets
Open your Excel workbook in WPS Office Spreadsheets, replace # spill references with fixed ranges or rewrite dynamic-array formulas as traditional formulas, and save the file as a compatible .xlsx workbook.
- 1. Download and open WPS Office: Download WPS Office from the official website, install it, and open the Excel workbook in WPS Office Spreadsheets.
- 2. Locate the spill reference formula: Click the cell containing the formula with the # operator, such as =SUM(C86#), and inspect it in the formula bar.
- 3. Replace it with a fixed range or traditional formula: Edit the formula to use a fixed range like =SUM(C86:Q86), or rewrite dynamic-array functions as traditional formulas.
- 4. Save and share the workbook: Press Ctrl+S to save the workbook as an Excel Workbook (*.xlsx) file and share it with users on older Excel versions.

Frequently Asked Questions
Why does Excel add a # operator to my formula?
Excel adds # automatically when you reference a spill range created by a dynamic-array formula. For example, if C86 spills to C86:Q86, using =SUM(C86#) tells Excel to total the entire spilled range.
Can I turn off spill range references in Excel?
There is no setting to disable # spill references while keeping dynamic-array behavior. You can manually replace references like C86# with fixed ranges such as C86:Q86.
Will replacing C86# with C86:Q86 make my workbook compatible with older Excel versions?
It removes the spill reference, but if the workbook still contains dynamic-array formulas such as FILTER, SORT, or UNIQUE, older Excel versions may not recalculate them. Replace those functions with traditional formulas for full compatibility.
Does WPS Office support Excel spill range references?
WPS Office Spreadsheets can open and edit .xlsx workbooks and formulas, and you can replace # spill references or dynamic-array formulas before sharing the file with other Excel users.
How do I find all formulas that use # spill references?
Display formulas with Ctrl+` or use the Formulas tab in WPS Office Spreadsheets, then search for # references in the formula text and check each one manually.




