Formula Errors

How to Avoid Spill Range References in Excel Formulas

Steven ParkerSteven Parker Nov 13, 2026 869 views

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.

How to Avoid Spill Range References in Excel Formulas
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the workbook in WPS Office Spreadsheets

Double-click the Excel file to open it in WPS Office Spreadsheets, or click Menu > Open and select the workbook.

2
Select the cell containing the spill reference

Click the cell with the formula, such as the cell containing =SUM(C86#), and view the formula in the formula bar.

3
Edit the formula with a fixed range

In the formula bar, replace C86# with the actual range that the spill covers, for example =SUM(C86:Q86).

4
Press Enter and verify the result

Press Enter to apply the change and confirm that the formula returns the expected total without a # spill reference.

Replace a Spill Reference with a Fixed Range in WPS Office Spreadsheets
Compatibility note: This change removes the # operator from the formula, but the underlying dynamic-array formula can still be unsupported in older Excel versions.
WPS Office Spreadsheets

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. 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. 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. 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. 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.
Removes # spill references without losing your fixed-range formulasMaintains .xlsx compatibility with Microsoft Excel for easy sharingFree alternative to Microsoft 365 with a familiar spreadsheet interfaceLightweight installation and fast performance for everyday spreadsheet tasks
microsoft office alternative - wps office

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.