What Does an Excel Heat Map Look Like?

An Excel heat map looks like a range of spreadsheet cells filled with different colors to show how values compare. For example, a table of monthly sales might shade lower figures pale yellow and higher figures dark green, making patterns visible at a glance without changing the underlying numbers.

The Basic Appearance of an Excel Heat Map

Most Excel heat maps look like ordinary tables with a color gradient applied to selected cells. Each cell keeps its value, but its background color reflects that value’s position in the range. A common example is a three-color scale: low values appear red, middle values yellow, and high values green. Other palettes use blues, oranges, or a single color that becomes more intense as values rise.

The colors usually blend gradually rather than forming sharply separated categories. A cell with a value near the middle of the range may look orange or yellow-green, while the lowest and highest values get the scale’s endpoint colors. If the range contains labels, those labels generally sit outside the colored cells, such as in row and column headers.

A heat map does not have to be a separate chart. In Excel, the most familiar version is a block of cells colored using Conditional Formatting. You can still read the numbers, sort or filter the table, and edit the values as usual. The color is an additional visual cue, not a replacement for the data.

A Small Example of What It Shows

Imagine a worksheet with sales by product and month. Rows list products, columns list January through March, and each intersecting cell contains a sales total. Applying a color scale across the numeric cells could make low-selling months look pale and high-selling months look dark. A quick scan might reveal that one product sells strongly in March while another is consistently weak.

The exact appearance depends on the selected cells and the rule. For instance, if the three monthly sales figures for one product are 20, 50, and 80, a low-to-high scale will assign progressively stronger colors. But if the same range also includes a figure of 800, the values 20, 50, and 80 may all appear close to the low end. The heat map communicates relative position within its comparison group, not necessarily whether a number is good or bad in an absolute sense.

This is why the layout matters. If you apply one scale to the entire table, Excel compares all selected values together. If you apply separate scales row by row, each product’s months are compared with one another. Those two choices can produce noticeably different colors for the same number.

How Excel Colors the Cells

Excel heat maps are commonly made with Conditional Formatting. A color-scale rule links cell values to colors between a minimum and a maximum. With a three-color scale, a midpoint color is added between the endpoints. Excel calculates each cell’s color from its value relative to the selected range and the rule’s settings.

That relative calculation is useful for spotting highs and lows, but it can be misleading if readers assume a shade has a fixed meaning. A dark green cell in one worksheet is not automatically equal to a dark green cell in another. The ranges may have different minimums and maximums. If you need colors to correspond to fixed thresholds, edit the rule and specify suitable values rather than relying only on automatic minimum and maximum settings.

Color scales generally apply to numeric values. Blank cells do not become meaningful low-value indicators simply because the range has a scale, and text entries are not treated like ordinary numeric measurements. Check the selected range before creating the rule so that headings, totals, or unrelated values do not distort the comparison.

How to Make One in Excel

In Excel for Microsoft 365 on Windows, select the numeric cells you want to compare, then choose Home > Conditional Formatting > Color Scales. Pick a two-color or three-color option from the gallery. Excel applies the rule immediately, so you can see the result and change the palette or settings if needed.

For more control, select the range and open Home > Conditional Formatting > Manage Rules. Select the relevant rule and choose to edit it. The formatting dialog lets you adjust the scale’s colors and the types or values used for its minimum, midpoint, and maximum. Menu labels and dialog layouts can vary slightly across Excel updates.

Select only the values you want compared; leave row and column headings out of the selection.
Choose a color scale that has enough contrast to distinguish its low, middle, and high areas.
Review whether the comparison should cover the whole table or separate rows or columns.
Check the result against the actual numbers, especially if the range contains outliers.

Excel for Mac also offers color scales through Home > Conditional Formatting > Color Scales, though the appearance of the ribbon and rule-editing dialog can differ. Excel for the web provides Conditional Formatting and color-scale options as well, with some differences in available editing controls. If the result needs carefully fixed thresholds or advanced rule management, desktop Excel may offer a more complete editing experience.

Choosing a Useful Color Scale

The best palette is one readers can interpret quickly and comfortably. A red-to-green scale is familiar in many settings, but red and green can be difficult for some people to distinguish. A blue gradient or another palette with clearly different lightness levels may be more accessible. Avoid choosing colors only because they look attractive; the important question is whether a reader can tell low, middle, and high values apart.

Think about the meaning of the data, too. If the values represent performance against a target, high may be favorable. If they represent defects, delays, or risk, high may be unfavorable. Excel’s default color scale does not know what the values mean. You can use a custom scale or a different conditional-formatting rule to make the visual convention match the subject, but include labels or notes if the interpretation might not be obvious.

A two-color scale works well when the main message is simply lower versus higher. A three-color scale is useful when a midpoint has a meaningful role, such as values near a target, with results below and above it shown in contrasting colors. If there is no meaningful midpoint, a three-color palette can imply a boundary that the data does not really have.

How to Read One Without Misjudging It

Use the colors to find patterns, then confirm them with the displayed values. A cluster of darker cells may suggest a strong region or a busy period, while a pale row may point to an area worth investigating. The color alone does not explain why a pattern exists, establish causation, or tell you whether a difference is important in practical terms.

Pay attention to the comparison range. If a heat map covers a full year, its colors compare values across that year. If each month or category has its own scale, the same shade may represent different numeric values in different parts of the table. When readers need to compare colors across groups, use a consistent scale and, where helpful, explain its thresholds.

Also look for outliers. One unusually large value can stretch an automatic scale so that many ordinary values appear nearly the same color. In that situation, consider setting fixed thresholds, using a scale that reflects meaningful percentiles, or presenting the outlier separately. Do not remove or alter data just to make a palette look more dramatic.

Where Heat Maps Are Useful

Heat maps are especially useful when a table has enough rows and columns that scanning every number takes time. Common examples include monthly sales by product, attendance by day and class, support requests by category and week, or survey scores by question and department. They can expose repeated patterns, abrupt changes, and unusually high or low cells.

They are less useful when a range has only a few values, when exact comparisons are the main task, or when the numbers have no sensible ordering. A colored table can also become confusing if it contains many unrelated measures with different units. For example, mixing percentages, dollar amounts, and counts in one scale would not create a meaningful comparison. Separate such measures or use a different visual approach.

Heat Maps Compared with Other Excel Visuals

A cell-based heat map keeps the data in its original grid and emphasizes comparisons within that grid. It is different from an Excel chart, which creates a separate visual object with axes, labels, and a plotted series. A chart may be clearer for showing a trend over time or comparing a small number of categories; a heat map is often better for spotting patterns across many intersections.

It is also different from a geographic map chart. A map chart colors geographic regions, such as countries or states, according to data associated with those locations. A Conditional Formatting heat map colors worksheet cells. Both use color to communicate values, but they represent different kinds of structure and are not interchangeable.

Common Problems and Practical Fixes

If every cell looks nearly the same, check whether the values are tightly grouped or whether one outlier has widened the scale. If the colors seem to imply the wrong result, verify the direction of the palette and whether high values are favorable in this context. If a header or total affects the colors, confirm that the rule applies only to the intended data cells.

When values change, conditional formatting can update the colors automatically. However, the rule may continue to cover an old range if new rows are added outside it. Review the rule’s “Applies to” range when you extend a table. Excel tables can make expanding data easier to manage, but it is still sensible to confirm that the formatting reaches newly added cells.

Finally, do not use color as the only way to communicate a critical result. Keep the numbers visible, choose colors with sufficient contrast, and add clear headings or a short explanation when the scale might be misunderstood. That makes the worksheet more useful both on screen and when printed in grayscale.

Frequently Asked Questions

Is an Excel heat map a chart?

Usually, no. The common Excel heat map is a Conditional Formatting rule that colors cells in a worksheet range. It keeps the data in the table rather than creating a separate chart object.

Can I see the numbers in an Excel heat map?

Yes. The values remain in their cells, and the color is applied as formatting behind the cell contents. You can still select a cell and inspect its value in the worksheet or formula bar.

Do darker colors always mean larger values?

No. That depends on the palette and rule settings. A scale can be reversed or use colors with a different meaning, so check the legend or the rule before interpreting it. Excel’s colors do not inherently mean good, bad, high, or low.

Why do the same values have different colors in two heat maps?

Each scale may be calculated from a different selected range or use different thresholds. A value can be near the top of one range and near the bottom of another. For direct comparisons, use consistent ranges and scale settings.

Can Excel make a heat map from a PivotTable?

Yes. You can apply conditional formatting to numeric values in a PivotTable, but check how the rule is set to apply as the PivotTable changes. A rule tied only to a fixed cell selection may not cover newly added or rearranged data as expected.

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.