To build a spending heatmap in Excel, make a grid with categories down the side and months across the top, fill it with SUMIFS, then apply a 3-colour scale. The catch is that one scale over the whole grid paints rent red every month and everything else pale. Colouring each category against its own peak is what shows you the festival month, the summer electricity month and the school-fee month.
How do you build the category-by-month grid?
Say your Expenses sheet has Date in A, Category in B and Amount in C, running to row 2000. On a sheet called Heatmap, type categories in A2:A8. In B1 type the first month of your April to March year as a real date, such as 01-04-2027, and in C1 type =EDATE(B1,1) and fill across to M1. Format row 1 as mmm. Then in B2:
=SUMIFS(Expenses!$C$2:$C$2000,Expenses!$B$2:$B$2000,$A2,Expenses!$A$2:$A$2000,">="&B$1,Expenses!$A$2:$A$2000,"<"&EDATE(B$1,1))
Fill it right to column M and down to row 8. The dollar signs lock the expense ranges, the category column and the month row, so one formula serves all 84 cells.
Why does a single colour scale on the whole grid mislead?
A colour scale ranks every number against all the others in its range. If Rent is ₹18,000 and Electricity is ₹2,600, the scale treats ₹2,600 as nearly the cheapest cell on the sheet, even though it is the highest that category ever gets. You would see a red rent row and a green everything else, which tells you nothing new.
How do you colour each row against its own peak?
The reliable method is a helper grid. In O2 enter:
=IF(MAX($B2:$M2)=0,0,B2/MAX($B2:$M2))
and fill across to Z2 and down to row 8. Each cell now says what fraction of that category's yearly peak the month reached, so 1 is the heaviest month. Select O2:Z8 once, then choose Conditional Formatting, Color Scales, and pick a three-colour option. Use Manage Rules, Edit Rule to set the minimum to Number 0.5, the midpoint to Number 0.75 and the maximum to Number 1. Anything at or below half its own peak shows as the coolest colour.
| Category | Apr | May | Jun | Jul | Peak in the year |
|---|---|---|---|---|---|
| Electricity | ₹1,800 | ₹2,600 | ₹3,100 | ₹2,900 | ₹3,100 |
| Groceries | ₹7,200 | ₹6,900 | ₹7,500 | ₹7,000 | ₹7,500 |
Electricity in April is 1,800 divided by 3,100, which is 58 percent of its peak, so it sits at the cool end; May is 84 percent, June 100 percent, July 94 percent. Groceries run 96, 92, 100 and 93 percent, so they all look hot, which honestly says the spend is steady all year. The numbers here are invented to show the method.
Can you skip the helper grid?
You can create one colour-scale rule per row. Select B2:M2 only, apply the scale, then repeat for B3:M3 and so on, seven separate rules. When you copy formats downward with Paste Special, check Manage Rules afterwards, because a single rule that now covers several rows will compare them all together. The helper grid avoids that trap, and you can hide columns O to Z once it works.
What should you look for in the result?
- Columns that are dark across many categories, usually the festival months or the month a big annual bill falls due.
- A category with one dark cell and the rest pale, which is a one-off worth explaining in a note.
- Rows that are uniformly dark. These are fixed costs, not places to save by habit.
Add a Total row beneath the grid with =SUM(B2:B8) filled across, so you can compare month totals against the heat above them.
Keeping that grid current is the real chore. SpendixAI can import statements from your bank or card account and has a library of ready-made reports grouped by question, which is worth a look once the sheet's refresh feels like homework.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


