To create a heat map in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a color scale. Excel colors each cell according to its value, making high and low figures easier to spot; you can adjust the scale, data, and formatting rules to make the result clearer and more useful.
What a heat map shows
A heat map uses color to represent the relative size of values in a grid. In Excel, the quickest way to make one is with conditional formatting: the cell’s fill color changes automatically based on its value. A common example is a monthly sales table, where darker or greener cells represent stronger results and lighter or redder cells show weaker ones.
Heat maps are useful when you want to scan many numbers at once and notice patterns that might be hard to see in a table. You might compare sales by product and month, attendance by day and class, or response times by hour and service team. The colors do not replace the underlying numbers; readers can still inspect the values in each cell.
Excel’s Color Scales feature makes a relative comparison across the selected range. In a basic two-color scale, the lowest value gets one end of the color range and the highest gets the other. A three-color scale adds a midpoint color. That means the same value can appear in different colors in two differently formatted ranges, because each range may have different minimum and maximum values.
Create a basic heat map step by step
Arrange the data so that each value sits at the intersection of useful row and column labels. For example, put product names in column A, month names in row 1, and monthly sales in the grid from B2 to M10. Apply the color scale to the values only, not to the labels.
- Select the numeric cells you want to format, such as B2:M10. Avoid including totals or labels unless you intentionally want them included in the same scale.
- On the ribbon, select Home > Conditional Formatting > Color Scales.
- Choose a two-color or three-color preset. Pause over a choice, if available, to preview its effect before selecting it.
- Check the result against the numbers and labels. If the colors are hard to distinguish or the pattern is misleading, choose another scale or customize its thresholds.
The formatting is conditional, not a chart pasted over the worksheet. If a value changes, Excel recalculates its color. You can still edit the cell normally, and the displayed number remains visible unless the fill and font colors make it difficult to read.
On Excel for Mac, select the cells and use Home > Conditional Formatting > Color Scales; ribbon arrangement and dialog details can differ from Windows. Excel for the web also supports conditional formatting and color scales, though available controls may vary by account and interface updates. If a command is not visible, check the Home tab’s Conditional Formatting menu.
Choose a color scale that communicates well
A preset is convenient, but the color choice affects how readers interpret the data. Use colors with enough contrast to distinguish low, middle, and high values, while keeping the numbers readable. A familiar red-to-yellow-to-green scale can imply poor-to-good performance, but that implication may not fit every measure. For example, high error counts or long wait times are usually undesirable, even though they are numerically large.
- Use one hue for simple magnitude. A light-to-dark blue scale can show low-to-high values without implying that one end is good or bad.
- Use a diverging scale when a meaningful midpoint exists. For example, results below a target can be one color, results near the target another, and results above it a third.
- Consider color accessibility. Do not rely on red and green alone to communicate the meaning. Keep the numbers visible, and include labels or a note explaining what the colors mean.
- Keep comparable ranges consistent. If two tables use different scales, matching colors may represent different values. Use common thresholds when readers need to compare the tables directly.
There is no universally best palette. Choose one that reflects the question being asked, works with the values’ meaning, and remains understandable when printed or viewed by people with color-vision differences. A heat map is a visual aid, not a substitute for a legend or clear headings.
Customize the minimum, midpoint, and maximum
Excel’s default color scale uses the selected range to determine its color endpoints. That works for an initial overview, but it can make comparisons unstable: adding a new extreme value may change the colors of many other cells. Custom thresholds are useful when you have known targets, meaningful cutoffs, or ranges that must be comparable over time.
To inspect or change a rule in Excel for Windows, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. Choose the relevant rule and select Edit Rule. In the formatting rule dialog, choose a two-color or three-color scale, then set the type and value for the minimum, midpoint, or maximum as needed. Depending on the type, a threshold can be based on a number, percentage, percentile, or formula. Choose the colors deliberately, then confirm the dialogs.
For instance, a service team might use a fixed midpoint representing its response-time target rather than the median of whatever data happens to be selected. A fixed threshold makes colors more consistent from one reporting period to another. Make sure the chosen scale still communicates the direction correctly: if lower response times are better, the favorable color should be assigned to the lower end.
Manage Rules also helps when a rule appears to affect the wrong cells. Check the rule’s Applies to range and edit it if necessary. If several rules overlap, inspect their order and settings; a different rule may affect the appearance of the same cells. The rule manager’s controls can differ on Mac and Excel for the web, but the same idea applies: verify the range and edit the conditional-formatting rule rather than manually filling cells.
Prepare the worksheet for a reliable result
The formatting can only tell a clear story if the selected values are suitable for comparison. Before applying a scale, check that the range contains numbers rather than numbers stored as text, and that values use the same units. A column mixing dollars and percentages, for example, does not make a meaningful single scale. Likewise, a grid with a total row may show the totals as the largest values, obscuring the patterns among individual items.
- Put descriptive row and column labels next to the data, and keep labels outside the range being colored.
- Exclude grand totals and subtotals when they should not be compared with the individual records.
- Decide how to handle blank cells and missing observations. A blank is not the same as zero, and coloring missing data as if it were a real low value can mislead readers.
- Check for outliers. One exceptionally large or small number can compress the colors of the remaining values into a narrow visual band.
- Use consistent units and periods, and make clear whether larger values are favorable, unfavorable, or simply larger.
Excel does not treat text labels as numeric measurements in a color scale. If cells that should be numbers do not color as expected, inspect their contents and number formats. Changing a cell’s display format alone does not always convert text into a numeric value; you may need to correct or convert the source data. Also look for formulas returning empty text, since those cells may not behave like truly empty cells in every related rule or calculation.
Read and interpret a heat map carefully
Color helps the eye find clusters, contrasts, and unusual values, but it can exaggerate small differences or hide important ones. A continuous scale maps values to a range of colors; it does not automatically group values into meaningful categories. If two cells are similar in color, they may still have different values, so read the numbers before making a decision.
Range selection matters especially when comparing groups. If each month has its own separate color scale, a dark cell in January may not represent the same sales amount as a dark cell in February. If direct comparison across months is the goal, apply one scale to the full data range or set the same numeric thresholds for each month. If the goal is instead to identify relative highs and lows within each month, separate scales may be appropriate, but explain that choice.
Consider adding a short legend or note near the table. It can state, for example, that lighter colors indicate values below a target and darker colors indicate values above it. This is particularly helpful when a color palette has no obvious good-to-bad meaning. Keep the actual values visible so that the map supports, rather than replaces, precise reading.
Common problems and how to fix them
If the whole range looks nearly the same color, check whether one extreme value is stretching the scale. Confirm that the extreme value is valid; if it is, consider fixed thresholds that better suit the task. Do not remove a legitimate outlier merely to make the worksheet look more colorful. If the values are tightly grouped, a subtle scale may be an accurate representation rather than a formatting failure.
If some expected cells are not colored, check the selected range and the rule’s Applies to setting. Also check for text-formatted numbers, blanks, formulas returning empty text, or another conditional-formatting rule. If the colors seem reversed, edit the rule and swap the endpoint colors rather than changing the data. If the display becomes confusing after copying cells, inspect the rules again: copied formatting can create additional rules or expand their ranges.
When new rows or columns are added, confirm that the conditional-formatting range includes them. If the source data is a structured Excel Table, adding rows directly to the table can make it easier to extend the dataset, but you should still verify that the rule covers the new cells. For reports that are regularly refreshed, check the range after each update instead of assuming the formatting has followed every change.
Heat maps compared with other Excel visuals
A conditional-formatting heat map works best when the reader needs to compare values in a grid while retaining the table’s labels and exact numbers. It is fast to build and updates with changing values. It can be less effective when there are many rows, when the audience needs a clear trend over time, or when the color scale requires a long explanation.
A chart may be a better choice when the central question concerns a trend, ranking, or relationship. For example, a line chart can show sales over time more directly, while a bar chart can make product rankings easier to compare. You can also use data bars or icon sets through Home > Conditional Formatting when a compact in-cell visual is more suitable than a full color scale. These tools answer different visual questions; they are alternatives, not interchangeable versions of a heat map.
Frequently Asked Questions
Can Excel create a heat map automatically?
Yes. Select the numeric cells and choose Home > Conditional Formatting > Color Scales. Excel applies a preset scale based on the values in the selected range, and you can edit the rule afterward.
How do I make a heat map with fixed colors for specific values?
Open Home > Conditional Formatting > Manage Rules, edit the color-scale rule, and set numeric thresholds for its minimum, midpoint, or maximum. Fixed thresholds are useful when you need consistent comparisons across reports.
Can I make a heat map with negative numbers?
Yes. Color scales can format numeric values that include negatives. If zero is an important dividing point, edit the scale so its midpoint is based on the appropriate value or type rather than assuming the default midpoint communicates that boundary.
Why do the colors change when I add a new value?
A default scale is based on the values in its applied range. A new minimum or maximum can shift the colors assigned to other cells. Set fixed thresholds in the rule if the colors need to remain stable as the data changes.
Can I copy a heat map to another worksheet?
Yes. You can copy and paste the formatted cells, but review the conditional-formatting rule afterward. Its applied range or thresholds may need adjustment, especially if the destination data has a different scale.
Will the heat map print or export?
Conditional formatting generally appears in worksheet printouts and exports that preserve cell formatting, but check Print Preview and the exported file. A color scale may be difficult to interpret in grayscale, so retain labels and values and consider adding a text explanation.
Related
- Excel Protect Sheet Greyed Out? Causes and Fixes
- Excel Insert Row Greyed Out? Causes and Fixes
- Excel Group Rows Shortcut: The Key and How It Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel AutoFilter Dates Before TodayTo show dates earlier than today in Excel, select your data and choose Data > Filter, then open…
- How to Share a Custom Calendar in Microsoft OutlookTo share a custom calendar in Outlook, select the calendar, open its sharing controls, add the recipient’s email…
- How to Schedule an Email in Outlook MobileTo schedule an email in Outlook mobile, write your message, tap the three-dot menu in the compose window,…
- Excel Power Query Join Types and When to Use EachExcel Power Query has six join types for merging tables: Left Outer, Right Outer, Full Outer, Inner, Left…
- Excel Data Analysis: What It Can Do and Where It Falls ShortYes—Excel is a capable tool for data analysis, especially when you need to organize, clean, summarize, visualize, or…
- Set Out of Office in Outlook Mail: A Step-by-Step GuideTo set an out-of-office reply in Outlook, open Automatic Replies, turn on automatic replies, write your message, and—if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.