How to Create a Heatmap in Excel (No Formulas)

Step-by-step

A heatmap colours each cell by its value, so busy and quiet spots jump out without reading a single number. The example uses five years of coffee shop orders by day of week and hour of day. It’s all conditional formatting and formatting tricks: no formulas, no VBA.

1. Get the grid the right way round

Copy the totals block (A53:Y60), go to a new sheet, click E5 and use Paste Special with Values and Transpose ticked. Now the days run across the top and the hours run down the side.

2. Add totals

Leave column M empty as a spacer. In N5 type Total, and in N6 add a row total with =SUM(F6:L6), filled down. In E32 type Total and in F32 add a column total with =SUM(F6:F29), filled across.

3. Apply a three-colour scale

Select the data grid (F6:L29), go to Conditional Formatting > New Rule and set the format style to 3-Color Scale: a very light red for the minimum, a medium red for the midpoint, and a dark red for the maximum. The morning rush and weekend surge show up instantly.

4. Make it look like tiles

With the grid selected, open Borders > More Borders, set the colour to white and apply Outline and Inside.

5. Hide the numbers

Open Format Cells > Custom and type three semicolons: ;;;. The numbers disappear but are still there; switch back to General to see them.

6. Tidy the layout

Use a compact font (Trebuchet, size 10), shorten the days to Mon, Tue, Wed… and centre them. Set the day columns to width 3 and the hour rows to height 15, make the spacer column M about 0.3 wide, and turn off gridlines.

7. Add data bars and a mini chart

Give the row totals (N6:N29) solid Data Bars; edit the rule and tick Show Bar Only. Select the day totals (F32:L32), insert a column chart, remove its title, border and gridlines, set the gap width to 50%, and point the axis labels at the day names. For tidy axis numbers like 6K, use the custom format #,##0,"K".

8. Add a legend and title

Put Low, Medium and High in O2:Q2 with the three colours underneath, add a title such as Coffee shop orders by day and time, and a note like Darker = more orders. Optionally add a Top 10 rule set to the top 5 with a red border to outline the busiest slots.