Automatically Expand an Excel Table from Data on Another Sheet in WPS Office
Question details
The user needs to keep a separate table synchronized with a growing master dataset on another sheet, so new rows are included automatically without manually resizing the output range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A master dataset on one sheet grows over time, and the user wants a separate table on another sheet to update automatically with the new rows.
- Observed behavior
- A normal Excel Table cannot automatically import and expand from an unrelated range merely by using a volatile named range; it requires a structured source table, Power Query, dynamic-array formulas, or VBA.
Confirm that the master data has a single header row, unique column headers, and no completely blank rows or columns inside the intended table area, because structured Excel Tables and Power Query require a consistent tabular layout.
Use Power Query to Load the Master Table and Refresh the Output
Use this when you need a separate, refreshable output table that keeps its own formatting and expands with new rows from a growing master dataset.
Power Query treats the master Excel Table as a structured source. After you load it to another sheet, refreshing the query imports all current rows and includes any new rows added to the source table.
Open WPS Spreadsheets, select the master data range, and press Ctrl+T or go to Insert > Table to create a structured Excel Table with headers.
Click any cell inside the master table, go to Data > From Table/Range, and open the Power Query editor.
In the Power Query editor, choose Close & Load, select Table and New worksheet, then confirm to create the separate output table.
After new rows are appended to the master table, right-click the output query table and choose Refresh, or click Data > Refresh All.
Use a Dynamic Array FILTER Formula to Spill Matching Rows
Use this when you want a formula-based output that updates immediately without pressing Refresh, and you do not need a physical Excel Table object.
Keep a Separate WPS Spreadsheet Table in Sync from Another Sheet
Use WPS Office to turn the master range into a structured Excel Table and create a refreshable Power Query connection so the separate table expands automatically as new rows are added.
- 1. Install and open WPS Office: Download WPS Office from the official website, install it, and open the workbook containing the master data.
- 2. Convert the master range to a table: In WPS Spreadsheets, select the master range, press Ctrl+T, and confirm the table has headers.
- 3. Create a Power Query connection: Click inside the table, go to Data > From Table/Range, and load the query to a new worksheet as a Table.
- 4. Refresh when new rows are added: Append rows to the master table, then click the output table and choose Data > Refresh All to expand it automatically.
Frequently Asked Questions
Why does a named range not automatically expand when new rows are added?
A normal named range using OFFSET or volatile formulas is not recognized as a structured source by the separate table, so it cannot trigger automatic expansion. Convert the source to an Excel Table with Ctrl+T instead.
How do I refresh the separate table after adding rows to the master sheet?
Select the output query table and click Data > Refresh All, or right-click the query table and choose Refresh.
Can I use a formula instead of Power Query to expand the output table?
Yes, if your WPS version supports dynamic arrays, enter =FILTER(MasterTable,MasterTable[ID]<>"") on the output sheet. Clear the spill area so new rows can appear automatically.
What should I check if the output table does not update?
Confirm that the master data is a real Excel Table, the query references that table, and no blank rows or columns are inside the table.
Is the output table compatible with Microsoft Excel?
Yes, WPS Office saves .xlsx workbooks with query tables and formulas that remain compatible when opened in Microsoft Excel.
