How to make a heatmap in Excel
A heatmap colours a grid by value so patterns jump out — busy hours, hot regions, seasonal peaks. In Excel it's a colour-scale away. Here's how, and the one-click alternative.
When a heatmap is the right tool
Whenever the story lives in a grid — two dimensions of categories with a number in every cell.
Time patterns
Orders by hour and weekday — find the peaks to staff for.
Geography
Sales or risk by region — see the hot and cold spots.
Correlation grids
Colour a correlation matrix to find what moves with what.
How to make one in Excel
No chart type and no formulas — just a grid of numbers and a conditional-formatting colour scale.
Lay out a matrix
Rows and columns of categories with a numeric value in each cell (e.g. hour × weekday).
Select the values
Highlight just the number cells, not the headers.
Apply a colour scale
Home › Conditional Formatting › Color Scales. Pick a 2- or 3-colour scale.
Tune the scale
Manage Rules › Edit to set the min/mid/max colours and values so the contrast reads clearly.
A colour-scale heatmap is one of Excel's best-kept secrets — zero formulas, instant insight. The limits show up at scale: it doesn't aggregate raw rows into the grid for you, so you still have to pivot first, and the colours don't carry into a clean exported report.
The faster way — skip the setup
The colours are easy; the pivoting and the refresh-every-week are not. DataHub Pro takes raw rows straight to a coloured grid.
Pivot first, then colour
- Build the matrix with a PivotTable
- Apply and tune the colour scale
- Re-pivot when new data arrives
- Colours don't travel into reports
Heatmap from raw rows
- Upload raw data — it pivots and colours for you
- Sensible scales chosen automatically
- Refreshes on new data instantly
- Exports into dashboards and reports
FAQ
How do I make a heatmap in Excel?
Lay your data out as a grid of numbers, select the value cells, then use Home › Conditional Formatting › Color Scales. Excel shades each cell by its value so patterns appear.
Can Excel make a heatmap without conditional formatting?
Conditional formatting colour scales are the standard way. You can also use a filled-map chart for geographic heatmaps, or a surface chart, but colour scales on a grid are simplest and most flexible.
How do I change the heatmap colours?
Go to Home › Conditional Formatting › Manage Rules, edit the colour-scale rule, and set the minimum, midpoint and maximum colours and their threshold values.
Keep exploring
More visual analysis you can run on your own data — no sign-up needed to read.
Get the heatmap from raw data
Upload an Excel or CSV file and DataHub Pro pivots it into a grid and colours the pattern for you. Free to try, no card.
Start free →