Power Query Problems

Fix Excel Linked Table Not Adding New Rows to Another Workbook

Camila Milosovich Oct 10, 2026 870 views

Question details

The user needs a linked Excel table in a destination workbook to automatically expand and include new rows added to the source workbook table.

Fix Excel Linked Table Not Adding New Rows to Another Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating a destination workbook that references data from a growing source table.
Observed behavior: Existing cell values update successfully through copy-and-paste links, but newly added rows in the source table fail to appear automatically in the destination file.

Before you start

Ensure both your source and destination workbooks are saved in a trusted location. If you are using standard cell references, note that both workbooks must generally remain open for dynamic table expansions to work properly.

Solution 1Recommended

Use Power Query to Import and Refresh Data

Power Query creates a robust connection to the source file that automatically includes newly added rows upon refresh, even if the source file is closed.

Power Query is the most reliable method for linking tables across workbooks because it connects directly to the table object rather than a static range of cells. This means it inherently knows when the table boundaries expand.

Get Data from Workbook

Open the destination workbook, go to the 'Data' tab on the ribbon, click 'Get Data', select 'From File', and then choose 'From Workbook'.

Select the Source File

Locate and select your source Excel workbook from your file directory, then click 'Import'.

Load the Target Table

In the Navigator window, select the specific table you want to link from the source file. Click 'Load' to bring the data into your current workbook.

Refresh to Show New Rows

Whenever new rows are added to the source table, return to your destination workbook, right-click anywhere inside the imported table, and select 'Refresh' to update the data automatically.

Use Power Query to Import and Refresh Data

Automatic Range Adjustment: Power Query automatically adjusts the row count during the refresh process, ensuring all new data is captured without manual range adjustments.

Solution 2

Use a Direct Table Reference

A quick method using structural references to mirror the source table exactly, though it requires both workbooks to be open to update newly added rows.

Easily Link and Manage Worksheet Data with WPS Spreadsheet

WPS Spreadsheet offers intuitive tools to reference data across multiple workbooks. You can easily link tables and automatically update values when your source data changes, all within a lightweight and highly compatible interface.

1. Open Both Workbooks: Launch WPS Spreadsheet and open both your source workbook and the destination workbook.
2. Start Your Connection: In the destination workbook, type '=' in the desired starting cell to begin referencing.
3. Select Source Data: Switch to the source workbook window and highlight the entire table range you want to mirror.
4. Finalize Connection: Press Enter to establish the connection, which will dynamically update when the source file is modified with both files open.

Fully compatible with Microsoft Excel (.xlsx) file formats and table structures.Easily link cell ranges and tables across multiple workbooks to keep data synchronized.Lightweight software that processes large datasets and dynamic references quickly.Built-in tools for data validation, structural referencing, and advanced formulas.

Frequently Asked Questions

Why do regular cell links not expand when I add rows to the source table?

Regular copy-and-paste links typically use absolute range references (like $A$1:$D$10). When you add row 11 to the source, the destination file still only looks at rows 1 to 10. Using Power Query or structural table references solves this limitation.

Do both Excel workbooks need to be open for linked data to update?

If you use basic formulas or direct structural table references, both workbooks usually need to be open to process dynamic array expansions correctly. If you use Power Query, the source workbook can remain closed.

How do I force Power Query to update after I add new rows?

Navigate to the Data tab and click 'Refresh All', or simply right-click anywhere inside the Power Query table in your destination workbook and select 'Refresh' from the context menu.

Will this work if my source data is a standard range instead of a formatted Table?

While Power Query can import standard ranges, it is highly recommended to convert your source data into an official Excel Table by pressing Ctrl+T. This ensures the reference boundary dynamically expands when new rows are added.

Excel & Data Problems