Function Problems

How to Automatically Expand Excel Tables from Spill Formulas

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs Excel tables to automatically add rows when a dynamic array or spill formula located on another worksheet expands.

How to Automatically Expand Excel Tables from Spill Formulas

Product: Excel
Device & OS: not provided
Scenario: Working with dynamic arrays and spill formulas across different worksheets while trying to maintain structural Excel table formatting for the output.
Observed behavior: Excel tables do not automatically resize or add new rows when the referenced spill range expands, and dynamic arrays cannot spill inside formatted tables.

Before you start

Understand that Excel natively prevents dynamic array formulas from spilling inside structured Tables. You must choose whether maintaining the structured Table format or utilizing the automatic dynamic spill functionality is more critical for your current spreadsheet.

Solution 1Recommended

Remove Table Formatting and Use Spill Range References

Since spill formulas cannot exist inside an Excel table, the best native workaround is to convert the table back to a standard range and use the # operator to reference the dynamic array directly.

Dynamic array formulas are designed to spill into adjacent empty cells, but structured Excel Tables require strict definitions for rows and columns. Therefore, the two features are fundamentally incompatible.

By converting your table to a normal range, your formulas can freely spill as the source data expands.

Convert the table to a range

Right-click any cell inside your existing Excel Table. Hover over 'Table' in the context menu, and select 'Convert to Range'. Confirm the prompt if asked.

Enter the dynamic array formula

Select the top-left cell of your destination range and enter your dynamic array formula (e.g., FILTER, UNIQUE, or SORT).

Reference the spill range

To reference this dynamic array in other formulas, select the first cell of the spill range and append the hashtag (#) operator at the end (for example, =SUM(A2#)). This ensures the reference automatically adjusts as the source range expands or contracts.

Remove Table Formatting and Use Spill Range References

Dynamic References: The # operator is the most efficient way to capture dynamic data without relying on structured tables, ensuring your downstream calculations never miss new rows.

Solution 2

Use VBA to Automatically Resize the Table

If you strictly require the output to remain in a formatted Excel Table, you can use a VBA macro to detect the spill range size and resize the table accordingly.

Free Microsoft Office alternative

Manage Complex Spreadsheet Data Smoothly with WPS Office

While Excel tables limit the use of spill formulas, WPS Office provides a highly compatible and lightweight alternative for your complex spreadsheet tasks. Enjoy seamless formula processing, robust data management, and extensive format compatibility without the heavy subscription fees.

1. Download the software: Visit the official WPS Office website and download the free installation package.
2. Install WPS Office: Run the installer and follow the simple on-screen instructions to set up the suite on your device.
3. Open your spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx files to continue analyzing your dynamic arrays and tables immediately.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Lightweight architecture that loads large data sets and complex arrays quicklyFamiliar spreadsheet interface requiring zero learning curveFree to use for everyday advanced formula calculations and data analysis

Frequently Asked Questions

Why do I get a #SPILL! error when using a dynamic array in an Excel table?

Excel natively blocks dynamic array formulas from spilling inside structured Tables to prevent data overlap and structural conflicts. If a formula needs to spill into multiple cells within a Table, it will return a #SPILL! error. To fix this, you must convert the table to a normal range.

Can Office Scripts be used instead of VBA to resize tables?

Yes. If you are using Excel for the Web or Microsoft 365, you can write an Office Script using TypeScript to detect the size of a spill range and dynamically update the boundaries of your Excel Table. This is often preferred over VBA for cloud-based automation.

How do I reference a dynamic spill array from a different worksheet?

You can reference a dynamic array on another sheet by typing the sheet name, an exclamation mark, the starting cell reference, and the # symbol. For example: =Sheet1!A2#. This will pull the entire dynamically sized array into your current formula.

Excel & Data Problems