How to Update Planned Excel Budget Data from an Actuals Sheet in WPS Office
Question details
The user needs to update planned budget records with actual expenses by matching a unique application ID across two Excel worksheets.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user keeps planned budget records on one worksheet and actual expense records on another, and needs to bring actual values into the planned budget by matching each row's unique application ID.
- Observed behavior
- Planned and actual data are maintained separately, so updated actual expense figures are not immediately available in the planned budget sheet.
Open the workbook in WPS Office Excel and confirm that the Planned and Actual worksheets use the same application ID values in a dedicated column, with no duplicate IDs in the actuals lookup range.
Update Planned Budget Fields with XLOOKUP and Excel Tables
Use this for a quick, formula-based update when you need a direct lookup from the Actuals sheet into the Planned sheet.
This method works best when both worksheets are already structured or can be converted to Excel tables. It keeps a dynamic link, so changes on the Actuals sheet recalculate automatically.
Open the workbook in WPS Office Excel, select the Planned data range, click Insert > Table, and check My table has headers. Repeat this on the Actuals worksheet.
Next to the planned budget table, type the header Actual Expense in the first empty column and press Enter so the table includes the new column.
In the first Actual Expense cell, enter =XLOOKUP([@ApplicationID],Actuals[ApplicationID],Actuals[ActualExpense],"Not found") and press Enter. Replace table and column names to match your workbook.
Merge Planned and Actual Tables with Power Query
Use this for recurring reports or larger datasets when you want a clean merged table instead of keeping formulas on the Planned sheet.
Update Planned Budget Actuals in WPS Office Excel with XLOOKUP
WPS Office Excel can update your planned budget directly by matching application IDs across worksheets. Use XLOOKUP in structured Excel tables to pull actual expense fields into the planned sheet, and save the workbook as a standard .xlsx file.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office, open your .xlsx workbook, and switch to the Planned budget worksheet.
- 2. Convert data ranges to Excel tables: Select the planned data, click Insert > Table, and enable My table has headers. Repeat this on the Actuals worksheet.
- 3. Add an Actual Expense column: In the planned table, enter Actual Expense as the next header and press Enter.
- 4. Enter the XLOOKUP formula: Type =XLOOKUP([@ApplicationID],Actuals[ApplicationID],Actuals[ActualExpense],"Not found") in the first data cell and let the table populate the column.
Frequently Asked Questions
Does WPS Office Excel support XLOOKUP for matching application IDs across worksheets?
Yes. You can use =XLOOKUP([@ApplicationID],Actuals[ApplicationID],Actuals[ActualExpense],"Not found") in a table formula, and WPS Office recalculates it in .xlsx workbooks.
What should I do if an application ID appears more than once in the Actuals sheet?
XLOOKUP returns the first match. If you need all rows or aggregated values, use Power Query to merge the Planned and Actual tables and remove duplicates or create a grouped total before loading the result.
How do I update the planned budget when actual expenses change?
Formulas update automatically when the workbook recalculates. If you use Power Query, refresh the merged result with Data > Refresh All after updating the Actuals table.
Can I merge Planned and Actual sheets with Power Query in WPS Office?
Yes. Select Data > Get Data > From Table/Range for each table, then use the Merge Queries command to join them on the Application ID column and load the expanded fields.
Do the Planned and Actual sheets need the same column order?
No. Use the application ID as the key column and select only the actual fields you need, so the two sheets do not need to have identical column layouts.
