In Excel, heat mapping usually means using Conditional Formatting color scales to shade cells according to their values. Select the numeric range, then choose Home > Conditional Formatting > Color Scales and pick a palette; use a custom rule when you need fixed thresholds or a particular midpoint.
What an Excel heat map shows
A heat map turns a table of numbers into a visual pattern. In a typical Excel worksheet, low values receive one color, high values another, and values between them are shaded along a gradient. A three-color scale can add a midpoint color, which helps distinguish below-average, middle, and above-average values at a glance.
The color is a visual layer, not a change to the underlying data. Cells still contain their original numbers, which can be selected, calculated, sorted, or copied. This makes color scales useful for scanning a large table without replacing its values with labels such as low, medium, and high.
For example, a manager might color a grid of monthly sales by product. Darker green cells could indicate stronger sales and pale red cells weaker sales. The pattern can reveal a product that is consistently slow, a strong month across many products, or an unusual value worth checking. It does not, by itself, explain why that pattern exists.
How to create a basic heat map
In Microsoft Excel for Microsoft 365 on Windows, create a basic color-scale heat map as follows:
- Select the cells containing the numbers you want to compare. Avoid selecting headers and totals unless you specifically want them included in the scale.
- Open Home > Conditional Formatting > Color Scales.
- Choose a two-color or three-color preset. Excel applies the formatting immediately, with the shades determined by the values in the selected range.
- Check the result against the data and adjust the selection or rule if the colors do not communicate the comparison you intended.
For a custom scale, select the range and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Format all cells based on their values, then select a two-color or three-color scale. Set the colors and the types and values for the minimum, midpoint if present, and maximum. Choose OK to apply the rule.
On Excel for Mac, the Conditional Formatting and Color Scales commands are also available from the Home tab, though dialog details can vary slightly by release. Excel for the web also offers color-scale conditional formatting; the layout and available rule-editing controls may differ from the desktop app.
How Excel assigns colors
With a default color scale, Excel evaluates the selected values and maps them across the scale. In a two-color scale, the minimum and maximum values anchor the two ends. A three-color scale adds a midpoint, which can be set to a particular value or interpreted using a type such as percent or percentile. Values between the anchor points receive interpolated shades.
The scale is relative to its rule’s range unless you set meaningful fixed thresholds. If the smallest value is 40 and the largest is 90, those values occupy the ends of the scale, even if both are considered good results in the real-world context. Add a new extreme value and the other shades may shift because the range has changed.
In a custom rule, the available threshold types let you define how Excel identifies scale points. A number sets a specific value; a percentile uses a position in the distribution of the selected values; and percent defines a position within the range between the minimum and maximum. These are not interchangeable. A percentile is useful when you want colors to reflect rank within a skewed distribution, while fixed numbers are often clearer when there are known business targets.
Choosing a scale that fits the data
Choose colors to support the meaning of the numbers, not just to make the sheet look vivid. A sequential scale, moving from a light shade to a darker shade of one color, works well when low-to-high is the only important direction. A diverging scale, with different colors on either side of a midpoint, is useful when values above and below a meaningful boundary have different interpretations.
For example, a three-color scale centered on zero can make positive and negative changes easy to distinguish. A scale centered on a sales target can show which results fall short, meet, or exceed it. If the midpoint is merely the arithmetic middle of the observed minimum and maximum, however, it may not correspond to a meaningful performance threshold. Set the midpoint deliberately when the business meaning matters.
- Use a simple palette with clearly distinguishable shades.
- Use a diverging palette when the midpoint has a real meaning, such as zero or a target.
- Keep the same palette and thresholds wherever readers are expected to compare tables.
- Do not rely on color alone to convey an important warning or decision.
A concrete example: monthly product sales
Imagine a worksheet with products in rows, months in columns, and sales values in the body of the table. Select only the sales cells and apply a three-color scale. If the purpose is to compare each month’s results with a fixed target, configure the midpoint or a boundary around that target rather than accepting a midpoint that changes with the data. A warm color for low results, a neutral shade near target, and a cool color for high results can make the table quick to scan.
Consider two products with monthly sales of 48 and 52 when the target is 50. A scale centered on 50 can show these values as slightly below and slightly above target. By contrast, if the selected table ranges from 10 to 200, a default scale may make 48 and 52 look almost identical, even though they fall on opposite sides of the target. The right configuration depends on the question: relative ranking across the observed table or performance against a stable benchmark.
Before applying the rule, decide whether to include totals. A grand total is usually much larger than an individual monthly value and can dominate the scale, making the detail cells look nearly the same color. The same problem can occur when the selection includes a header, an annual total, or a different kind of measure. Keep comparable values together and separate unlike quantities.
Heat maps compared with other Excel visuals
Color scales are one option among Excel’s conditional formatting tools. Data Bars place an in-cell bar behind or alongside a value, which can make relative magnitude easier to compare while preserving a visible zero-to-value length. Icon Sets add symbols such as arrows or traffic-light icons to group values into categories. These tools answer different visual questions, and can also be used in combination if the sheet remains readable.
A heat map is often best when the data forms a matrix and the reader needs to spot clusters or outliers. Data Bars can be more intuitive in a short list where direct comparisons matter. A chart is often a better choice when the reader needs to understand a trend over time, compare a small number of series, or see a relationship between variables. A color scale does not replace a chart when the order of time or the shape of a trend is important.
Excel’s Filled Map chart is also different from a cell heat map. It displays geographic regions based on location data, rather than shading worksheet cells according to numeric values. Find it from Insert > Maps > Filled Map when the data is geographic and Excel can recognize the locations.
Comparing separate tables fairly
Two heat maps can look similar while using different scales, so their colors may not represent the same values. If Excel calculates each range’s minimum and maximum independently, the darkest shade in one table means that table’s maximum, not necessarily the same number as the darkest shade in another. This is helpful for finding patterns within each table but misleading if readers compare colors across periods, teams, or departments as though they shared a common scale.
For a fair comparison across several periods, use the same scale boundaries in each rule. For example, if every table reports a percentage with a meaningful range from 0 to 100, set the scale to those fixed endpoints instead of letting each period choose its own extremes. If the values are measured in units with an agreed target, use consistent thresholds based on that target. State the units and threshold meaning near the table or in a legend.
When you add rows or columns, check that the conditional-formatting rule covers the new cells. Excel may extend formatting as you edit a range, but the exact result depends on how the cells and rules were created. Use Home > Conditional Formatting > Manage Rules to review the rule’s range and adjust it when necessary. For a structured Excel Table, confirm that new rows receive the intended formatting rather than assuming the rule will always cover them.
Common problems and how to fix them
If the entire range appears nearly the same color, first check whether the values are genuinely close together. Then look for an outlier, a total, or an unintended cell in the selection that is stretching the scale. Excluding unrelated cells or setting fixed thresholds can make the meaningful differences easier to see. If values are stored as text rather than numbers, Excel may not treat them as expected; verify the cell contents and number format.
If the colors change after you add data, Excel may be recalculating a relative scale using the updated minimum and maximum. That is normal for an automatically scaled range. Use fixed numbers or percentiles when you need greater consistency, and review the rule range after changing the table. If a cell has an unexpected appearance, open Manage Rules to inspect overlapping rules, their ranges, and their order. Other conditional formatting may affect how the cell looks.
- Totals overpower the detail: exclude totals from the scale or place them in a separate range.
- Colors imply a false threshold: set a meaningful midpoint or use a one-direction scale.
- Different tables seem inconsistent: apply common numeric boundaries and label the scale.
- Readers cannot identify the exact value: keep the numbers visible and use the colors as supporting context.
Accessibility and interpretation
Color perception differs between readers and display conditions. Avoid palettes that distinguish categories only through red and green, and do not make color the only way to recognize a critical result. Keep the numeric values visible, use clear headings, and consider adding text labels or a separate status column when a decision depends on a threshold. A legend should explain what the colors mean, especially if the scale uses fixed targets rather than relative values.
Interpret a heat map as a prompt for investigation, not as proof of a cause. A dark cell identifies a value’s position on a scale; it does not explain whether a promotion, seasonal change, data-entry error, or other factor produced that value. Check the source data, units, and relevant context before making a conclusion.
Frequently Asked Questions
Does Excel have a heat map feature?
Excel does not need a separate heat-map chart type for a typical worksheet grid. Use Conditional Formatting color scales to shade cells according to their values. For geographic data, Excel has a separate Filled Map chart.
Can I make a heat map with fixed minimum and maximum values?
Yes. Choose Home > Conditional Formatting > New Rule, select a color scale, and set the minimum and maximum types to Number. Enter the numeric boundaries you want so new data does not automatically redefine the endpoints.
Can I make a heat map in a PivotTable?
Yes. Select the PivotTable value cells and apply a color scale through Conditional Formatting. Review the rule’s scope in Manage Rules, because PivotTable conditional formatting can be configured to apply to selected cells or to a broader set of values in the PivotTable.
Why do the colors change when I add a new value?
An automatically scaled rule uses the values in its range to determine the color endpoints. A new minimum or maximum can shift the gradient. Set fixed numeric boundaries if you need the same values to keep the same colors over time.
Can I show the numbers and the heat map together?
Yes. Conditional formatting changes the cell’s appearance without replacing its value, so the numbers remain visible by default. If a shade makes text hard to read, choose lighter colors or adjust the scale’s palette.
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
- Google Sheets: Format a Cell as a DateTo format a cell as a date in Google Sheets, select the cell or range, then choose Format…
- How to Loop a PowerPoint Slideshow Without ClickingTo loop a PowerPoint slideshow without clicking, set the slides to advance automatically and turn on continuous looping.…
- How to Add a Draft Watermark in Word on Every PageTo add a Draft watermark in Word for Microsoft 365 on Windows, open the document and choose Design…
- QuickBooks Excel and CSV Import Toolkit: What It DoesThe QuickBooks Excel and CSV Import Toolkit is an Intuit support resource for bringing certain list data from…
- Excel Data Validation Failed? Common Causes and FixesIn Excel, a data validation failure usually means an entered value does not meet the rule set for…
- Excel Row Height in Inches: Conversion and SettingsExcel measures row height in points, not inches: divide the height in points by 72 to convert it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.