How to Merge Department Updates Back into a Master Excel File in WPS Office
Question details
The user needs to combine changes from department-specific Excel report copies back into an original master workbook without overwriting columns that were not changed or were protected.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Departments update separate copies of an Excel report, and the owner must consolidate only valid changes into the master workbook using a unique record identifier.
- Observed behavior
- Simple copy-paste or direct merging can overwrite unchanged master columns, so the goal is to replace master values only when a department copy contains a valid change.
Make sure the master workbook and every department copy share the same unique record ID column and the same column layout. Place all returned department files in one folder so the editable columns can be staged and compared consistently.
Merge returned department files with a VLOOKUP-controlled staging sheet in WPS Spreadsheets
Use this method when you need to manually merge small-to-medium department copies and replace master values only for valid, non-empty department updates keyed by a unique record ID.
A controlled merge keeps your original master values until a department copy contains a valid non-empty entry. This prevents blank or filtered cells from wiping out existing master data and preserves columns that departments were not allowed to edit.
Click File > Open in WPS Office Spreadsheets and select the master Excel file that will receive the department updates.
Click the plus icon next to the sheet tabs to insert a new worksheet, then copy each department table into the same staging sheet with the unique record ID column in the first column.
In the first editable master cell, click the formula bar and enter a formula such as =IF(AND(VLOOKUP(A2,Staging!$A$2:$H$100,3,0)<>"",VLOOKUP(A2,Staging!$A$2:$H$100,3,0)<>0),VLOOKUP(A2,Staging!$A$2:$H$100,3,0),B2). Adjust the range and column index to match your staging sheet.
Press Enter, then drag the fill handle down and across the editable columns. Skip any columns that departments should not modify so the master values remain unchanged.
Select the newly calculated cells, right-click, choose Copy, then right-click again and use Paste Values to save the merged updates as static values.
Avoid future merge conflicts by sharing one controlled WPS workbook
Use this approach when the merging process is recurring. A shared WPS workbook or WPS Cloud link removes the need to distribute filtered copies and recombine them later.
Merge department Excel updates in WPS Office with lookup-controlled logic
Use WPS Spreadsheets to stage returned department copies in one workbook, merge them into the master file by unique record ID, and replace only valid editable columns without overwriting protected data. Save the result as an Excel-compatible workbook and keep full .xlsx compatibility.
- 1. Install WPS Office and open the master file: Download WPS Office from the official website, install it, launch WPS Spreadsheets, and open your master .xlsx report.
- 2. Create a staging worksheet for department copies: Add a new sheet and copy the returned department tables into it, keeping the unique record ID column available for lookup.
- 3. Use lookup formulas to merge only valid updates: Use VLOOKUP or INDEX/MATCH formulas keyed to the unique ID in WPS Spreadsheets to replace master values only when the department copy has a non-empty valid entry.
- 4. Protect non-editable columns before sharing: Select the master columns that departments should not modify, then click Review > Protect Sheet to lock them.
- 5. Save as Excel workbook: Save the final merged file as .xlsx to maintain full Microsoft Excel format compatibility.
Frequently Asked Questions
Does WPS Office include Power Query for merging Excel files like Microsoft Excel does?
WPS Office does not include the same Power Query ribbon as Microsoft Excel, but you can open .xlsx files and merge department updates with a staging sheet and lookup formulas keyed to a unique ID. If you already use Power Query in Microsoft Excel, WPS Office can open and work with the resulting workbook.
How do I prevent department copies from overwriting master-only columns?
Keep the master-only columns out of your lookup formula range and protect them in WPS Spreadsheets by clicking Review > Protect Sheet. Only columns that departments are allowed to edit should be included in the merge formulas.
Can I merge multiple department files into one master sheet at once?
Yes. Copy all returned department tables into one staging worksheet, keep the unique record ID column consistent, and then apply the VLOOKUP or INDEX/MATCH formulas to the editable master columns so each value is replaced only when a valid update exists.
What is the safest long-term way to avoid re-merging department copies?
Keep one shared workbook or WPS Cloud file as the single source of truth. Use the Share button in WPS Spreadsheets and protect restricted columns so departments edit directly in the shared file instead of sending filtered copies back for manual merging.
