Excel does not automatically add a legend to a heat map made with Conditional Formatting color scales. To explain the colors, create a small key beside the data and make sure its labels and colors reflect the heat map’s actual thresholds; on Windows, start with Home > Conditional Formatting > Manage Rules.
What a heat map legend means in Excel
A heat map uses color to show how cell values compare. A legend, also called a key, tells readers what the colors represent: for example, red for lower scores, yellow for middle scores, and green for higher scores. Without that explanation, a reader may not know whether red means a good result, a warning, or simply a low number.
In Excel, the common way to make a cell heat map is a Conditional Formatting color scale. The scale assigns colors based on the values in its applied range. A two-color scale blends between two colors; a three-color scale includes a middle color as well. The color is a visual cue, not a replacement for the underlying number or its units. A useful legend therefore names the measure and provides meaningful labels or values, such as Low, 50, and High.
Does Excel add a legend automatically?
No. A color scale formats worksheet cells, but it does not create the kind of automatic legend commonly found beside a chart. This is a frequent source of confusion: a worksheet heat map and a chart heat map are not the same feature. A chart may have its own legend when its series are encoded by color, while a cell range formatted with a color scale has no built-in key.
You can make a key using ordinary worksheet cells, text boxes, or a small table. Worksheet cells are usually easiest to keep aligned with the data and to copy with the sheet. Excel’s standard color scale also does not print its numeric breakpoints in the cells, so the legend needs to state those breakpoints if readers need exact interpretation.
How to make a manual legend
For a simple three-color heat map, create a compact key in unused cells near the table. The example below assumes that the data are scores from 0 to 100 and that the colors run from red through yellow to green.
- Choose a place for the key and add a heading such as Score. Put labels such as Low, Middle, and High in adjacent cells.
- In a second row, enter the scale’s representative values: 0, 50, and 100. These are illustrative thresholds; use the values that make sense for your data.
- Select the three numeric cells, then choose Home > Conditional Formatting > Color Scales and select the same three-color scale used for the data.
- To make the colors match reliably, edit the heat map’s rule and the key’s rule so both use the same color scale, colors, and numeric minimum, midpoint, and maximum. For this example, set them to 0, 50, and 100.
- Format the key for readability. Widen columns if labels are clipped, use a visible border, and make clear which number belongs to which color.
When the scale uses fixed numeric thresholds, the three sample values in this example represent its endpoint and midpoint colors. For a more gradual key, use several sample values between the minimum and maximum, apply the identical scale rule to those values, and place them in order. This makes a stepped swatch rather than a continuous gradient, but it can communicate the transition more clearly than three blocks.
Set the color scale’s thresholds
A legend can only be accurate if the heat map’s rule is understood. Select a cell in the formatted range and open Home > Conditional Formatting > Manage Rules. In the Rules Manager, check that Show formatting rules for is set to the current selection or the relevant worksheet. Select the color-scale rule and choose Edit Rule. The dialog lets you choose a two-color or three-color scale and configure the colors and threshold types.
For a three-color scale, Excel can base the endpoints and midpoint on choices such as lowest value, highest value, a number, a percent, a percentile, or a formula, depending on the point being configured. Those options do not all mean the same thing. A midpoint set to 50 percent is not necessarily the numeric value 50: it places the midpoint according to the range’s distribution or relative position, rather than declaring that the score 50 is the central threshold. If readers need a fixed cutoff, choose Number and enter that cutoff.
For instance, a monthly satisfaction score from 0 to 100 might use numeric thresholds of 0, 50, and 100. A table of response times might instead use a red high end because longer times are undesirable. Pick the direction based on the meaning of the measure, not on a general assumption that green is always good. Document the units and cutoff values in the key when those details affect decisions.
Make the legend match the formatted range
There are two common approaches to a matching key. One is a sample-value key: enter example numbers and apply the same conditional formatting scale. The other is a label-and-swatch key: place colored cells beside text such as Under 50, 50, and Over 50. The sample-value method is less likely to drift from the scale because the swatches are colored by the rule itself. The label-and-swatch method can be clearer when readers need categories, but you must update its fills when you change the rule.
Compare the rule settings rather than relying on a color that merely looks similar. In Manage Rules, confirm that each rule applies to the intended cells and that the scale type, threshold types, threshold values, and colors agree. If the heat map uses automatic lowest and highest values, the colors depend on the range’s current data. A key using fixed numbers may then misrepresent the scale. Either use matching fixed numeric thresholds for both ranges or label the key clearly as relative to the displayed data.
Also check whether the data range changes over time. If new rows are added outside the Applies to range, those cells may remain unformatted and the legend will not describe them. Update the rule’s Applies to range in the Rules Manager when appropriate, and verify that the key’s sample cells still correspond to the configured thresholds.
Read the colors without overinterpreting them
A color scale communicates relative position, not necessarily significance. If a scale is based on the lowest and highest values in the selected range, a cell can look strongly red or green even when the underlying difference is small. Add the numeric values, units, or explicit threshold labels when the magnitude matters. For a report, a title such as On-time delivery rate (%) and a key showing the cutoffs are more informative than colors alone.
Color also has practical limits. Some readers have difficulty distinguishing particular color combinations, and printed colors may differ from on-screen colors. Do not make color the only signal: keep the original values visible, use descriptive labels, and consider choosing a palette with distinct lightness as well as hue. A text note can explain what higher and lower values mean in plain language.
Common problems and how to fix them
- The key has different colors from the data. Check that both ranges use the same scale colors and threshold settings. A separate automatically scaled rule may calculate colors from the key’s own values instead of the heat map’s data.
- The colors change when values are added or removed. Lowest Value and Highest Value settings recalculate from the applied range. Use fixed numeric thresholds if the meaning of each color should remain stable, or state that the scale is relative.
- Everything looks nearly the same color. The values may be tightly grouped compared with the scale’s full range, or a threshold may not fit the data. Review the actual numbers and choose meaningful fixed breakpoints where appropriate.
- Some cells are not colored. Confirm that they fall inside the rule’s Applies to range and contain numeric values. Text entries and blanks do not behave like ordinary numeric values in a color scale.
- The legend’s meaning is unclear. Add a measure name, units, and direction. Say whether green means a higher value or a better outcome; those meanings are not interchangeable.
When a different display may work better
A cell color scale is useful for spotting patterns across a matrix, such as sales by product and month or scores by team and category. It is less suitable when readers need exact category counts, a precise continuous color ramp, or a chart-style legend. For fixed categories such as Red, Amber, and Green status, separate conditional formatting rules based on explicit cutoffs can make the categories easier to explain. For a trend over time, a line chart may show the pattern more clearly than colored cells.
If the key is for a report or dashboard, test it at the size and output where it will be read. A compact key that looks obvious on a large monitor may become hard to read in a printout. Keep the scale consistent across comparable tables when readers are expected to compare them; if each table uses its own range, say so because the same color can represent different values in different tables.
Frequently Asked Questions
How do I add a legend to an Excel heat map?
Create a small key from worksheet cells beside the formatted range. Add labels and representative values, then apply a color scale with the same colors and thresholds as the heat map. Excel does not generate this key automatically.
Can I show exact numbers in a heat map legend?
Yes. Include the actual cutoff values in the key, such as 0, 50, and 100, and set the color-scale thresholds to those same numbers. If the rule is based on lowest and highest values, the breakpoints can change as the data changes, so a fixed-number legend may not stay accurate.
Why does my Excel heat map change color when I add data?
If the rule uses Lowest Value or Highest Value, Excel recalculates the scale from the values in its applied range. New values can shift the mapping. Use fixed numeric thresholds for stable cutoffs, or explain that the colors show relative values within the current range.
Can I make a gradient legend in Excel cells?
You can create a stepped key by putting a sequence of numeric sample values in cells and applying the same color-scale rule and thresholds as the data. The cells show discrete steps rather than one continuous legend bar.
Does a heat map legend work in Excel for Mac or the web?
The same general idea applies: use a separate key and align it with the conditional-formatting rule. Ribbon labels and rule-editing controls can differ by platform and release. The Windows Microsoft 365 path described here is Home > Conditional Formatting > Manage Rules; check the corresponding Conditional Formatting controls in your Mac or web version.
Related
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Make a Trendline Solid in Excel and Fix DashesTo make a trendline solid in Excel for Microsoft 365 on Windows, select the trendline, press Ctrl+1, open…
- Add Power Pivot to Excel: Enable It and Get StartedTo add Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
- Excel Column Chart: Make Columns Wider and Improve SpacingTo make columns wider in an Excel column chart, select a column, open Format Data Series, and reduce…
- Project Gantt Chart Template Excel: Build and Use OneA project Gantt chart template in Excel is a task list paired with a date-based grid: enter each…
- Excel Gantt Chart With Dependencies: How to Build OneYes. In Excel for Microsoft 365 on Windows, you can build a Gantt chart whose task dates update…
- Format Trendline in Excel: Steps and OptionsTo format a trendline in Excel for Microsoft 365 on Windows, select the chart, choose Chart Design >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.