Spreadsheets

Calendar heat map of daily spending in Excel

Build a calendar heat map of daily spending in Excel with a week-by-weekday grid, SUMIFS and a colour scale, starting with one month and extending to a year.

Editorial illustration for Calendar heat map of daily spending in Excel

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?

  1. Select the spend block (B12:H17).
  2. Choose Home, Conditional Formatting, Color Scales, and pick a white-to-dark one.
  3. Open Manage Rules, then Edit Rule, to set the Maximum to Percentile 90, so one huge day does not flatten the rest.
  4. 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 noticeWhat to check
A dark column on the same weekday every weekA regular habit, such as weekly shopping
A dark cell at month startRent, EMI or auto-debit payments
A dark cluster in one weekTravel, a festival or a one-off purchase
Many grey cellsZero 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports