How to Automatically Link Rows from One Excel Sheet to Another
Question details
The user needs to automatically synchronize and link rows of data from a primary worksheet to a secondary one while accommodating newly inserted rows and project-specific data on the second sheet.

- Product
- Excel / Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining matching data across multiple worksheets where one sheet acts as the source and another inherits this data alongside its own unique rows.
- Observed behavior
- Simple cell references fail to keep rows properly aligned when new rows are inserted in the source sheet. The goal is to have changes and newly inserted rows automatically populate in the destination sheet in the correct order.
Ensure your source dataset does not contain completely empty rows in the middle of your data, as this can interrupt dynamic array formulas and table queries.
Use the FILTER Dynamic Array Formula
The most efficient way to link data live and automatically expand rows as source data changes without mixing up empty rows.
Dynamic array functions like FILTER automatically spill their results into adjacent cells. This allows you to pull all non-blank rows from a source sheet, and it will dynamically adjust as you add or remove rows.
Open your destination sheet (Sheet 2) and click on the top-left cell where you want the linked data to start.
Type the formula =FILTER(Sheet1!A1:E100, Sheet1!A1:A100<>"") into the formula bar. Replace 'Sheet1!A1:E100' with your actual source data range, and 'Sheet1!A1:A100' with the column you want to check for non-blank values.
Press Enter. The data from Sheet 1 will instantly populate across the cells. Try inserting a new row in Sheet 1 to verify that Sheet 2 updates automatically.

Use Power Query to Sync and Combine Sheet Data
Ideal for maintaining strict row order, handling complex data merging between sheets, and supporting extra project-specific columns.
Easily Link and Manage Spreadsheet Data with WPS Office
WPS Spreadsheet offers powerful functions, including dynamic array formulas and advanced data queries, to keep your worksheets perfectly synced. Easily link rows between sheets while maintaining formatting and structure.
- 1. Download and install WPS Office: Get the free WPS Office suite from the official website and open your workbook in WPS Spreadsheet.
- 2. Navigate to the destination sheet: Switch to your secondary sheet and click the starting cell where you want the linked data to appear.
- 3. Apply the dynamic linking formula: Enter the =FILTER() formula referencing your primary sheet to automatically pull and sync data in real-time.
- 4. Save in standard formats: Save your file as a standard .xlsx document to ensure the linked rows work perfectly for any other users you share the file with.

Frequently Asked Questions
Why do simple cell references like =Sheet1!A1 mess up when I insert new rows?
Simple cell references point to fixed cell addresses. When a new row is inserted in the source sheet, Excel pushes the old data down, but the destination sheet's formula may still reference the old empty address or shift out of alignment. Dynamic array formulas or Power Query solve this by evaluating the actual data range dynamically.
Will the FILTER function automatically copy the cell colors and formatting from the source sheet?
No, formula-based approaches like FILTER only extract the data values. You will need to manually apply the desired cell formatting (such as date formats, background colors, or borders) to the destination sheet.
How can I ensure my formulas capture new data added to the very bottom of the source sheet?
The most reliable method is to format your source dataset as an Excel Table (by pressing Ctrl+T). Then, update your formulas to use the Table's name (e.g., =FILTER(Table1, Table1[Column1]<>"")) instead of fixed ranges like A1:E100. As the table expands, the formula will automatically include the new rows.




