Once you have more than a few dozen receipts, writing a SUMIF formula for every category gets tedious. A PivotTable gives you totals by category, store and month with a few clicks and no formulas at all. Here's how to build one step by step, even if you've never used one.
1. Tidy the source table first
PivotTables work best when the data underneath is clean.
- Row 1 holds headers only (Date, Store, Total, Category and so on), with no blank rows or subtotal rows in the middle.
- Every cell in the date column should be a real date and every amount a number. Files from the receipt tool already come that way.
- Delete the total row at the bottom of the downloaded file, or leave it out of the range. Otherwise everything gets counted twice.
Click any cell in the data and press Ctrl+T (⌘+T on a Mac) to turn it into an Excel Table. The range then grows automatically when you add rows underneath.
2. Create the PivotTable
- Click a cell in the table and choose Insert > PivotTable.
- Choose "New Worksheet" and click OK.
- In the field list on the right, drag Category to Rows and Total to Values.
That's a category summary. To see spending by store instead, swap Category for Store in the Rows area.
3. When it counts instead of sums
If the Values area says "Count of Total", the amount column contains text or blanks. Click the field in Values and choose Value Field Settings > Sum, then find and fix the text cells too. Filtering the amount column or looking for left-aligned numbers usually reveals them.
4. Group dates by month
Put Date in Rows and recent versions of Excel group it into years, quarters and months automatically. If yours doesn't, right-click any date in the PivotTable, choose Group, and select both Months and Years. Leaving out Years merges the same month from different years into one row.
Months in Rows and Category in Columns gives you a month-by-category grid in one table.
| Month | Food | Transport | Household | Grand Total |
|---|---|---|---|---|
| Jul | 412.30 | 98.00 | 64.50 | 574.80 |
| Aug | 455.90 | 121.40 | 38.20 | 615.50 |
5. Refresh after adding receipts
PivotTables don't update the moment the source changes. After pasting this month's receipts, click inside the PivotTable and choose Data > Refresh All. If you didn't turn the source into a Table in step 1, the new rows sit outside the range, so widen it with PivotTable Analyze > Change Data Source.
6. Make it easier to read
- In Value Field Settings > Number Format, choose a number format with thousands separators. It survives every refresh.
- To see the biggest categories first, open the Row Labels drop-down and choose More Sort Options > Descending (by Sum of Total).
- Add a Slicer (PivotTable Analyze > Insert Slicer) for payment method or person, and filter the whole table with a click.
Summary
PivotTables come down to a tidy source table, dragging fields into Rows and Values, and refreshing after you add data. Let the receipt tool turn photos into rows, press Refresh once a month, and your category and monthly totals are ready.