Creating a heat map in Excel helps you visualize intensity patterns across categories or time, making trends easy to spot at a glance. This quick guide shows how to build a clear, actionable heat map using conditional formatting and structured data.
Below is a focused roadmap that outlines the essential inputs, steps, and checks you need before building the heat map in Excel.
| Goal | Data Requirement | Tool Used | Output Example |
|---|---|---|---|
| Compare performance by region | Region, Metric, Value | Conditional Formatting | Color gradient cells by value |
| Track weekly intensity | Week Number, Sales, Target | Icon Sets or Color Scales | Red-Amber-Green heat cells |
| Highlight peak hours | Hour, Visits, Conversion | Custom Rules | Dark shade for highest density |
| Monitor SLA compliance | Ticket ID, Response Time, SLA | Formula + Scale | Quick risk spotting |
Structure your source data correctly
Clean and consistent data is the foundation of a reliable heat map. Use a rectangular range with clear headers and numeric values only.
Recommended table layout
- One row per record or time period
- One column per category you want to compare
- No merged cells inside the data range
- Total and subtotal rows placed outside the range
Apply conditional formatting for color scales
Color scales provide an instant visual heat map by mapping low, mid, and high values to a gradient.
Steps to add a color scale
- Select the numeric data range
- Go to Home > Conditional Formatting > Color Scales
- Choose a gradient that suits your context (red-yellow-green or blue-white-red)
Use icon sets for categorical heat signals
When you prefer symbols over gradients, icon sets show performance classes clearly across rows and columns.
How to set icon rules
- Select the range you want to visualize
- Open Conditional Formatting > Icon Sets
- Pick a 3-icon set and adjust thresholds in Manage Rules if needed
Customize rules for your specific thresholds
Default rules may not match your business context; creating custom rules keeps the heat map aligned with targets.
Build a custom 2-Color Scale rule
- Select data > Conditional Formatting > New Rule
- Use Format by: Cell Value and set min/type and max/type
- Pick distinct colors for low and high ends
Refine and maintain your heat map over time
Ongoing maintenance ensures your heat map remains accurate and easy to interpret as data evolves.
- Refresh data sources and Table ranges regularly
- Document rule thresholds and color meanings in a reference cell
- Test new rows against existing rules to confirm automatic updates
- Archive older snapshots to preserve trend context
- Validate that color choices are accessible for color-blind viewers
FAQ
Reader questions
How do I keep the heat map updating when I add new rows?
Convert your data range into an Excel Table (Ctrl+T); conditional formatting and color scales will automatically apply to new rows added within the table.
Can I create a heat map for text categories like priority levels?
Yes, map each category to a numeric helper column (e.g., High = 3, Medium = 2, Low = 1) and apply color scales or icon sets to that helper column.
Why do my negative values show the wrong color in the heat map?
Adjust the rule type to two-color scale with a center point or three-color scale, and set distinct colors for negative, zero, and positive values in the formatting rules.
How do I exclude zeros from distorting the color distribution?
Create a custom rule using a formula such as =A1<>0 and apply it to the range, then set separate formatting for zero values to make them transparent.