How to Categorize Bank Transactions and Track Spending in WPS Office Spreadsheets
Question details
The user needs to record every bank transaction in one row, assign a category through a drop-down list, and use a PivotTable to aggregate spending by category instead of copying amounts into separate category columns.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking personal or business bank transactions and producing a category-based spending summary in a spreadsheet.
- Observed behavior
- A single transaction table with a data validation category field and a PivotTable that updates category totals after refresh.
Have your bank transactions ready with dates, descriptions, and amounts. Create a separate sheet or range listing the spending categories you want to use in the drop-down list.
Set Up a Transaction Table with Category Drop-Downs and a Spending PivotTable
Use this method when you want a single source of transactions that automatically totals spending by category without maintaining separate columns.
Storing every transaction as one row lets the PivotTable aggregate the Amount field by Type, so you avoid duplicated data and manual formula errors.
Open WPS Office Spreadsheets, enter column headers such as Date, Description, Amount, and Type, select the data range, go to the Home tab, and choose Format as Table.
On a separate sheet, list your categories in one column. Select the Type column cells in the transaction table, go to the Data tab, click Validation, choose List, and set the source to the category list range.
Click any cell inside the transaction table, go to the Insert tab, click PivotTable, select New Worksheet, and confirm.
In the PivotTable Field List, drag Type to the Rows area and Amount to the Values area. WPS will total amounts by category.
After you add new transaction rows, click inside the PivotTable, go to the PivotTable Analyze tab, and choose Refresh All to update the spending summary.
Create an automatic bank transaction category summary in WPS Office Spreadsheets
WPS Office Spreadsheets gives you a free way to turn a simple bank transaction list into a drop-down category tracker and a PivotTable spending report, with .xlsx compatibility for Excel users.
- 1. Open WPS Office Spreadsheets: Download and install WPS Office, then create a new blank spreadsheet or open your existing .xlsx bank transaction workbook.
- 2. Enter transaction data: Use columns for Date, Description, Amount, and Type, and keep every transaction in its own row.
- 3. Set up category drop-downs: Add a category list on a separate sheet, select the Type column, and apply Data > Validation > List using that category range.
- 4. Create and arrange the PivotTable: Go to Insert > PivotTable, then drag Type to Rows and Amount to Values to see spending totals by category.
- 5. Save and refresh: Save the workbook as .xlsx and use Refresh All under the PivotTable Analyze tab after adding new transactions.
Frequently Asked Questions
How do I add or remove categories from the drop-down list?
Edit the category list on the separate sheet, then update the Data Validation source range. If the category list is formatted as a table, new categories are included automatically.
Why does my PivotTable not show a newly added transaction?
Click inside the PivotTable, go to the PivotTable Analyze tab, and choose Refresh All. Also ensure the transaction data is an Excel Table or that the PivotTable source range includes the new rows.
Can I track spending by month and category?
Yes. Add a Date column, insert a PivotTable, drag Date to Rows and group it by month, then place Type in Columns or Rows and Amount in Values.
Do I need to copy amounts into separate category columns?
No. Store all transactions in one row each and let the PivotTable aggregate the Amount by Type. This avoids duplicated data and manual formula errors.
Can I use this workbook in Microsoft Excel?
Yes. WPS Office Spreadsheets saves to .xlsx format, so data validation lists and PivotTables can be opened and refreshed in Microsoft Excel.
