Function Problems

How to Use the Spill Operator (#) in Excel Dynamic Arrays

WPS Editor Sep 27, 2026 869 views

Question details

The user needs to understand how to correctly apply the spill operator (#) in spreadsheet software to reference dynamic-array formulas and troubleshoot why it might not be working in their specific environment.

How to Use the Spill Operator (#) in Excel Dynamic Arrays

Product: Excel
Device & OS: not provided
Scenario: Referencing a dynamic range of spilled data generated by another formula without having to select the entire range manually.
Observed behavior: The spill operator works in some examples but fails in the user's environment, often due to software version limitations, referencing non-spill ranges, or trying to apply it to static values.

Before you start

Ensure you are using a modern version of your spreadsheet software (such as Microsoft 365, Excel 2021, or the latest WPS Office) that fully supports dynamic arrays, as older versions will not recognize the spill operator and will return an error.

Solution 1Recommended

Apply the Spill Operator (#) to Reference a Dynamic Array

Use the hash symbol to automatically reference the entire variable output of a dynamic array formula in subsequent calculations.

The spill operator (#) is designed to make referencing dynamic arrays effortless. Instead of hardcoding a range like C2:C10, which might break if the array expands or shrinks, you simply append the hash symbol to the top-left cell of the spilled array.

Enter a dynamic array formula

Select an empty cell (for example, C2) and enter a dynamic array formula, such as =UNIQUE(A1:A100). Press Enter, and allow the results to spill down into the adjacent cells.

Select a calculation cell

Click on a different empty cell where you want to perform a summary calculation on the newly generated spilled data.

Input the spill operator reference

Type a function like =SUM(C2#) or =COUNTA(C2#). The # operator instructs the spreadsheet to dynamically include every cell in the spilled range originating from C2.

Execute the formula

Press Enter to calculate the result. As the original source data changes and the spilled array expands or contracts, your summary formula will automatically update without any manual adjustments.

Apply the Spill Operator (#) to Reference a Dynamic Array

Operator Limitations: The spill operator will only work if the referenced cell (e.g., C2) actually contains a dynamic-array formula. If C2 contains a static value or a standard, non-spilling formula, the # reference will fail.

Efficient Data Analysis with WPS Office

Use Dynamic Arrays and the Spill Operator in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays and the spill operator (#), allowing you to build flexible, automated reports. It provides a lightweight, highly compatible alternative to Microsoft Office for complex data analysis workflows.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Generate a dynamic array: In an empty cell, type a modern dynamic array function like =UNIQUE(A2:A50) to generate a spilled range.
3. Reference with the spill operator: In another cell, type a summary function like =COUNTA(C2#) to instantly calculate the total items in the spilled range.

Fully compatible with Microsoft Excel formulas, including dynamic arrays like UNIQUE, FILTER, and SORT.Automatically updates calculation ranges using the # spill operator.Lightweight installation and fast processing for large datasets.Free to use with a familiar, easy-to-navigate user interface.

Frequently Asked Questions

Why does the spill operator return an error in my spreadsheet?

The spill operator will fail if it references a cell that contains a static value instead of a dynamic array formula. It will also return a #NAME? or #REF! error if you are using an older version of Excel (like Excel 2019 or older) that does not support dynamic arrays natively.

Can I use the spill operator with standard functions like VLOOKUP?

Yes, you can use the spill operator inside most standard functions that accept a cell range as an argument. For instance, you can use it in SUM, COUNT, AVERAGE, or as the lookup array in a VLOOKUP formula simply by appending the hash symbol (#) to the source cell reference.

What happens to the spill reference if the dynamic array grows or shrinks?

The spill operator (#) is completely dynamic. If your source data changes and the spilled array expands to more rows or contracts to fewer, any formula using the spill reference (e.g., C2#) will automatically update its calculation range to match the new dimensions of the spilled array.

Excel & Data Problems