Pivot Table Issues

Design an Excel Expense and Budget Tracker with a PivotTable

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Create a maintainable system for recording expenses and comparing actual spending with budgets.

Design an Excel Expense and Budget Tracker with a PivotTable
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the source table

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.

2
Add selection lists

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.

3
Enter budget and actual rows

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.

4
Create the PivotTable

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.

5
Show months and refresh

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 a structured table and an actual-versus-budget PivotTable
Match the reporting level: Budget and actual rows must use matching categories and periods for a meaningful comparison. Do not enter the same budget allocation more than once.
Track spending in WPS Spreadsheets

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. 1. Set up the data: Open WPS Spreadsheets and create columns for Date, Type, Budget, Sub-budget, Line Item, Employee, and Amount.
  2. 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. 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. 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.
Summarize budget and actual amounts in a PivotTableOpen and save Microsoft Excel XLSX workbooksKeep source entries together in an easy-to-update worksheetUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.