Data Import & Export

Use a Master Employee List to Update Excel Sheets Automatically

Camila MilosovichCamila Milosovich Nov 13, 2026 871 views

Question details

The user needs to maintain one editable employee list and use it as the single source for training registers, expiry sheets, and summary reports across a workbook.

Use a Master Employee List to Update WPS Office Sheets Automatically
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user manages multiple workbook sheets that depend on employee information and wants to avoid manually updating the same list in every sheet.
Observed behavior
The goal state is a single master employee list that automatically propagates changes to dependent sheets and reports without manual re-entry.
Before you start

Confirm that employee identifiers such as Employee ID, name, or email are identical in every sheet, and prepare a clean master list with one row per employee. If you plan to use Power Query or PivotTables, store the master data in a named Excel Table so new rows are included automatically.

Solution 1Recommended

Create a Master Excel Table and Link Dependent Sheets with Formulas

Use this when you want dependent training registers, expiry sheets, and reports to recalculate automatically after you edit the master list.

A named Excel Table expands automatically when employees are added or removed, so formulas that reference the table by name do not need to be re-selected each time.

For lookup fields, use VLOOKUP or INDEX/MATCH. For multi-cell results, use dynamic array formulas if supported by your WPS Office version.

1
Select and format the master employee list as a table

Open the workbook in WPS Office Spreadsheets, select the master employee range including headers, click Insert > Table, and check My table has headers.

2
Name the master table

With a cell in the table selected, go to Table Tools > Design, open the Table Name box, and enter something like MasterEmployees.

3
Reference the master table from another sheet

In the dependent sheet, click the first data cell and enter a lookup formula such as =VLOOKUP(A2,MasterEmployees,2,FALSE) or =XLOOKUP(A2,MasterEmployees[EmployeeID],MasterEmployees[Surname]).

4
Extend formulas and confirm automatic updates

Fill the formula down and across the required columns, then add or remove an employee in the master table and check that the dependent sheet updates immediately.

Create a Master Excel Table and Link Dependent Sheets with Formulas
Automatic updates enabled: The formulas recalculate whenever the master table changes; no manual copy-paste is required.
WPS Spreadsheets automation

Use a Master Employee List to Update WPS Spreadsheets Automatically

WPS Office Spreadsheets can act as the single source of truth: create one master employee table, link dependent sheets with formulas or queries, and refresh summary reports without maintaining duplicate copies.

  1. 1. Download and open WPS Office Spreadsheets: Download WPS Office Free, open the workbook with the master employee list, and sign in if you want cloud sync.
  2. 2. Create a named master employee table: Select the employee data, click Insert > Table, check My table has headers, and name the table in Table Tools > Design.
  3. 3. Link dependent sheets: In each reporting sheet, use lookup formulas or Power Query connections to pull data from the master table instead of pasting static lists.
  4. 4. Refresh and review: Add, remove, or reorder employees in the master table, then click Data > Refresh All or confirm that formulas recalculate automatically.
One editable master list updates training registers, expiry sheets, and reports automaticallyFull compatibility with Microsoft Excel .xlsx, .xls, and .csv filesFamiliar ribbon, formulas, and PivotTables help existing Excel users feel at homeLightweight and free to download, edit, and share cross-deviceSupports Excel Tables, Power Query, and refreshable data connections
microsoft office alternative - wps office

Frequently Asked Questions

Can WPS Office Spreadsheets create a named Excel Table like Excel?

Yes. Select the employee range, go to Insert > Table, check My table has headers, and rename the table from Table Tools > Design > Table Name.

Will the dependent sheets update automatically when I add a new employee?

Formulas that reference the named table by column update automatically. Power Query and PivotTables need a refresh: open Data > Refresh All or right-click the PivotTable and choose Refresh.

What is the best formula to pull employee details from a master list?

Use VLOOKUP or INDEX/MATCH for a single value. If your WPS version supports XLOOKUP, use =XLOOKUP(A2,MasterEmployees[EmployeeID],MasterEmployees[JobTitle]).

Can I use this master list for training registers and expiry sheets?

Yes. Create a master table and then link each training or expiry sheet by employee ID using lookup formulas or Power Query, so changes flow through after refresh.

What should I do if my WPS version does not have Power Query?

Use formula-based solutions with named Excel Tables and VLOOKUP or XLOOKUP. They are available in WPS Office Spreadsheets and update without refreshing queries.