A calendar heat map is a grid with weeks down the side, weekdays across the top, and each cell holding that day's spend, coloured from light to dark with a colour scale. Build the dates with a formula, fetch spending with SUMIFS, then apply Home, Conditional Formatting, Color Scales. You will see at a glance which weekdays and weeks run heavy.
How do I build the date grid for one month?
Put the first day of the month in B1; as a hypothetical, 01-Oct-2025, which falls on a Wednesday. Label B3:H3 Mon to Sun. In B4 enter the Monday on or before the first day:
- =$B$1-WEEKDAY($B$1,2)+1
- WEEKDAY with the second argument 2 numbers Monday as 1 and Sunday as 7. For 01-Oct-2025 it returns 3, so B4 becomes 29-Sep-2025.
- In C4 enter =B4+1 and fill right to H4. In B5 enter =H4+1, then fill right and down through six rows (B4:H9).
That gives a six-row calendar. Some cells show dates from the neighbouring months; the next step hides them.
How do I pull daily spending into the grid?
Copy the grid down below as a second block of the same size, say B12:H17, and put the spend in each cell. If transaction dates are in Txn!A and amounts in Txn!C:
- =IF(MONTH(B4)<>MONTH($B$1),"",SUMIFS(Txn!$C$2:$C$2000,Txn!$A$2:$A$2000,B4))
- The IF returns an empty text for days outside the month, which colour scales ignore.
- Dates with a time part need SUMIFS with ">="&B4 and "<"&B4+1 instead of an exact match.
How do I apply the colour scale?
- Select the spend block (B12:H17).
- Choose Home, Conditional Formatting, Color Scales, and pick a white-to-dark one.
- Open Manage Rules, then Edit Rule, to set the Maximum to Percentile 90, so one huge day does not flatten the rest.
- Add a number format such as #,##0;-#,##0;"-" so zero days show a dash.
What should I do about zero days?
Days with no spending get the lightest shade, the same as very small spending days. If you want a visible difference, add a rule above the scale (Home, Conditional Formatting, New Rule, Format only cells that contain, Cell Value equal to 0) with a pale grey fill. Then a grey day means nothing was spent, and a pale colour means something small was.
How can I see the whole year?
Switch to a layout with weeks across and weekdays down: seven rows, 53 columns. Start with the Monday on or before 1 April (1 April 2025 is a Tuesday, so the first cell is 31 March 2025) and add 7 days per column. The cell formula is the start date plus (column number - 1) x 7 plus (row number - 1), and SUMIFS fills the spend in the same way. Colour the whole block with a single scale. Heavy festival weeks and quiet weeks stand out.
| Pattern you may notice | What to check |
|---|---|
| A dark column on the same weekday every week | A regular habit, such as weekly shopping |
| A dark cell at month start | Rent, EMI or auto-debit payments |
| A dark cluster in one week | Travel, a festival or a one-off purchase |
| Many grey cells | Zero days, or missing data for the period |
Can I add weekday totals beside the grid?
Yes. Under each column of the spend block add =SUM(B12:B17) for the weekday total and =IFERROR(SUM(B12:B17)/COUNT(B12:B17),0) for the average per day. COUNT counts only numbers, so the blank text cells for other months are left out. If Saturday's average is clearly higher than the rest, you have found a habit worth a closer look, and you can set a weekend budget for it.
Checklist
- Check the grid dates, especially the first and last row.
- Match a few cells with your statement.
- Compare months by using the same colour scale maximum.
- Do not read a day as a trend; look at patterns across weeks.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


