To make a spending heat map in Excel, lay out categories down the side and months across the top, select the numbers, and choose Home, Conditional Formatting, Color Scales. Apply the scale to each row separately, rather than to the whole table, so every category shows its own busiest months instead of being drowned out by rent.
Start with a category by month grid
A pivot table of Sum of Amount with Category in Rows and months in Columns works, or a SUMIFS grid. Remove the grand total row and column before colouring, because totals are always the darkest cells and distort the scale.
Whole-table scale or per-row scale?
| Approach | What it answers | Weakness |
|---|---|---|
| One scale over the whole table | Which category-month cells are biggest in rupees? | Rent and EMIs stay dark all year, and everything small looks pale |
| One scale per row | In which months is each category at its peak? | A nearly flat row can show strong colour for a trivial difference |
Say electricity runs 1,900, 2,100, 3,100, 3,400, 3,000 and 2,200 from April to September. Beside rent at 18,000 and groceries at 9,000, a whole-table scale paints that row almost white. A per-row scale makes July the darkest cell, which is the useful fact.
Apply the colour scale to each row
- Select the numbers in the first category's row only, for example B2:M2.
- Choose Home, then Conditional Formatting, then Color Scales, and pick a white-to-one-colour option.
- Select the next row and repeat.
- For ten categories this takes a minute. Open Conditional Formatting, then Manage Rules, to confirm each row has its own rule.
Choose a single hue, white to dark green or white to dark orange, rather than red-yellow-green. Single-hue scales are easier to read when printed and work better for people who find red and green hard to tell apart.
What stops a flat row looking dramatic?
By default each row's scale runs from its own lowest value to its highest. A rent row of 18,000 every month except one month at 18,500 would look striking. Fix it by editing the rule: Manage Rules, Edit Rule, and set Minimum to Type Number with the value 0. Now colour depth reflects how large the amount is against its peak rather than how far it is from its own minimum, and a flat row stays uniformly dark.
A helper grid that needs only one rule
If you would rather manage one rule, build a second grid beside the first, with each cell showing the month as a share of that category's busiest month:
=IF(MAX($B2:$M2)=0, 0, B2/MAX($B2:$M2))
Fill it over the whole block, format as Percentage, and apply a single colour scale with Minimum set to Number 0 and Maximum to Number 1. A cell reading 100% is that category's peak month. The cost is a second grid on the sheet, so keep it beside the real numbers or on a calc sheet.
Mistakes that spoil a heat map
- Blank cells for months with no spend are left uncoloured, while 0 is coloured as the lowest. Pick one and stay consistent.
- Hiding the numbers makes it harder to check; keep them visible in a small, grey font.
- More than about twelve categories or months makes the grid unreadable on one screen.
- A scale colouring both years in one row hides year-on-year changes. Use separate blocks per year.
Using it
A heat map is for spotting patterns: power bills in summer, travel in a particular month, gifts around festivals. Pick the two darkest cells in each row, open the transactions behind them, and decide whether they were planned. For a ready-made version, SpendixAI has a library of 25 ready-made reports grouped by question, which is worth trying if you prefer not to rebuild the grid each month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

