Power Query Problems

How to Automatically Populate Shared Reference Data Across Excel Worksheets

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to automatically copy shared reference data entered on a primary department worksheet into corresponding rows on other department worksheets within the same workbook.

Product: Microsoft Excel 365
Device & OS: not provided
Scenario: A multi-department workflow where entering a new row on Sheet 1 should automatically generate a row with the same reference information on Sheets 2 and 3, allowing department-specific data to be entered later.
Observed behavior: Standard cell referencing and co-authoring do not automatically create synchronized rows across multiple sheets for subsequent independent data entry.

Before you start

Ensure your Excel workbook is saved locally or on OneDrive, and verify that you have access to the 'Get & Transform Data' (Power Query) features in your version of Excel.

Solution 1Recommended

Use Power Query and Power Pivot to Synchronize and Merge Data

Leverage Excel 365's native Power Query functionality to automate the extraction and merging of reference data into distinct department views.

Power Query is the most robust method for this scenario. It allows you to designate a primary source table and automatically push that reference data to other worksheets. However, be mindful that any manual data entered directly next to a Power Query output table might misalign if the source data is sorted or deleted.

Format Source Data as a Table

Select the reference data on your main worksheet, navigate to the Insert tab, and click 'Table' (or press Ctrl+T). Name this table appropriately in the Table Design tab.

Load Table into Power Query

Go to the Data tab and select 'From Table/Range'. This will open the Power Query Editor with your source data loaded.

Transform and Merge Queries

If you have existing department tables, use the 'Merge Queries' function in the Home tab of the Power Query Editor to join them with the primary reference table based on a unique identifier column.

Output to Department Worksheets

Click 'Close & Load To...' and select 'Table'. Choose the specific department worksheet (e.g., Sheet 2 or Sheet 3) where you want the synchronized rows to appear.

Data Refresh Requirement: Power Query tables do not update in real-time as you type. You must go to the Data tab and click 'Refresh All' to see new rows appear on the department worksheets.

Solution 2

Consider a Custom Web Application for Complex Automation

If your workflow requires strict row-level synchronization coupled with independent manual data entry, standard spreadsheet functions may not suffice.

Easily Sync Reference Data Across Sheets with WPS Spreadsheet

You can efficiently link and synchronize reference data across multiple department worksheets using dynamic formulas and data consolidation features in WPS Office.

1. Open Your Workbook: Launch WPS Spreadsheet and navigate to the destination department worksheet where you want the reference data to appear.
2. Create a Dynamic Link: Select the starting cell (e.g., A2), type an equals sign (=), and then click the corresponding reference cell on the primary source worksheet.
3. Apply Array Handling: Wrap the reference in an IF formula to prevent zeros from appearing for blank rows: =IF(Sheet1!A2<>"", Sheet1!A2, "").
4. Populate Future Rows: Click the small square at the bottom-right of the cell (the fill handle) and drag it down to apply the synchronization formula to future rows.

Seamless compatibility with Microsoft Excel (.xlsx) formats and complex formulasLightweight architecture ensures fast performance when processing multi-sheet linksIntuitive Data tools make it easy to manage and consolidate multi-department workflows

Frequently Asked Questions

Can Excel co-authoring automatically sync new rows across different sheets?

No. While co-authoring allows multiple users to edit a workbook simultaneously, it does not automatically duplicate, generate, or synchronize newly created rows across different worksheets.

Why does manual data become misaligned next to Power Query tables?

When Power Query refreshes, it rebuilds the table from the source data. If the source data order changes (via sorting or deletion), the manual data entered in adjacent columns will remain in its static row, causing misalignment with the newly refreshed reference data.

When should I use a custom web app instead of Excel for sharing department data?

You should consider a custom web application or a relational database if your workflow requires two-way synchronization, strict data validation, or if multiple departments need to independently input data tied to shared reference records without risking row misalignment.

Excel & Data Problems