To format a heatmap in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and select a palette. Excel applies a color gradient based on each cell’s value in relation to the selected range; use Home > Conditional Formatting > New Rule to customize the scale’s colors and breakpoints.
What an Excel heatmap shows
An Excel heatmap is a way to make patterns in a table easier to see by assigning colors to values. The standard method is conditional formatting with a color scale: lower values receive one end of a gradient, higher values receive the other, and values between them receive intermediate colors. The colors update when the underlying numbers change, so the formatting can work as a visual summary of a changing worksheet.
A heatmap is not a separate chart type, and adding one does not change the cell values. It is a formatting rule applied to cells. For example, a manager might color a table of monthly sales by product, a teacher might compare scores by student and subject, or an analyst might scan a table of response times. The pattern can make unusually high or low areas stand out without adding a chart beside the data.
Color scales are most useful when the numbers form a meaningful continuum. They are less suitable for text labels, unrelated categories, or data where a precise value must be read at a glance. Keep the numbers visible and treat color as a cue rather than a replacement for the values.
Apply a color scale
For a basic heatmap, select only the cells containing the numbers you want to compare. On the Home tab, open Conditional Formatting, point to Color Scales, and choose a two-color or three-color preset. Excel applies the rule immediately. On Excel for Mac, the Conditional Formatting and Color Scales options are also available from the Home tab, although the ribbon layout can vary slightly by release. In Excel for the web, use Home > Conditional Formatting > Color Scales.
- Arrange the data so each cell has a clear meaning, such as one product’s sales in one month.
- Select the numeric data area. Exclude headings and totals unless you intentionally want them included in the same comparison.
- Choose Home > Conditional Formatting > Color Scales, then select a palette that communicates the pattern clearly.
- Review the result against the values. If the gradient exaggerates or hides useful differences, choose a different scale or customize its limits.
A common example is a 12-month by 6-product sales table. If the 72 monthly sales figures are selected together, Excel compares each figure with the other numbers in that selected range. A dark color might mark the largest values and a light color the smallest, depending on the selected preset. If you select each product row separately instead, each row gets its own comparison range, so the colors communicate relative performance within each product rather than across all products.
Choose the right range and comparison
The selection determines what the colors mean. A single scale across a whole table answers questions such as which month-product combinations are highest overall. Separate scales for each row can show each product’s stronger and weaker months, even if one product sells far more than another. Neither approach is universally better; choose the one that matches the question you want the reader to answer.
Including totals can distort the visual comparison. A row total may be much larger than any monthly value, causing the monthly cells to cluster near one end of the gradient. Similarly, a grand total or a forecast column can set the scale in a way that makes the main data harder to distinguish. Usually, apply the rule to comparable observations and keep summaries outside that range.
Think about the data’s meaning before choosing a scale. For values where higher is better, a low-to-high gradient may be intuitive. For error rates, costs, or response times where lower can be better, the same colors could accidentally imply the opposite. A label or note can clarify the meaning, particularly when the palette uses green for high and red for low.
Customize colors and breakpoints
To make a scale more precise, select the cells and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Format all cells based on their values and choose 2-Color Scale or 3-Color Scale under Format Style. Set the colors and the Type and Value for the minimum, midpoint if present, and maximum. The available endpoint types include options such as Lowest Value, Highest Value, Number, Percent, and Percentile; choices can vary by endpoint.
Lowest Value and Highest Value make the gradient follow the extremes in the selected cells. A Number setting lets you choose a fixed threshold. For instance, if a score of 70 is a meaningful boundary, a custom scale can use 70 as a breakpoint rather than letting Excel place the transition wherever the current data range dictates. Fixed values can make comparisons between different periods more consistent, provided the same rule and thresholds are used for each period.
Percent and Percentile are not interchangeable. Percent uses a proportion of the range between the minimum and maximum; it does not mean that a chosen percentage of cells will fall below that point. Percentile refers to the distribution of the values and can place a boundary by rank. A midpoint in a three-color scale is useful when a meaningful central value exists, such as zero for gains and losses or a target score. Without a meaningful center, a two-color scale may be easier to interpret.
When customizing, choose colors with enough contrast to distinguish the gradient but not so much that every cell competes for attention. A restrained, perceptually clear palette often works better for a dense table than highly saturated red, yellow, and green. If readers need to distinguish values in print or with color-vision differences, retain visible numbers and consider adding labels or another visual cue.
How Excel calculates the colors
A color scale maps values in its Applies to range onto a gradient. In the default style, the minimum and maximum values in that range anchor the ends of the scale, and intermediate values receive colors between them. A three-color scale also uses a midpoint. This is why the same number can have different colors in two separately formatted ranges: its color depends on the other values and on the rule’s limits.
Extreme values can affect the rest of the display. Suppose most response times are between 2 and 10 seconds, but one cell contains 300 seconds. If the scale stretches from the smallest to the largest value, many ordinary values may look nearly alike. You can address this by setting a fixed maximum that matches the question, using a percentile breakpoint, or reviewing the outlier for a data-entry error. Do not simply hide a genuine extreme value without making the choice clear to readers.
Color scales compare numbers, not the meaning of labels. A cell containing the text High is not treated as a high numeric measurement. Dates and times are stored as numbers in Excel and can therefore be colored as values, but the result is only useful if the scale and selected range make sense for those values.
Keep a heatmap useful as data changes
Conditional formatting is useful for recurring reports because the display responds when cell values change. But the rule applies to a defined range, so newly added rows may not automatically be included in every worksheet setup. Check the Applies to range through Home > Conditional Formatting > Manage Rules. The dialog’s Show formatting rules for setting lets you inspect rules for the current selection or the worksheet and adjust the range.
If the data is organized as an Excel table, table features can make expanding datasets easier to manage, but it is still sensible to verify the conditional formatting range after adding data. For a report with fixed monthly rows, selecting a planned range that includes future entries can be convenient. Avoid extending the rule across many empty columns or the entire worksheet without a reason, because a broad range can make the display harder to manage.
Use Home > Conditional Formatting > Manage Rules to inspect, edit, or remove rules. To remove a heatmap from selected cells, choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clearing a rule removes the visual formatting, not the cell contents. If you make an unwanted change, Ctrl+Z on Windows or Command+Z on Mac can undo it while the action remains in the undo history.
Common problems and practical fixes
- The whole range looks almost the same: An outlier may be setting the scale’s extreme. Check the largest and smallest values, then decide whether a fixed threshold or percentile is more informative.
- Headings or totals receive unexpected colors: The selected range may include labels or summary cells. Edit the rule’s Applies to range so it covers only comparable numeric observations.
- High values look like bad results: The palette may conflict with the metric’s meaning. Choose colors and labels that make clear whether higher or lower is desirable.
- Colors change after sorting or editing: A color scale is based on values in its comparison range, not a permanent color assigned to each number. Recheck the range and rule limits if the relative distribution changes.
- Some cells remain uncolored: Check whether those cells contain numbers stored as text, blanks, or errors. Convert text-formatted numbers to numeric values when appropriate, and confirm the cells are inside the rule’s range.
Another frequent misconception is that a color scale marks a fixed category such as “good,” “average,” and “poor.” A default scale usually represents relative position between its endpoints, not a business judgment. If a score of 80 must always mean acceptable, regardless of the rest of the worksheet, set a meaningful numeric threshold or use a different conditional formatting rule that expresses that requirement.
Color scales compared with other conditional formats
Color scales are designed to show a continuous progression. Data bars are another option under Home > Conditional Formatting; they place bars inside cells so readers can compare magnitude while still seeing the numbers. Icon sets assign symbols to value bands and can be useful for a compact status indicator. Highlight Cells Rules can mark values that meet explicit conditions, such as being greater than a chosen number.
Use a color scale when relative high-to-low patterns across a grid are the main point. Use a data bar when comparing magnitudes in a list, especially if the table has one value column. Use explicit rules or icons when categories have fixed meanings, such as overdue, on target, and ahead. Mixing several visual systems in the same small table can make it harder to read, so add only what improves the decision or comparison.
Frequently Asked Questions
How do I make a heatmap in Excel?
Select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. For custom colors or limits, choose New Rule and set up a 2-Color Scale or 3-Color Scale.
Can I make the middle of an Excel heatmap represent zero?
Yes. Create a three-color scale with a midpoint Type of Number and set its value to 0. Choose colors that clearly distinguish negative values, zero, and positive values.
Why does the same value have different colors in two Excel heatmaps?
Each rule may compare values against a different range or use different limits. For consistent comparisons, use matching ranges, colors, and fixed numeric breakpoints where appropriate.
Can I use a heatmap with text or dates?
Color scales are intended to compare numeric values. Dates and times are stored as numbers and can be formatted, but text labels are not ranked as numeric measurements. For text categories, use rules based on the category instead.
How do I remove heatmap formatting without deleting data?
Select the formatted cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the conditional formatting rule, not the values.
Related
- Excel Data Table With Multiple Outputs: How It Works
- Excel Data Table Sensitivity Analysis Explained
- Excel Data Table Row and Column Input Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Named Range: How to Create OneTo create a named range in Excel, select the cells, type a clear name in the Name Box…
- Excel SUBTOTAL: Does It Ignore Hidden Rows?Yes, Excel’s SUBTOTAL can ignore hidden rows, but the result depends on its function number. Use 101–111 to…
- Excel Workbook Hidden? How to Unhide ItIf an Excel workbook window is hidden in Microsoft 365 for Windows, open Excel and choose View >…
- Excel Keyboard Shortcut to Jump to the TopIn Excel for Microsoft 365 on Windows, press Ctrl+Home to jump to the beginning of the worksheet, normally…
- Google Sheets FILTER: Array Formulas ExplainedIn Google Sheets, use FILTER to return rows or columns that meet conditions; it expands its results automatically,…
- How to Multiply Hours by Rate in Excel and Format TotalsFor decimal hours, put the hours in B2 and the hourly rate in C2, then enter =B2*C2 in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.