Function Problems

How to Use Dynamic Array Formulas to Auto-Fill Variable Rows in Excel

Chanuka Geekiyanage Sep 25, 2026 872 views

Question details

The user needs to set up dependent column formulas that automatically expand to match a dynamically changing number of rows generated by a SEQUENCE function, without using VBA macros or Excel Tables.

How to Use Dynamic Array Formulas to Fill Variable Rows in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic, web-compatible workbook where the primary column generates variable rows using SEQUENCE, and adjacent columns must scale their formulas automatically to match.
Observed behavior: The generated row count changes based on input values, but dependent formulas in the second, third, and fourth columns need a native method to cross-reference and expand alongside the primary sequence array.

Before you start

Ensure you are using a modern spreadsheet version that supports Dynamic Array functions (like SEQUENCE) and the spilled-range operator (#).

Solution 1Recommended

Use the Spilled-Range Operator (#) for Dependent Columns

Reference the entire dynamic array using the hashtag (#) operator to make your dependent formulas spill automatically alongside the original sequence.

The spilled-range operator is a built-in feature for dynamic arrays. By adding a hashtag (#) immediately after the cell reference of a spilled array's top-left cell, Excel knows to apply the formula to every row generated by that array.

Locate the primary dynamic array

Identify the cell containing your original dynamic array formula, such as the SEQUENCE function located in cell B9.

Enter the dependent formula

Click the first cell of the adjacent column where you want the dependent formula to start. Type your formula and append the `#` symbol to the primary cell reference (e.g., `B9#`).

Apply advanced calculations

For complex calculations across the variable range, construct the formula around the spilled reference. For example, input `=$D$4*SIN($D$3*B9#+PI()/2*$D$2)`.

Execute to spill the formula

Press the Enter key. The formula will automatically calculate and spill down the column, perfectly matching the exact number of rows output by the primary array.

Use the Spilled-Range Operator (#) for Dependent Columns

Web Compatibility Confirmed: Using the spilled-range operator eliminates the need for VBA macros and Excel Tables, ensuring your workbook remains fully functional when shared or used on the web.

Solution 2

Combine SCAN and LAMBDA for Sequential Cross-References

Use advanced iteration functions like SCAN when your dynamic array formulas depend on the results of previous rows within the same spilled range.

Advanced Spreadsheet Features

Easily Manage Dynamic Arrays with WPS Spreadsheet

WPS Office Spreadsheet fully supports dynamic array functions like SEQUENCE and the spilled-range operator, allowing you to build complex, scalable, and web-friendly workbooks completely macro-free.

1. Open WPS Spreadsheet: Launch WPS Office and create or open your workbook in WPS Spreadsheet.
2. Generate the dynamic rows: Enter your SEQUENCE formula in the starting cell (e.g., B9) to generate the initial variable rows.
3. Reference the spilled range: In your dependent column, type the formula utilizing the `#` operator (e.g., `B9#`) to reference the entire sequence.
4. Auto-fill the rows: Press Enter to instantly execute the calculation. The results will automatically spill down the column to match the generated data array.

Seamless compatibility with Microsoft Excel dynamic array formulas and functions.Lightweight application that runs smoothly even with large, dynamically shifting datasets.Completely free to use with a familiar, easy-to-navigate interface for quick adoption.

Frequently Asked Questions

Why does my dynamic array formula return a #SPILL! error?

A #SPILL! error occurs when the formula attempts to expand into adjacent cells that already contain data, text, or spaces. Clear any blocking content in the spill zone to allow the formula to expand properly.

Do I need to format my data as a Table to use spilled ranges?

No. One of the main advantages of dynamic arrays is that they automatically expand and shrink without the need for traditional Excel Tables or VBA macros.

Can I reference a spilled array located in a different worksheet?

Yes, you can reference a spilled array across different worksheets by including the sheet name followed by the top-left cell reference and the hashtag. For example: `=Sheet1!B9#`.

Are dynamic array formulas backwards compatible with older spreadsheet versions?

Dynamic array functions (like SEQUENCE) and the spilled-range operator (#) are not supported in older, non-subscription versions of Excel. They require modern environments like Microsoft 365, Excel 2021, or current versions of WPS Office.

Excel & Data Problems