Keep Employee Reports Linked to a Master Excel Table
Question details
Create employee reports that stay linked to one master 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.
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.
Build employee reports from the master table with Power Query
Use Power Query when you want refreshable reports based on the same employee list.
Select the employee data, choose Insert > Table, and give the table a name such as Employees on the Table Design tab.
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.
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.
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.
Update the Employees table, then select Data > Refresh All to update the loaded reports.

Retrieve employee fields with XLOOKUP
Use XLOOKUP when a separate Excel table already contains employee IDs and needs fields from the master table.
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. 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. Enter report IDs: On a report sheet, enter the employee IDs to include in column A, starting at A2.
- 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. 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.

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.




