Design an Excel Expense and Budget Tracker with a PivotTable
Question details
Create a maintainable system for recording expenses and comparing actual spending with budgets.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Track expenses across budgets, sub-budgets, line items, employees, and months without writing transactions into multiple reporting worksheets.
- Observed behavior
- A central transaction table feeds a refreshable PivotTable that summarizes budgeted and actual amounts.
List the budgets, sub-budgets, line items, and employees you need to track. Decide whether budgets will be entered monthly and use consistent categories for budget and actual rows.
Build a structured table and an actual-versus-budget PivotTable
Use one source table to record budget and actual amounts, then summarize them in a PivotTable.
Enter each expense as a separate Actual row. Enter budget allocations as Budget rows at the same category and monthly level you want to compare; leave Employee blank on budget rows if it does not apply.
In a new worksheet, add headers for Date, Type, Budget, Sub-budget, Line Item, Employee, and Amount. Select the range and choose Insert > Table, confirming that the table has headers.
Enter your allowed types, budgets, categories, and employees in list ranges. Select the relevant cells in the source table and use Data > Data Validation > List to point each selection field to its list; use named ranges for lists on another worksheet.
Add a Budget row for each allocation and an Actual row for each expense. Enter numeric amounts and dates, using a date in the applicable month for each monthly budget allocation.
Click inside the source table and select Insert > PivotTable. Add Budget, Sub-budget, and Line Item to Rows, Type to Columns, and Amount to Values; check that Amount is summarized by Sum.
Add Date to Rows, then right-click a date in the PivotTable and choose Group to group by Months and Years. After adding or editing source rows, right-click the PivotTable and select Refresh.

Build an expense and budget PivotTable in WPS Office
Use WPS Spreadsheets to keep budget allocations and expenses in one worksheet, then create a PivotTable to compare amounts by category.
- 1. Set up the data: Open WPS Spreadsheets and create columns for Date, Type, Budget, Sub-budget, Line Item, Employee, and Amount.
- 2. Record allocations and expenses: Enter budget allocations as Budget rows and individual expenses as Actual rows. Use matching categories and dates in the applicable reporting month.
- 3. Insert a PivotTable: Select the populated data range and choose Insert > PivotTable. Place budget categories in Rows, Type in Columns, and Amount in Values, summarized by Sum.
- 4. Update the report: When you add or change entries, select the PivotTable and use Refresh. If new rows fall outside the original source range, update the PivotTable's data source to include them.

Frequently Asked Questions
How do I compare budget and actual spending in a PivotTable?
Enter budget allocations and expenses as separate rows with Budget or Actual in a Type column. In the PivotTable field list, put Type in Columns and Amount in Values, summarized by Sum.
How do I update the report after entering expenses?
Add the expenses to the Excel source table, then right-click the PivotTable and select Refresh. A PivotTable based on the table can include its new rows when refreshed.
Can I track expenses by month?
Add Date to the PivotTable Rows area, right-click a date, and choose Group. Select Months and Years so that the same month in different years remains distinct.
How can I keep employee and category names consistent?
Maintain lists of valid names and categories in the workbook, then use Data > Data Validation > List on the corresponding entry cells. Named ranges can make lists on another worksheet easier to reference.
Should budgets and expenses always be in the same table?
One table is convenient for a simple report. For more complex models, you can keep budgets and actuals in separate tables and connect them through appropriate relationships in Excel's Data Model.




