How to Relate Accounts and Transactions in WPS Office Excel for a Finance Report
Question details
The user needs to build a multi-year personal finance report in WPS Office Excel that summarizes transactions by account attributes and bank without manually repeating account details in every transaction row.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has account data and transaction data in separate Excel tables and wants to combine both in a PivotTable report without duplicating account fields across transaction rows.
- Observed behavior
- The expected result is a PivotTable that totals transactions by bank and account attributes from two related tables without repeated manual account entries.
Make sure your account table and transaction table are both saved as WPS Office Excel tables and share a common key column, such as Account ID or Account Number. Confirm that the transaction table contains complete date, amount, and account key data before creating the lookup column.
Use a Lookup Column to Combine Account Attributes and Create a PivotTable
Use this solution when you need a finance report in WPS Office Excel based on two related tables without manually copying account details into every transaction row.
WPS Office Excel does not expose the Microsoft Excel Data Model relationship dialog in the same way, so a lookup formula creates the needed relationship and keeps the account table as the single source for account attributes.
Open the workbook in WPS Office Excel and confirm both ranges are formatted as tables by selecting a cell in each range and pressing Ctrl+T.
In the transaction table, insert a new column named Bank or Account Attribute, then enter =VLOOKUP([@AccountID],AccountsTable,2,FALSE) to pull the account attribute from the account table.
Select any cell in the transaction table, go to the Insert tab, and click PivotTable. Choose a new worksheet as the destination.
In the PivotTable Field List, drag Bank and Account Attribute to the Rows area and drag Amount to the Values area to summarize transaction totals.
Right-click the PivotTable and choose Refresh, then apply currency or number formatting to the value field so the finance report is easy to read.
Build a Multi-Year Finance Report with WPS Office Excel PivotTable
Use WPS Office Excel to combine account and transaction tables with a lookup column, then create a PivotTable that reports totals by bank and account attributes without repeating account details on every row.
- 1. Download and install WPS Office: Go to the official WPS Office website, download WPS Office Free, and open the workbook containing your account and transaction tables.
- 2. Prepare your Excel tables: Open WPS Office Excel, select your account and transaction ranges, and press Ctrl+T to convert each range to a table.
- 3. Add the account attribute column: In the transaction table, insert a Bank column and use =VLOOKUP([@AccountID],AccountsTable,2,FALSE) to pull the account attribute from the account table.
- 4. Insert the PivotTable: Click any transaction table cell, go to Insert > PivotTable, and choose a new worksheet as the report location.
- 5. Build and refresh the report: Drag Bank and Account Attribute to Rows and Amount to Values, then right-click the PivotTable and choose Refresh after adding new transactions.
Frequently Asked Questions
Can WPS Office Excel create a Data Model relationship like Microsoft Excel?
WPS Office Excel does not include the Excel Data Model or Power Pivot relationship dialog in the same way, but you can achieve the same report using a VLOOKUP or XLOOKUP column and then a PivotTable.
How do I keep account details from repeating on every transaction row?
Store the account attributes once in an account table, and use a lookup formula in the transaction table or build a PivotTable from a combined lookup column so the source account table stays normalized.
Can I open an existing Microsoft Excel workbook with two tables in WPS Office?
Yes, WPS Office opens .xlsx and .xls files and preserves Excel tables. Format the ranges as tables and create the PivotTable in WPS Office Excel.
How do I refresh the finance report after adding new transactions?
Right-click the PivotTable and choose Refresh, or go to the PivotTable Analyze tab and click Refresh to update the totals.
Which WPS formula can pull account attributes into the transaction table?
Use =VLOOKUP([@AccountID],AccountsTable,column_number,FALSE) or =XLOOKUP([@AccountID],AccountsTable[AccountID],AccountsTable[Bank]) if XLOOKUP is available.
