In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colours each cell according to its value within the selected range, making high, low, and middle values easier to spot; you can customise the colours and thresholds when the default scale does not suit your data.
What an Excel heat map means
An Excel heat map is usually a range of cells coloured with conditional formatting, rather than a separate chart. A colour scale assigns colours according to cell values: for example, a green-to-yellow-to-red scale can show low values in green, middle values in yellow, and high values in red. The colours update when the underlying numbers change, so the display can stay current as a table is edited.
The term “heat map” is used informally for several visualisations, but in a worksheet it most often means a conditional-formatting colour scale. It is useful for scanning a large table for patterns, such as unusually high monthly expenses, strong sales by region, or slow response times. It does not explain why a value is high or low; it only helps direct attention to values within the range being formatted.
How to apply a colour scale
To make a basic heat map in Excel for Microsoft 365 on Windows:
- Select the numeric cells you want to compare. Include or exclude totals deliberately; a grand total can distort the scale.
- Open Home > Conditional Formatting > Color Scales.
- Choose a two-colour or three-colour preset. Excel applies the formatting to the selected cells.
- Check the result against the numbers and your intended meaning. If the colours do not tell the story you need, edit the rule rather than assuming the preset is appropriate.
For more control, select the range and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Format all cells based on their values, then choose a two-colour or three-colour scale. Set the minimum, midpoint if used, and maximum types and colours, then select OK. A two-colour scale maps values between two endpoints; a three-colour scale adds a midpoint, often used to make a neutral or typical value visually distinct.
On Excel for Mac, the Conditional Formatting options are also on the Home tab, although dialog layout can vary by release. Excel for the web includes conditional-formatting colour scales too, but some rule-editing controls may differ from the Windows desktop dialog. If you cannot find a particular setting in the browser, open the workbook in the desktop app.
How Excel maps values to colours
With an automatic scale, Excel uses the values in the rule’s applied range to determine the ends of the gradient. In a simple range containing 10, 20, 30, 40, and 50, a three-colour scale could make 10 green, 30 yellow, and 50 red, with values between those points receiving blended colours. The exact result depends on the selected scale and its settings. The colour is relative to the range, not a universal label attached to that number.
This relative behaviour matters when comparing separate tables. If one table contains values from 1 to 10 and another from 100 to 1,000, each can show its own low-to-high gradient. A bright red cell in one table does not necessarily have a larger value than a yellow cell in the other. For meaningful comparisons between periods or groups, apply a consistent rule and fixed thresholds where appropriate, or put the values in one shared range.
A midpoint is not automatically the average or the median. It is the value or position specified by the rule. In the New Formatting Rule dialog, the available type choices depend on which point of the scale you are setting; options include values based on the range, a number, a percentage, a percentile, or a formula. “Percent” and “Percentile” are not interchangeable: a percentage can position a point along the scale, while a percentile refers to a position in the distribution of values. Choose the type that matches the question you are trying to answer.
Choosing colours and thresholds
Start with the meaning of the data, not the palette. For a measure where larger is better, such as units sold, a low-to-high progression may make sense. For a measure where smaller is better, such as time taken to resolve an issue, reverse the visual meaning so that the colours do not imply that the largest value is best. For data with a meaningful target, use a midpoint at that target rather than automatically placing it at the centre of the observed range.
- Use a two-colour scale when the main task is to distinguish smaller from larger values.
- Use a three-colour scale when a middle value or target deserves a separate colour, such as below, near, and above a service goal.
- Set numeric thresholds when the same value should mean the same thing across reports. A fixed threshold can make a comparison more interpretable than a scale that shifts with each selection.
- Choose restrained colours for dense tables. Strong, saturated fills can make the sheet tiring to read and can compete with headings and labels.
- Check whether the direction is intuitive and add a legend or a short note if readers might interpret the colours differently.
For example, imagine a table of monthly delivery times where 24 hours is the target. A scale with a midpoint set to 24 can separate faster-than-target values from slower ones more usefully than a midpoint based on the range’s central position. If the table is refreshed next month, fixed thresholds also help preserve the meaning of each colour.
Prepare the data before colouring it
Colour scales work best when the selected range contains comparable numeric values. Mixing counts, percentages, dates, and text in one scale can produce a result that is difficult to interpret. Keep labels and headings outside the formatted data range, and avoid including subtotals unless they should influence the scale. If different columns use different units, format them separately rather than asking one gradient to compare unlike measures.
Consider how blanks, errors, and zeros should be treated. A zero can be a genuine measurement, not missing data, and may therefore receive the low-end colour. A blank cell does not represent an ordinary numeric observation, but it may still be included in the rule’s applied range. If blanks or error values need a distinct treatment, use a separate conditional-formatting rule or adjust the data layout instead of relying on the colour scale to communicate missingness.
Outliers deserve particular attention. Suppose most response times fall between 2 and 12 minutes but one is 240 minutes. If the scale uses the full range, ordinary values may occupy only a small part of the gradient and look nearly alike. Check the underlying values before excluding anything: the outlier might be a data-entry error, but it might also be an important event. You can use fixed thresholds or a separate rule to make the distribution more useful, while documenting any decision to exclude or treat an unusual value differently.
Edit, inspect, or remove a heat map
To edit an existing scale, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. Find the relevant rule, select it, and choose Edit Rule. Check the rule’s applied range as well as its colours and thresholds. A rule can be correct but appear to do nothing if it applies to a different range than the one you are viewing.
Manage Rules can also help when a worksheet has overlapping conditional-formatting rules. Review the rules that apply to the selected cells, and confirm that their ranges and order match your intention. If an unwanted scale is affecting a range, select the cells and choose Home > Conditional Formatting > Clear Rules. The menu offers choices for clearing rules from selected cells or from the entire worksheet; choose carefully, because clearing rules removes conditional formatting rather than merely hiding its colours.
Copying cells can also copy conditional formatting. If colours appear unexpectedly after pasting, inspect Manage Rules and the applied range before rebuilding the formatting. When adding rows to a data table, check that the scale covers the new data. The table or range’s behaviour depends on how the rule was set up, so verify it after changes rather than assuming every added row is included.
Common problems and practical checks
- Every cell looks almost the same: the values may be tightly grouped, or an outlier may stretch the scale. Inspect the numbers and consider an appropriate fixed threshold or a separate view.
- The “best” values are the wrong colour: reverse the colour assignments or choose a scale whose direction matches whether high or low is desirable.
- A total changes the whole palette: remove totals from the applied range if they should not set the scale’s endpoints.
- Colours change after filtering or editing: the data included in the rule or its range may have changed. Review the rule and test the display after the change.
- The colours do not print clearly: check print preview and consider lighter fills, clear labels, or a different way of presenting the values.
Do not treat colour as the only carrier of information. Readers may have colour-vision differences, view the sheet in grayscale, or use a display where subtle shades are hard to distinguish. Keep the numbers visible, use sufficient contrast, and consider adding labels, symbols, or a written explanation of what the scale means. A heat map should make the data quicker to scan, not make the values impossible to read.
Heat maps compared with other Excel formatting
A colour scale is well suited to seeing relative patterns across a block of numbers. Data bars, found under Home > Conditional Formatting > Data Bars, show magnitude as a bar within each cell and can make differences easier to compare at a glance. Icon sets use symbols to group values into categories. These options answer slightly different visual questions: a gradient shows position along a range, bars suggest size, and icons classify values into bands.
For a small table, direct labels and ordinary number formatting may be clearer than any visual effect. For a large matrix, a heat map can reveal clusters that are hard to notice by reading every cell. For precise comparisons, keep the values visible and consider sorting or filtering the table as well. A colour scale is a scanning aid, not a replacement for the underlying data or a statistical analysis.
Frequently Asked Questions
How do I make a heat map in Excel?
Select the numeric range, then choose Home > Conditional Formatting > Color Scales and select a preset. Use New Rule if you need to set custom colours, thresholds, or a midpoint.
Can I use my own colours?
Yes. Open Home > Conditional Formatting > New Rule, select Format all cells based on their values, choose a two-colour or three-colour scale, and set the colour for each point in the scale.
Why did my heat map colours change?
Automatic scales are based on the values in the rule’s range, so changing the data or range can change the colours assigned to values. If the same value must keep the same colour across reports, use consistent fixed thresholds.
Can I make zero a neutral colour?
Yes. Create or edit a three-colour scale and set its midpoint to a number of 0, then choose the midpoint colour. This is useful for data with meaningful positive and negative values, provided zero is genuinely the neutral point.
How do I remove a heat map without deleting the numbers?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the conditional formatting while leaving the cell contents intact.
Related
- Excel Consolidate Command: Combine Data Across Sheets
- Excel Consolidate Duplicates and Sum Values
- Excel Solver: Where to Find It and How It Works in 365
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 Create File Labels in Word: A Step-by-Step GuideTo create file labels in Microsoft Word, open Mailings > Labels, choose the label brand and product number…
- Delete a Page in Word with Ctrl+G: Step-by-Step GuideTo delete a page in Microsoft Word on Windows, click in the page you want to remove, press…
- Excel Workbook Shortcut Keys for Everyday TasksFor Excel for Microsoft 365 on Windows, the most useful workbook shortcuts are Ctrl+N to create a workbook,…
- Excel Find and Replace Regex: What Works and What Doesn’tNo: Excel’s Find and Replace dialog does not support regular expressions. In Microsoft 365, use the REGEXREPLACE worksheet…
- Google Sheets Budget Spreadsheet: Setup and FormulasA Google Sheets budget spreadsheet is a reusable table for recording income, planned spending, and actual spending, with…
- Applying Styles in Word: A Practical GuideTo apply a style in Microsoft Word for Microsoft 365 on Windows, select the text or place the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.