Paste your transactions into a sheet, summarise the amount by category with a pivot table, then insert a bar chart from the summary to chart monthly spending. Once the sheet is set up, each new month takes a paste and a refresh, not an afternoon.
Get the data into the right shape
A pivot table needs one row per transaction and one heading above every column. Use four columns: Date, Description, Category, Amount. Download your bank or card export as CSV, paste it under those headings, and drop any column you do not need. Spending should be a positive number, so if your export shows purchases as negatives, multiply that column by -1 first.
Build the pivot table, step by step
- Click any cell in your data and choose Insert, then PivotTable. In Google Sheets it is Insert, then Pivot table.
- Put Category in Rows.
- Put Amount in Values and set it to Sum.
- Put Date in Filters, or add a Month helper column and filter that, so only the month you want is included.
- Select the summary and insert a bar chart (in Excel, the PivotChart option does this in one step).
A worked month in dollars
Say a household spent $2,800 in a month, made up as in the table. The numbers are invented, but the check is real: the category totals must add back to the sum of all transactions.
| Category | Amount ($) |
|---|---|
| Rent | 1,450 |
| Groceries | 520 |
| Utilities | 240 |
| Dining out | 210 |
| Transport | 175 |
| Other | 157 |
| Subscriptions | 48 |
| Total | 2,800 |
Check: 1,450 + 520 + 240 + 210 + 175 + 157 + 48 = 2,800. If the pivot total differs from the total of your raw Amount column, some rows have a blank or misspelt category, and the pivot is showing them under a blank label.
Which chart should you pick?
A horizontal bar chart sorted from the biggest category to the smallest is the clearest, because the eye compares bar lengths easily and the category names stay readable. A pie chart is fine for three or four slices, such as rent, food and everything else, and poor beyond that. Seven slices of different colours teach nobody anything.
Why does my pivot not update?
Pivot tables in both apps do not always refresh the moment you paste new rows. Right-click the pivot and choose Refresh in Excel, and check that the data range covers the new rows. In Google Sheets, extend the range from the pivot editor. This is the most common reason a chart looks like last month's.
Reading the chart you just made
A bar chart of a $2,800 month is not much use until you ask what you would expect each bar to be. A rule of thumb of your own works better than any average: before you open the chart, guess which category will be largest and which will surprise you. Then compare. The gap between guess and reality is where the learning happens. If a category is bigger than you guessed two months running, that is a signal to open its transactions rather than just to feel bad about it.
A five-minute monthly routine
- Paste the new transactions below the old ones.
- Fill in any blank categories, using the Description column as a guide.
- Refresh the pivot and look at the total.
- Look at the chart, pick the one bar that surprises you, and open its transactions.
A chart is useful only when it prompts a question. Take the bar you did not expect, filter the data to that category, and read the lines. That is where a forgotten subscription or a doubled payment shows up.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


