Generate a Heat Map in Excel: Color Scales and Tips

How-to basics · Updated November 21, 2026

To generate a heat map in Microsoft Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colors each cell according to its value; use Home > Conditional Formatting > Manage Rules or New Rule to adjust the range and thresholds.

What an Excel heat map shows

A heat map uses color to make patterns in a table easier to spot. In Excel, the usual approach is conditional formatting: the values stay in their cells, while Excel changes the cells’ appearance based on rules. A three-color scale might show lower values in red, middle values in yellow, and higher values in green. The exact colors depend on the scale you choose.

This is useful for comparing sales by product and month, spotting high or low scores, reviewing response times, or identifying unusually large expenses. A heat map is not a separate chart or a special worksheet type. It is a formatting layer over data, so readers can still inspect and calculate with the underlying numbers.

Color scales are best for numeric values with a meaningful order. They are less suitable for text labels, unrelated categories, or data where color could suggest a ranking that does not exist. For example, coloring department names by alphabetical order would not communicate a useful measure.

Create a basic heat map with Color Scales

  1. Arrange the data in a table, with clear row and column headings. Keep the values you want to color in a contiguous block when possible.
  2. Select only the numeric cells to format. Leave headings and totals out unless you specifically want them included in the same scale.
  3. On the ribbon, choose Home > Conditional Formatting > Color Scales.
  4. Choose a preset. Excel applies the formatting immediately, with the colors reflecting the values in the selected range.
  5. Check the result against the actual numbers and labels. If the colors are difficult to interpret, choose another scale or set thresholds yourself.

For instance, if B2:D4 contains monthly sales for three products, selecting B2:D4 before choosing a scale colors the nine sales cells. The column and row labels remain unchanged. If one product has a much larger sales figure than all the others, the other cells may look almost alike because the scale must include that high value. That is a property of the chosen range, not evidence that the smaller values are identical.

On Excel for Mac, the Conditional Formatting and Color Scales commands are also available from the Home tab, though the ribbon layout and dialog appearance can differ. Excel for the web also offers conditional formatting and color scales; some editing controls may be arranged differently from the Windows desktop app.

Choose a color scale and set thresholds

Excel’s preset scales are a quick starting point, but the best choice depends on what the data means. A two-color scale can work well when the main distinction is low versus high. A three-color scale can emphasize a meaningful middle point, such as a target, a neutral score, or zero. Do not assume that red always means bad or green always means good: explain the meaning if readers might interpret the colors differently.

To customize the scale, select the cells and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose a rule for formatting all cells based on their values, then select a two-color or three-color scale. Set the minimum, midpoint if used, and maximum types and values, and select the colors. The exact choices shown depend on the selected scale.

  • Lowest value and highest value: Excel sets the endpoints from the smallest and largest values in the rule’s range. This is convenient for a quick overview, but the endpoints can change when the data changes.
  • Number: Use explicit numeric thresholds when a value has a fixed meaning, such as a target of 100 or a limit of 0. The colors then relate to those chosen values rather than simply to the current extremes.
  • Percent: This places a threshold at a percentage position across the range between its low and high endpoints.
  • Percentile: This uses a value’s rank within the distribution. It is useful when you want thresholds tied to how observations are distributed, rather than a fixed numeric amount.

Percent and percentile are not interchangeable. A threshold set to 50 percent is about a position across the scale’s numeric span; the 50th percentile is the median-ranked point in the data. When readers need to compare results over time, fixed numeric thresholds often make more sense than automatically recalculated endpoints.

Decide which cells should share a scale

The selected range determines what Excel treats as low and high. If you select an entire table of monthly sales, Excel compares every included cell with every other included cell. That is suitable when all the values share a common unit and you want to see the overall extremes. It can be misleading when each row or column should be judged against its own baseline.

Consider a table with sales for several regions. A high-volume region may have much larger figures than a small region. One shared scale can make the small region appear uniformly low, even if its month-to-month performance is strong. If the question is which months were strongest within each region, separate scales for each row may be more revealing. If the question is which region had the greatest sales in absolute terms, keep a shared scale.

Before applying formatting, decide what comparison you want a reader to make:

  • Use one scale for all cells when values are directly comparable across the whole table.
  • Use separate scales by row or column when the goal is to find patterns within each group.
  • Use a fixed midpoint when a target or neutral value matters more than the dataset’s average.
  • Exclude totals if they would dominate the scale and make the detail cells hard to distinguish.

Excel does not automatically create independent row-by-row scales just because a table has rows. A color scale rule applies to its defined range. To make comparisons within rows, create separate rules for the relevant row ranges or use another presentation design that makes the comparison clear.

Handle blanks, zeros, negatives, and unusual values

Check how the data’s special values should be understood before interpreting the colors. A blank cell may mean not recorded, not applicable, or zero; those meanings are different. A blank should not be silently treated by the reader as a low score. Consider leaving blanks unformatted, adding a note, or using a separate marker for missing data.

Zero is a numeric value and can be an important result. In a range containing only positive values, it may fall at or below the low end of the scale. If zero means a neutral point between positive and negative results, a three-color scale with its midpoint set to the number 0 can make the interpretation clearer. For example, a monthly profit-and-loss table can use one color for negative amounts, a neutral color at zero, and another color for positive amounts. Ensure that the units and currency are visible in the worksheet.

One unusually large or small value can stretch an automatic scale and reduce contrast among the rest. Check whether it is a data-entry error, a legitimate outlier, or a value that should be displayed separately. If it is valid but overwhelms the visual pattern, a fixed threshold or a separate view may be more honest than deleting it from the comparison. Do not remove a valid value merely to make the colors look more dramatic.

Manage, expand, or remove the formatting

To inspect or edit an existing rule, select a formatted cell and choose Home > Conditional Formatting > Manage Rules. In the Conditional Formatting Rules Manager, inspect the rule and its Applies to range. If the wrong cells are colored, correcting this range is often the key fix. The manager can show rules for the current selection or other worksheet scopes, depending on the setting in its display list.

If you add more rows or columns later, check whether the rule’s Applies to range includes them. Excel may extend formatting in some table-editing situations, but you should not assume that every pasted or inserted range will be covered. Update the range in Manage Rules when needed, and verify the colors after adding data or refreshing a report.

To remove conditional formatting without deleting cell contents, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose Clear Rules from Entire Sheet only when you intend to remove conditional formatting throughout that worksheet. These commands remove rules, not the underlying values.

Common problems and how to fix them

  • Everything looks nearly the same: The values may be tightly grouped, or one outlier may be stretching the scale. Review the minimum and maximum, then choose thresholds that reflect the purpose of the comparison.
  • Headings or totals are colored: The selected range included them. Edit the Applies to range in Manage Rules so it covers only the intended values.
  • The colors change after new data is added: An automatic scale recalculates from the values in its rule range. Set numeric thresholds if stable comparisons across updates are important.
  • A heat map seems to reverse meaning: Confirm which color represents the high end and which represents the low end. Do not rely on familiar color conventions without checking the rule.
  • Some numbers do not appear to affect the scale: Check that the cells contain numeric values rather than numbers stored as text, and confirm those cells are inside the rule’s Applies to range.
  • Colors are hard to read: Use a restrained palette, retain the displayed numbers, and avoid colors with poor contrast. Color should support the data, not replace labels or make exact values impossible to read.

Conditional formatting rules can also interact. If the same cells have several rules, the final appearance may depend on rule order and settings in the Rules Manager. Review the rules affecting those cells instead of adding another scale blindly. For a simple heat map, keeping one clear color-scale rule on the intended range makes maintenance easier.

Heat map versus an Excel map chart

A cell-based heat map and a geographic map chart solve different problems. A color scale shades cells in a table according to numeric values, whether those values represent sales, time, or scores. A map chart shades geographic regions, such as countries or states, based on location data and a measure. If you want to see which regions have higher sales on a map of the world, a geographic map chart may be appropriate. If you want to compare sales across products and months, conditional formatting is usually the simpler choice.

Likewise, a heat map is not the same as a chart with colored bars or a scatter plot. Its strength is showing patterns in a grid while leaving the numbers visible. Its limitation is that color can be difficult to compare precisely, especially for readers with color-vision differences or when printed in grayscale. Use a chart or a well-labeled table as an alternative when exact comparisons or accessibility are central to the task.

Frequently Asked Questions

Can I make a heat map in Excel without a chart?
Yes. Select the numeric cells and apply Home > Conditional Formatting > Color Scales. This colors the cells directly and does not create a chart.

Does Excel update a heat map when the values change?
Yes. Conditional formatting recalculates when values in the rule’s range change. With automatic minimum and maximum settings, the color scale can also shift as the range’s values change.

Can I make each row use its own heat map scale?
Yes, but a single rule across the whole table compares all cells together. Create separate color-scale rules for the individual row ranges if you want each row’s values compared within that row.

Can I use a heat map for negative numbers?
Yes. A color scale can format negative and positive values. For data where zero is the meaningful dividing point, set the midpoint to Number and enter 0, then choose colors that make the negative, neutral, and positive ranges easy to distinguish.

Why do my cells have different colors from someone else’s file?
The rule may use a different range, palette, or threshold settings. Automatic endpoints also depend on the values currently included in the range, so compare the rule’s settings as well as the data.

Make the result understandable

A useful Excel heat map starts with a clear comparison, not a color choice. Select the cells that belong together, choose whether the scale should be automatic or fixed, and make sure readers can still see the values and understand the direction of the colors. After applying the rule, inspect edge cases such as blanks, totals, and outliers. A restrained scale with an explicit purpose usually communicates more than a dramatic palette applied to an arbitrary range.

Related

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.