How to Build a Formula from an Excel Spill Range
Question details
The user needs to reference an existing spilled array (like an XLOOKUP result) in an adjacent column so that the dependent formula automatically spills and resizes alongside the source data.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a dependent formula that scales automatically with a dynamic array spill range without the need to manually drag or copy the formula down.
- Observed behavior
- The primary formula spills correctly, but the adjacent dependent formula does not automatically spill, requiring manual copying whenever the data size changes.
Ensure that your source formula is successfully spilling its results without any #SPILL! errors, and identify the exact cell reference of the top-left cell in that spill range (e.g., cell B2).
Use the Spill Range Operator (#) to Create a Dependent Dynamic Formula
By appending a hashtag (#) to the starting cell reference, you can instruct your spreadsheet to reference the entire dynamic array. This allows the new formula to automatically spill down the adjacent column.
The spill range operator (#) is designed specifically for dynamic arrays. Instead of defining a static range (like B2:B10) or relying on relative references that must be copied down, the # symbol automatically adjusts to capture the exact footprint of the referenced spilled array.
Identify the top-left cell where your original dynamic array formula (such as an XLOOKUP or FILTER function) is entered. For example, assume this is cell B2.
Click on the adjacent cell where you want your new results to start spilling (e.g., cell C2) and type an equals sign (=) to begin your formula.
When referencing the source data as your lookup value or array, type B2# (or select cell B2 and manually append the # symbol). This tells the program to use the entire spilled array originating from B2.
Finish constructing your dependent formula (for example, =XLOOKUP(B2#, E:E, F:F)) and press Enter. The results will instantly spill down the column to match the exact length of the B2 array.
Easily Manage Dynamic Arrays and Spill Ranges with WPS Office
WPS Spreadsheet fully supports modern dynamic array functions and the spill operator (#). You can effortlessly build dependent formulas without manually dragging cells down, ensuring your data calculations are fully automated and error-free.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to analyze.
- 2. Input the primary array formula: Enter your initial dynamic array formula (such as an XLOOKUP) into your starting cell (e.g., B2) and hit Enter to let it spill.
- 3. Build the dependent formula: In the adjacent column (e.g., C2), create your next formula and reference the first cell's result followed by the # symbol (e.g., =XLOOKUP(B2#, ...)).
- 4. View the automatic spill: Press Enter to see your dependent results dynamically spill and align perfectly with the original array's length.
Frequently Asked Questions
Why does my dependent formula return a #SPILL! error?
A #SPILL! error occurs when the dynamic array formula does not have enough empty cells to display all of its results. Ensure there is no existing data, hidden text, or spaces blocking the path where the formula intends to spill.
Can I use the # spill operator with traditional functions like VLOOKUP?
Yes, you can pass a dynamic spill range (e.g., B2#) into the lookup_value argument of a traditional function like VLOOKUP or MATCH. This will force the traditional function to evaluate multiple criteria at once and return a dynamic, spilled array of results.
Will the # operator work in older versions of Excel or other spreadsheet software?
The spill operator (#) and dynamic arrays require modern spreadsheet engines, such as Microsoft 365, Excel 2021, or the latest versions of WPS Office. Older versions do not support dynamic spilling and may return an error or require legacy array entry (Ctrl+Shift+Enter).
How do I reference a spill range located on another worksheet?
You can reference a spill range on a different sheet by including the sheet name before the cell reference and appending the spill operator at the end. For example, it should look like this: =Sheet1!B2#.
