Others

How to Automatically Link Rows from One Excel Sheet to Another

Bushra ParveenBushra Parveen Oct 22, 2026 875 views

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.

How to Automatically Link Rows from One Excel Sheet to Another
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Open your destination sheet (Sheet 2) and click on the top-left cell where you want the linked data to start.

2
Enter the FILTER formula

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.

3
Apply and test the formula

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 the FILTER Dynamic Array Formula
Tip for Expanding Data: If your source data expands frequently, change the range references to entire columns (e.g., =FILTER(Sheet1!A:E, Sheet1!A:A<>"")) or format the source as a Table and use Structured References.
Seamless Data Management

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. 1. Download and install WPS Office: Get the free WPS Office suite from the official website and open your workbook in WPS Spreadsheet.
  2. 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. 3. Apply the dynamic linking formula: Enter the =FILTER() formula referencing your primary sheet to automatically pull and sync data in real-time.
  4. 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.
Fully compatible with Microsoft Excel (.xlsx) formats and dynamic formulas.Supports advanced array functions like FILTER, UNIQUE, and SORT to automatically link rows.Lightweight, fast, and completely free to download and use.Intuitive tabbed viewing interface to easily switch between multiple linked sheets.
QA img-9

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.