In Excel for Microsoft 365 on Windows, select the numeric cells in your table, then choose Home > Conditional Formatting > Color Scales and select a preset. Excel colors each cell according to its value, turning the table into a heatmap that makes patterns and outliers easier to spot.
What an Excel heatmap table shows
An Excel heatmap table is a range of cells whose fill colors represent the values they contain. It is usually made with conditional formatting, so the colors respond when the numbers change. A darker or more saturated shade might indicate a larger value, for example, while a contrasting shade marks a smaller one. The colors are a visual layer on top of the data, not a replacement for the numbers.
Heatmaps are useful when a table has many comparable values and the pattern matters more than reading each entry individually. A monthly sales table with products in rows and months in columns can show seasonal peaks at a glance. A service team might use one to compare response times by weekday and hour, or a teacher might use one to spot score patterns across questions and students.
In Excel, “heatmap” generally describes a formatting technique rather than a separate chart type. A color scale applied to cells is the most common approach, though formula-based conditional formatting can create other heatmap-like effects. The table remains editable and can still be sorted, filtered, or used in calculations.
How color scales work
A color scale compares the numeric values in the range covered by a conditional-formatting rule. A two-color scale maps the lower end of the range to one color and the upper end to another, blending colors for values between them. A three-color scale adds a midpoint color, which is useful when the center has a meaningful interpretation, such as zero, a target, or the middle of a distribution.
With Excel’s default scale, the minimum and maximum are usually based on the smallest and largest values in the selected range. That means a value’s color depends on the other values it is being compared with. If the largest value changes, the scale can shift and the same number may receive a different shade. A color does not, by itself, communicate a fixed amount or a pass/fail threshold.
For example, in a range of monthly sales from 40 to 100, 70 will appear near the middle of the scale. If a later month has sales of 500 and it is included in the same range, most other values may appear much closer to the low end. This is why range selection and scale settings matter as much as choosing attractive colors.
Create a heatmap with Color Scales
For a basic heatmap, keep row and column headings outside the selected numeric range. On Windows in Excel for Microsoft 365:
- Select the cells containing the values to compare. For a matrix, include the full block of numbers but not its labels.
- Choose Home > Conditional Formatting > Color Scales.
- Preview the available presets and select a two-color or three-color scale.
- Check the result against the actual values, including the lowest and highest numbers, to confirm that the contrast communicates the pattern you want.
For instance, suppose B2:E6 contains five products’ sales across four months, with product names in column A and month names in row 1. Selecting B2:E6 and applying a scale compares all 20 sales figures together. The column and row headings remain readable and uncolored. If the goal is to compare each month only with its own values, apply a separate scale to each month’s column instead.
On Excel for Mac, select the range and use Home > Conditional Formatting > Color Scales; the menu may look different from the Windows ribbon, but the same basic feature is available in current Excel versions. Excel for the web also supports conditional formatting, although some rule-editing options and dialog layouts can differ from desktop Excel.
Choose a scale that fits the question
Decide what comparison the colors should support before choosing a palette. If you want to identify the highest and lowest values in one group, a two-color gradient is straightforward. If values below and above a meaningful center should look different, use a three-color scale. For example, a scale centered on zero can distinguish negative from positive changes, while a scale centered on a target can show which results fall short and which exceed it.
- Use one scale across the full matrix when cells need to be compared with one another across rows and columns.
- Use separate scales by column when each column represents a different group and the within-group pattern matters more than absolute comparisons between groups.
- Use a fixed midpoint or bounds when the same color should consistently mean the same value across reports or reporting periods.
- Choose colors with readable contrast and avoid relying on a red-versus-green distinction alone, which may be difficult for some readers to distinguish.
Separate scales make local highs and lows easier to see, but they can also mislead if readers compare colors between groups. A dark cell in one column may represent a smaller number than a pale cell in another when each column has its own scale. State the comparison basis in a nearby note or label when that distinction matters.
Customize minimums, midpoints, and maximums
A preset is quick, but Excel lets you configure the scale when automatic minimums and maximums are not suitable. Select a cell in the formatted range, then choose Home > Conditional Formatting > Manage Rules. Select the relevant rule, choose Edit Rule, and use the color-scale settings to set the minimum, midpoint, and maximum types, values, and colors. The dialog’s choices can vary with the selected scale type.
For example, a three-color scale for percentage results could use 0% as a fixed minimum, 50% as a fixed midpoint, and 100% as a fixed maximum. This gives the colors a stable interpretation across different groups, assuming the underlying percentages are stored as numeric values from 0 to 1 and displayed as percentages. If the data are on a different scale, set bounds that match the actual values.
Excel’s scale types include value-based and distribution-based choices such as lowest value, highest value, number, percent, percentile, and formula. A percentage setting is not necessarily the same as a percentile: the former relates to a percentage of the range between its endpoints, while a percentile is based on the values’ position in the distribution. Use fixed numeric bounds when a value has a clear business meaning; use automatic bounds when the purpose is simply to reveal relative variation within the current data.
Read a heatmap without losing the numbers
A heatmap is best treated as an overview, not as a substitute for exact values. Keep the numbers visible, use an appropriate number format, and add a legend or short explanation when the meaning of the colors is not obvious. If a table shows response times, for example, label whether darker colors mean slower or faster responses; readers should not have to guess whether “hot” means good or bad.
Look at the scale endpoints and midpoint before drawing conclusions. A color can look dramatic even when the numerical difference is small, particularly when the range is narrow. Conversely, one extreme value can compress the colors assigned to the rest of the data. Consider checking the table alongside a sorted list, summary statistics, or a chart if the exact size of differences is important.
Common problems and how to fix them
- The colors barely differ: One extreme value may be stretching the scale. Review the data, adjust the bounds, or use a percentile-based scale if that better matches the intended comparison.
- Headings or unrelated cells are colored: Open Home > Conditional Formatting > Manage Rules, select the rule, and check the Applies to range. Edit it so it covers only the intended numeric cells.
- Some numbers do not receive expected colors: Check whether the entries are genuine numbers rather than text that looks numeric. Imported values can contain spaces or other characters. Also inspect blanks, error values, and formulas returning text rather than numbers.
- Zero looks like a low value: Zero is a numeric value, not a blank. If zero represents a special state, use a rule that gives it an explicit treatment or adjust the scale so its color has the intended meaning.
- The colors change after adding data: A scale based on lowest and highest values recalculates as the range’s data changes. Set fixed bounds if consistent comparisons over time are essential, and confirm that new rows are included in the Applies to range.
- Rules conflict or unexpected formatting appears: Review the rules for the affected cells in Manage Rules. Multiple conditional-formatting rules can apply to the same range, so remove obsolete rules or adjust their ranges and order as appropriate.
For Excel Tables, a conditional-formatting rule may extend when rows are added within the table, but do not assume every paste or import will expand its range correctly. After adding data, check Manage Rules and confirm that the Applies to range includes the new cells. This is especially useful for recurring reports.
Heatmaps compared with other Excel formatting
Color Scales are a good fit when values form a continuum and relative magnitude matters. Data Bars, available under Home > Conditional Formatting > Data Bars, show an in-cell bar whose length represents a value; they can make size comparisons easier while leaving the number visible. Icon Sets use symbols to group values into categories, which may be clearer when the reader needs broad status bands rather than a smooth gradient.
For a fixed threshold, such as marking every result below a target, a conditional-formatting rule such as Highlight Cells Rules is often more appropriate than a scale. A scale continuously changes colors according to the range, so its colors are not a reliable way to enforce a threshold. Manual cell fills are another option for a one-off illustration, but unlike conditional formatting they do not automatically respond when values change.
Make the heatmap easier to maintain
Use a consistent range, keep headings separate, and document what the colors mean. If readers compare several periods, keep the scale bounds consistent across those periods instead of letting every period select its own minimum and maximum. Otherwise, the same shade can represent different values in different sections and create a false impression of comparability.
Keep the underlying data clean and preserve a copy of the unformatted values when preparing reports. Avoid using color as the only signal: retain labels and numbers, and consider a palette that remains distinguishable in grayscale or for people with color-vision differences. Before sharing or printing, check that the cells are still legible at the intended display size and that the formatting does not obscure negative signs, decimals, or percentage symbols.
Frequently Asked Questions
Can Excel make a heatmap automatically?
Yes. Select numeric cells and choose Home > Conditional Formatting > Color Scales. Excel applies a color gradient based on the values in the rule’s range; you can edit the rule if the automatic scale does not fit your purpose.
Can I make a heatmap with different colors for positive and negative numbers?
Yes. Use a three-color scale and set its midpoint to zero, with one color for negative values and another for positive values. If the values should be classified using exact thresholds rather than a gradient, use separate conditional-formatting rules instead.
Why do the colors change when I add a new value?
Automatic color scales use the minimum and maximum in their applied range. A new low or high value can shift the scale for every cell. Set fixed bounds in Edit Rule when the same color needs to represent the same value consistently.
Can I apply a heatmap to only part of a table?
Yes. Select the specific numeric cells before applying Color Scales, or edit the rule’s Applies to range through Manage Rules. Keeping headings outside that range helps prevent labels from receiving unintended formatting.
Can I use a heatmap in an Excel PivotTable?
Yes, conditional formatting can be applied to PivotTable values. When creating or editing the rule, check its scope so it applies to the intended value field and behaves appropriately as the PivotTable changes or refreshes.
Related
- Excel Continually Not Responding? Causes and Fixes
- Excel Named Range to Table: What Works and What Doesn’t
- Excel Consolidated Sum: Combine Data Across Sheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Citation Footnotes in Word and Format ThemTo add a citation footnote in Microsoft Word, place your cursor where the footnote marker should appear, choose…
- Excel Protect Sheet: How It Works and How to Use ItIn Excel, Protect Sheet restricts what people can change on a worksheet, chiefly by preventing edits to cells…
- Excel Data Validation Source: Lists, Ranges, and RulesIn Microsoft Excel for Microsoft 365 on Windows, the Data Validation Source is the field that tells a…
- Excel Text to Columns: Split Names, Dates, and Other DataIn Excel for Microsoft 365 on Windows, select the cells in one column, then choose Data > Text…
- How to Wrap Text in Google Sheets on a PC: A GuideTo wrap text in Google Sheets on a PC, select the cell or cells, then choose Format >…
- Excel Page Break Shortcut: Insert, Remove, and ResetIn Excel for Microsoft 365 on Windows, press Alt+P, B, I to insert a page break: press the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.