Power Query Problems

Keep Employee Reports Linked to a Master Excel Table

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Create employee reports that stay linked to one master table.

Keep Employee Reports Linked to a Master Excel Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
An employee master table supplies information to multiple reports or related tables.
Observed behavior
Employee information is maintained in one master table and retrieved by reports using unique IDs, with active employees available for filtering.
Before you start

Give each employee a unique, consistent ID and add a Status column to the master table. Mark former employees as Inactive rather than deleting their records, and make sure related tables use the same IDs.

Solution 1Recommended

Build employee reports from the master table with Power Query

Use Power Query when you want refreshable reports based on the same employee list.

1
Create the master table

Select the employee data, choose Insert > Table, and give the table a name such as Employees on the Table Design tab.

2
Load it into Power Query

Click a cell in Employees and select Data > From Table/Range. In Power Query, check that the ID and Status columns contain the expected values.

3
Filter the report

Open the Status column filter and select Active. Keep the columns needed for the report, then choose Home > Close & Load to place the result on a worksheet.

4
Create other reports

In Data > Queries & Connections, right-click the query and choose Reference. Adjust the columns or filters for each report and use Close & Load to load it.

5
Refresh after changes

Update the Employees table, then select Data > Refresh All to update the loaded reports.

Build employee reports from the master table with Power Query
Link existing related tables: To add master-table fields to a separate table, load both tables into Power Query and use Home > Merge Queries, selecting the employee ID column in each table. A merge updates matching rows but does not automatically add every new employee to the separate table.
WPS Spreadsheets

Link Employee Report Fields in WPS Spreadsheets

Use employee IDs and VLOOKUP in WPS Spreadsheets to pull section and status values from a master sheet into a report.

  1. 1. Set up the master sheet: Open the workbook in WPS Spreadsheets. On a sheet named Master, place employee IDs in column A, sections in column B, and statuses in column C.
  2. 2. Enter report IDs: On a report sheet, enter the employee IDs to include in column A, starting at A2.
  3. 3. Look up employee fields: In B2, enter =IFERROR(VLOOKUP(A2,Master!$A$2:$C$1000,2,FALSE),"N/A"). In C2, use the same formula with 3 instead of 2 to retrieve Status, then fill both formulas down.
  4. 4. Filter and maintain the report: Select the report headers and choose Data > Filter, then filter the Status column to Active. Add new report IDs as needed, extend the lookup range if the master grows beyond row 1000, and reapply the filter after status changes.
Look up employee fields by unique IDFilter reports to show active employeesOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface at no cost
microsoft office alternative - wps office

Frequently Asked Questions

Should I delete employees who have left?

Keep their records in the master table and set Status to Inactive. Filter reports by Active when you only need current employees.

Why should employee tables use IDs instead of names?

Names can be duplicated or changed. A unique ID provides a consistent matching key for Power Query merges and lookup formulas.

How do I update Power Query reports after editing the master table?

In Excel, select Data > Refresh All. The queries will reload the master data and apply their saved filters and transformations.

Will XLOOKUP add new employees to a separate report automatically?

No. XLOOKUP retrieves values for IDs already in the report. Add new IDs to that table, or create a master-driven report with Power Query.