Excel Heat Map Color Codes: How to Choose and Apply

Excel has no single set of required heat map color codes: a heat map is usually a Conditional Formatting color scale that assigns colors according to each cell’s value. To make one, select the values and choose Home > Conditional Formatting > Color Scales; to use particular colors, edit the rule and choose custom colors, such as pale yellow #FFF2CC and dark green #548235.

What Excel heat map color codes mean

A color code identifies a specific color, but it does not define what that color means in a spreadsheet. In a heat map, the rule assigns color based on the values in a selected range. A common design uses a light color for low values and a darker color for high values, making patterns easier to spot. Another design uses red for a negative or concerning result and green for a positive one.

Hex codes use a hash mark followed by six hexadecimal characters, two each for red, green, and blue. For example, #FFF2CC is RGB 255, 242, 204, a pale yellow; #548235 is RGB 84, 130, 53, a deep green; and #F4CCCC is RGB 244, 204, 204, a pale red. Those are color examples, not special Excel heat map codes. Excel’s conditional formatting rule compares cell values and maps them to chosen colors; it does not interpret the hex string as a value or category.

There is no universal Excel standard that says a particular hex color must represent a specific number, status, or risk level. The meaning comes from the rule, the data, and any legend or instructions you provide. A pale-to-dark single-hue scale can show magnitude without implying that one end is good and the other bad. A red-to-green scale suggests a judgment, so use it only when that interpretation fits the measure.

How a color scale works

A color scale evaluates values in its Applies to range and places each value along a gradient. A two-color scale has a low endpoint and a high endpoint; a three-color scale adds a midpoint. Excel calculates intermediate shades between the selected endpoint colors. The color shown in a cell therefore depends both on its value and on the scale’s thresholds.

For example, suppose a two-color scale covers monthly sales from 0 to 100,000. A cell near zero appears close to the minimum color, one near 100,000 appears close to the maximum color, and values between them receive blended shades. If the data changes, the minimum and maximum can change too, so a value may display a different shade even when that cell’s number has not changed.

Color scales are conditional formatting: they change how cells appear without changing the values stored in them. You can still sort, calculate, and chart the underlying numbers normally. The formatting is not a substitute for a numeric threshold or a written status, and color alone may not be apparent to every reader.

Apply a built-in heat map in Excel

Select the numeric cells to format. Include the full comparison range, but usually leave out headings and totals that should not influence the scale.
On Windows in Excel for Microsoft 365, choose Home > Conditional Formatting > Color Scales.
Choose a two-color or three-color preset. Excel applies the color scale to the selected cells.
Check that the darkest and lightest cells make sense for the data, and verify that a total row or outlier has not made the rest of the range look nearly identical.

To change or inspect the rule, choose Home > Conditional Formatting > Manage Rules. In the rules manager, select the relevant rule and choose Edit Rule. For a color scale, set the minimum, midpoint if present, and maximum types and values, then select the colors for those points. The available dialog labels can vary slightly by build. A custom color can be selected through the color control and More Colors; depending on the color dialog available in your Excel build, enter a hex value directly or use its RGB fields.

On Excel for Mac, the Conditional Formatting commands are also on the Home tab, though dialog layouts and some labels can differ. Excel for the web supports color-scale conditional formatting as well, but editing options may not match the desktop dialogs exactly. If you need precise custom color values or advanced rule control, use the desktop app and verify the finished workbook in the version your audience will use.

Choose useful colors and thresholds

The best palette depends on what the numbers represent and who needs to read them. Before choosing colors, decide whether the scale should communicate relative size, performance against a target, or a warning condition. A gradient should have a clear visual order and sufficient contrast, but it should not make small differences look more meaningful than they are.

For magnitude: Use a light-to-dark single-hue scale, such as pale blue to navy. This emphasizes more versus less without labeling one end as good or bad.
For values around a meaningful center: Use a three-color scale with a neutral midpoint, such as low red, middle cream, and high green. Set the midpoint to the actual benchmark when that is more informative than the arithmetic midpoint.
For a dashboard: Use restrained fills and keep the same meaning consistent across comparable tables. Add a short legend such as “lighter = lower” or name the threshold values.
For accessible reports: Avoid relying on red and green alone. Consider a single-hue scale, add data labels or status text, and check the result in grayscale or with a color-vision simulator.

Threshold selection matters as much as the colors. Excel can base scale endpoints on lowest and highest values, a number, a percentage, a percentile, or a formula, depending on the rule and endpoint. A Percent threshold places a point at a proportion of the numerical range; a Percentile threshold uses the value’s position in the distribution. These are not interchangeable. If a fixed target matters, use a numeric threshold or suitable formula rather than allowing the scale to move with the data.

For example, a weekly service rating from 1 to 5 may have a meaningful center of 3. If the data’s observed minimum and maximum happen to be 2.8 and 4.9, an automatically scaled three-color rule would place its midpoint based on the selected scale type, not necessarily at the rating 3. Set the midpoint explicitly if 3 is the boundary readers should interpret as neutral. A clearly stated target prevents a visually attractive gradient from implying the wrong benchmark.

Custom color codes and consistency

When several sheets or reports need the same appearance, record the chosen colors and their meanings in a small style guide. For example, you might specify low #F4CCCC, midpoint #FFF2CC, and high #D9EAD3, while noting whether high means better or simply larger. These sample colors are not a recommended universal scheme; check contrast and context before adopting them.

In Excel’s color picker, the custom color interface may offer RGB fields, a hex field, or both, depending on the Office build and the route used to open it. If you only see RGB, convert a hex code by reading each pair: #548235 corresponds to red 84, green 130, blue 53. Entering those components yields the same RGB color. Theme colors can also help maintain a workbook’s visual consistency, but a theme choice may look different if another workbook theme is applied.

Excel does not automatically create a human-readable legend for a color scale. Add one yourself when readers need to interpret the gradient, and label any fixed thresholds. A legend is especially useful when printing, sharing a static PDF, or handing a workbook to someone who did not create the rule. Also note that recipients may have a different display, theme, or printer, so subtle shades can reproduce differently.

Common problems and fixes

Most cells look almost the same: One extreme value may stretch the scale. Review the data, then set fixed endpoints or use percentile thresholds if that better fits the question. Do not remove a legitimate outlier just to improve the appearance.
Totals distort the colors: A grand total is often much larger than individual observations. Exclude totals and headings from the Applies to range, or create a separate rule for them.
Blank cells appear colored: Check the rule’s Applies to range and whether cells contain formulas returning a value such as zero or an empty string. A visually blank formula result is not always the same as an actually empty cell.
Colors change after adding data: Automatic minimum and maximum settings can recalculate from the expanded range. Choose fixed thresholds when comparisons across periods must remain stable.
Another rule overrides the scale: Open Home > Conditional Formatting > Manage Rules and inspect rules that apply to the same cells. Rule order and the Stop If True setting can affect other conditional formats, although a color scale is itself a distinct rule type.
The color code seems unavailable: A hex code is not typed into the worksheet cell to set a heat map. Edit the conditional formatting rule’s color controls. If the dialog lacks a hex field, use the equivalent RGB components.

To remove a scale without deleting values, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To edit the range instead, use Manage Rules and inspect the Applies to field. Be careful when clearing rules: clearing from the entire worksheet can remove unrelated conditional formatting as well.

Color scales versus other heat map methods

Conditional Formatting > Color Scales is usually the simplest option for a numeric heat map because Excel recalculates cell fills as data changes. It is suited to comparing values within a range, but its automatic gradient may not express fixed categories such as “below target,” “on target,” and “above target” clearly.

For discrete categories, use separate conditional formatting rules such as Highlight Cells Rules or rules based on a formula, assigning a fill to each category. That approach can make exact boundaries explicit, but requires more setup and careful rule management. A chart can be a better choice when readers need to compare a trend, see a legend, or understand the data without inspecting a table cell by cell. For a static presentation, manually colored cells are possible, but they do not update automatically when values change and can become misleading.

Choose the method that matches the question. A color scale is useful for quickly locating relative highs and lows. Explicit rules are better when a particular cutoff changes the interpretation. A chart is often more effective for a broad audience or a large data set. None of these methods changes the underlying data; they change how people see and interpret it.

Frequently Asked Questions

What are the standard Excel heat map color codes?
There are no mandatory standard codes. Excel offers built-in color-scale presets, and users can choose custom colors. Agree on a palette and explain what its colors mean if the workbook will be shared.

Can I enter a hex color code in Excel conditional formatting?
In the rule’s color picker, use a hex field if your Excel color dialog provides one. If it does not, enter the equivalent red, green, and blue values through the custom color controls. Do not enter the code into the data cell.

How do I make red mean low and green mean high?
Select the range, choose Home > Conditional Formatting > Color Scales, and choose a red-to-green preset with the desired direction. If the preset runs the opposite way, edit the rule and assign the minimum and maximum colors explicitly.

Why does the same value have a different color in another range?
Each rule usually scales values against its own Applies to range and thresholds. The same number can therefore occupy a different position in two ranges. Use shared fixed minimum and maximum values when you need a consistent comparison.

Do heat map colors change the cell values?
No. A color scale is formatting only. It changes the cell’s appearance according to its value; it does not replace, round, or otherwise alter the stored number.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.