If you would rather not use a pivot table, a small summary built with SUMIFS gives you a monthly spending chart that you control completely. List your categories once, add a formula beside each, and point a bar chart at the result. You can see exactly where every figure comes from.
The layout
Keep your transactions in columns A to C: Date in A, Category in B, Amount in C, with data in rows 2 to 500. On the same sheet, put the start date of the month in H1 and the end date in H2. In column F list the categories, one per row, and in column G the totals.
The formula
In G2, enter this and fill it down for each category:
- =SUMIFS($C$2:$C$500,$B$2:$B$500,$F2,$A$2:$A$500,">="&$H$1,$A$2:$A$500,"<="&$H$2)
- SUMIFS takes the range to add first, then pairs of range and condition. Here the pairs are category equals F2, date on or after H1 and date on or before H2.
- In Google Sheets the formula is identical. If your sheet uses a locale with decimal commas, the argument separator may be a semicolon, so follow what the formula bar accepts.
A worked month in pounds
Say a month's spending is as below. The figures are invented.
| Category | Amount (£) |
|---|---|
| Rent | 950 |
| Groceries | 310 |
| Bills | 165 |
| Eating out | 120 |
| Fun | 90 |
| Travel | 85 |
| Other | 80 |
| Total | 1,800 |
Add: 950 + 310 + 165 + 120 + 90 + 85 + 80 = 1,800.
How do I know nothing is missing?
Add a check cell. In G10 put =SUM(G2:G8), and in G11 put =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&$H$1,$A$2:$A$500,"<="&$H$2). If G10 and G11 differ, a transaction has a category that is not in your list, or it is spelt differently. Fix that row and the totals will match.
Sort it, then chart it
- Select F1:G8 and sort by column G from largest to smallest. In Excel use Data, then Sort. In Google Sheets use Data, then Sort range.
- Insert a bar chart from the sorted block.
- Give the chart a title that includes the month, and put the total in the title.
- Remove gridlines and legends that add nothing.
Changing the month
Because the dates are in H1 and H2, you only change two cells. For a new month, set H1 to the first day and H2 to the last day, and every total recalculates. Save a copy of last month's sheet if you want to compare them side by side.
Small upgrades once the basics work
A few additions make the sheet more useful without making it complicated.
- Add a percentage column: =G2/SUM($G$2:$G$8) formatted as a percentage, so each bar has a share beside it.
- Use conditional formatting to shade any category that is above the value in a target column you set yourself.
- Put a note beside the chart saying which accounts the data came from, so you remember what is missing.
- Keep a copy of each month's sheet as a new tab, named by month, instead of overwriting.
It also helps to decide, before you build anything, how many categories you want. Six to eight is a good number for a household chart. With fewer, the bars hide useful detail, and with more, the chart turns into a list and the small bars are unreadable. If a category is regularly smaller than a few percent of the total, fold it into Other and look inside it once in a while. The point of the chart is a quick read, so every extra bar has to earn its place on the page.
Next steps
- Add a fifth column for payment method if you want to see card against cash.
- Compare this month's bar with last month's by adding a second totals column.
- Look at the smallest categories as well as the largest. Small leaks add up.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

