Can You Create a Heat Map in Excel? A Practical Guide

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, select your numeric cells and choose Home > Conditional Formatting > Color Scales to create a heat map. Excel colors values according to their position in the selected range; you can then adjust the colors and scale to make patterns easier to interpret.

What an Excel heat map shows

A heat map uses color to make differences in a set of values visible at a glance. In Excel, the common spreadsheet version is a range of cells colored with conditional formatting. A color scale connects each cell’s value to a color, such as pale yellow for lower values and dark green for higher ones. The cells and their numbers remain in the worksheet; the colors are an additional visual cue, not a separate chart.

This is useful when a table has enough rows or columns that comparing every number individually would take time. For example, a sales table with products in rows and months in columns can reveal seasonal dips or strong product lines. A class-score table can highlight high and low results, while a service dashboard might show response times by team and week.

A heat map does not explain why a value is high or low, and the colors do not replace the underlying data. It is a way to spot possible patterns and outliers, after which you can inspect the actual numbers and their context.

Create a basic heat map with Color Scales

For a straightforward heat map, apply a color scale to the numeric cells you want to compare. In Excel for Microsoft 365 on Windows:

  1. Arrange the data in a clear table, with labels outside the numeric range where practical.
  2. Select the cells containing the values to color. If you want to compare each month across several products, select the month-by-product values together.
  3. Choose Home > Conditional Formatting > Color Scales.
  4. Choose a two-color or three-color preset. Excel immediately applies the scale to the selected cells.
  5. Check whether the result emphasizes the pattern you intend. If not, undo the change or choose another scale, then consider setting custom scale points.

Conditional formatting changes a cell’s appearance based on its value. If a value changes, Excel updates its color; you do not need to recolor cells manually. The formatting is attached to the selected range, so confirm that you selected the correct cells before applying the scale.

In Excel for Mac, the equivalent command is available from Home > Conditional Formatting > Color Scales. Excel for the web also offers conditional formatting and color scales, though the ribbon layout and rule-editing options can differ from the desktop app. If you cannot find an option in the web version, open the workbook in the desktop application.

Choose a scale that fits the data

A color scale can change the way readers interpret the same numbers, so choose one that reflects the question the table is meant to answer. Two-color scales are often suitable when the main goal is to distinguish lower values from higher values. Three-color scales add a middle color, which can help separate low, middle, and high parts of a range.

For example, a red-to-green scale may be familiar for performance measures, but it can suggest that red is bad and green is good. That implication may not fit every measure. If you are showing error counts, response times, or risk levels, make sure the meaning of high and low is clear from the labels and surrounding explanation. A sequential palette that moves from light to dark can be less suggestive of a good-versus-bad judgment.

Think about these factors before settling on a preset:

  • Direction: Decide whether a larger number is favorable, unfavorable, or simply different.
  • Middle values: Use a three-color scale when a meaningful middle point helps interpretation, rather than adding a midpoint just for decoration.
  • Accessibility: Choose colors with adequate contrast and do not rely on color alone. Keep labels and values visible, and explain what the colors represent.
  • Comparability: If readers will compare separate tables, use consistent scale limits and colors where the data allows it.

Set custom minimum, midpoint, and maximum values

The default scale uses the values in the selected range to determine its low and high ends. That is convenient for a quick scan, but it can make two separate ranges look comparable when they are not. If one month’s values run from 10 to 30 and another month’s run from 40 to 60, a default scale may color the relative highs similarly even though the actual values differ.

To customize a scale, select the range and choose Home > Conditional Formatting > Manage Rules. Select the relevant rule and choose Edit Rule, or choose New Rule if you are creating one. Under Format Style, choose Color Scale. For the minimum, midpoint, and maximum, you can choose a type such as Lowest Value, Highest Value, Number, Percent, or Percentile where applicable, and set the corresponding colors. The available fields depend on the scale and rule settings.

Use a fixed number when the scale needs to mean the same thing across reports. For example, if a 100-point score has a meaningful midpoint of 70, setting that point explicitly can make the visual consistent from one group to another. A percentile midpoint instead reflects the distribution of the selected data; it identifies a position in that distribution, not a particular score. Percent and Percentile are therefore not interchangeable choices.

After editing, check the rule’s Applies to range in Manage Rules. A scale applied to the wrong cells, or to only part of a table, can produce misleading colors. If the data grows, update the range or verify that the new rows are included.

Example: compare monthly sales by product

Suppose a worksheet has products in rows and months in columns, with each cell containing a monthly sales total. Select only the sales values, not the product names or month headings, and apply a three-color scale. If the goal is to spot each product’s strongest and weakest months, applying the scale across each product’s row can make within-product changes stand out.

If instead the question is which products had the highest sales in a particular month, compare values down each month’s column. These are different comparisons, and applying a single scale to the whole table answers a third question: which cells are high or low relative to every selected value. The choice of selection range determines the comparison group.

Consider a small example: if a product’s monthly totals are 12, 18, and 30, a row-based scale makes 12 the low end and 30 the high end for that product. A whole-table scale could assign 12 a different color if other products have values far below or above it. Neither approach is inherently wrong; select the range that matches the intended comparison and state the units, such as dollars or units sold.

Prepare the data and avoid misleading results

Before applying colors, check that the values are genuinely numeric and use consistent units. Numbers stored as text may not behave like numbers in calculations or conditional formatting. Dates are stored as numeric serial values in Excel, so a color scale applied to dates can color them according to their numeric order, but that may not be the visual message you want. Use a scale only when the ordering of the underlying values is meaningful.

Review blanks, zeros, negative values, and unusually large values. A zero may mean no activity, or it may mean the measurement is exactly zero; those meanings should not be confused. A single extreme value can stretch the range and make most other cells appear nearly the same color. If the outlier is valid, consider whether a fixed scale or a separate outlier indicator would communicate the table more clearly. Do not remove valid values merely to improve the appearance.

Also decide whether the labels belong in the selection. In most tables, leave headings and text labels unselected so that the scale applies to the data only. If a table contains subtotals or totals, decide whether they should be part of the same comparison. A grand total can be much larger than individual entries and flatten the color differences among the entries.

Common problems and how to fix them

  • Every cell looks almost the same: The values may be tightly clustered, or one extreme value may be stretching the scale. Check the actual numbers and consider setting meaningful fixed limits.
  • The colors seem reversed: Edit the rule and inspect which color is assigned to the minimum and maximum. Confirm that the direction matches the meaning of the metric.
  • Headings or blank-looking cells are colored: Review the selected range and the rule’s Applies to setting. A cell that looks blank may contain a formula returning an empty string or other content, so inspect the cell if its formatting is unexpected.
  • New rows have no heat-map colors: The conditional-formatting rule may not include them. Extend the Applies to range or reapply the rule to the expanded data.
  • Several rules compete: Open Home > Conditional Formatting > Manage Rules and inspect rules that apply to the same cells. Remove or revise redundant rules and confirm their order and ranges.

When you filter or sort a table, the colors remain attached to the cells according to the conditional-formatting rule. That makes the display responsive to the values, but it is still worth checking the rule scope after reorganizing data, especially if the range has been copied or expanded.

Heat map versus an Excel Map chart

A spreadsheet heat map and an Excel Map chart are different features. A color-scale heat map colors cells in a grid, such as a table of products and months. A Map chart displays geographic regions using location data, such as countries or states. If you want to show sales intensity by region on a geographic map, look for the Map chart feature rather than applying a color scale to a table.

Likewise, a color scale is not the same as a dedicated chart type that displays values as colored squares. Excel’s conditional formatting is often enough for a compact table, but a large or presentation-focused visualization may be easier to read in a charting or business-intelligence tool. The right option depends on whether readers need exact cell values, geographic context, or a more polished visual summary.

Frequently Asked Questions

Can I create a heat map in Excel without a macro?
Yes. Conditional Formatting > Color Scales creates a heat map without VBA or a macro. You can edit or remove the rule later through Manage Rules.

Can a heat map update when the numbers change?
Yes. Conditional formatting recalculates the colors when values in the formatted range change. If you add rows beyond the rule’s Applies to range, update that range so the new cells are included.

Can I make a heat map from a PivotTable?
Yes. You can apply conditional formatting to PivotTable values, but check the rule’s scope after refreshing or changing the layout. PivotTables can expand or rearrange, so confirm that the intended value cells still receive the formatting.

Can Excel make a heat map from text labels?
Color Scales are designed to compare numeric values, not categories such as High, Medium, and Low. For text labels, use conditional-formatting rules that match specific text or use a consistent numeric score if the categories have a genuine ordered meaning.

Can I compare two heat maps fairly?
Yes, but use the same scale limits and color meaning in both ranges when direct comparison matters. Automatic scales can assign similar colors to different numeric values because each range is scaled independently.

A useful Excel heat map starts with a clear comparison, a correctly selected range, and a scale whose colors have an understandable meaning. Apply Color Scales for a quick result, then inspect the rule and data before relying on the pattern to guide a decision.

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.