To put a heat map in Excel, select the numeric cells you want to compare, then choose Home > Conditional Formatting > Color Scales and select a palette. Excel shades the cells according to their values, making high and low results easier to spot. You can then adjust the scale, colors, and range so the visual emphasizes the comparisons that matter.
What You’ll Need
- A worksheet containing comparable numeric values, such as sales by product, scores by student, or monthly usage by location.
- A clear idea of which cells belong in the heat map. Include the data values, but usually leave out headings, totals, and notes.
- Excel for Microsoft 365 on Windows for the steps below. The Color Scales feature is also available in current Excel for Mac and Excel for the web, though some dialog labels and controls may appear in different places.
Step-by-Step
- Arrange and check the data. Put the values you want to compare in a rectangular range, with row or column labels next to the numbers. For example, a small sales report could have products in rows, months in columns, and sales amounts in the cells where each product and month meet. Make sure the values are genuinely numeric rather than text that only looks like numbers. Remove stray spaces, error values, or explanatory text from the range where possible. A heat map is most useful when colors reflect comparable measurements; avoid combining unrelated quantities, such as dollars and percentages, in the same scale.
- Select only the values to color. Click the first numeric cell and drag to the last, or click the first cell and Shift-click the last cell. If the range is large, click the first cell, hold Shift, and click the last cell while using the Name Box to navigate as needed. Do not include row labels, column headings, or a grand-total row unless you deliberately want them colored too. Including labels can make Excel treat the range differently, and including a much larger total can flatten the colors for all the ordinary values.
- Apply a color scale. On the ribbon, choose Home > Conditional Formatting > Color Scales. The gallery offers two-color and three-color options. A two-color scale blends from a color for lower values to a color for higher values. A three-color scale adds a middle color, which can make values around the center of a range easier to distinguish. Point to a palette to preview it, then click the one that communicates the pattern clearly. Excel applies conditional formatting, so the colors respond when values in the selected cells change.
- Read the result before customizing. Look at the smallest, largest, and middle values and check that the color order makes sense. In the usual low-to-high scale, the first color represents the low end and the final color represents the high end. Remember that the colors are relative to the rule’s range, not a universal rating: the strongest shade means the highest value in that selected group, not necessarily a good or bad outcome. If your scale makes the results hard to interpret, choose another palette before changing individual settings.
- Edit the scale when you need more control. Keep the relevant cells selected and choose Home > Conditional Formatting > Manage Rules. In the Show formatting rules for list, select This Worksheet if the rule is not shown for the current selection. Click the heat map rule, choose Edit Rule, and use the Format Style controls to confirm that it is a two-color or three-color scale. You can change the colors and the Minimum, Midpoint, and Maximum settings. Choose a type such as Lowest Value, Highest Value, Number, Percent, or Percentile, as appropriate, then enter the corresponding value where Excel allows it. Click OK, then Apply or OK in the rules manager.
- Choose thresholds that match the question. The default scale uses the lowest and highest values in the range, which works well for a quick overview. To compare results against a meaningful target, edit the minimum or maximum and set its type to Number, then enter the threshold. For instance, if a score of 80 is an important boundary, you could make 80 the midpoint in a three-color scale. A midpoint is useful when there is a meaningful center or target; it is not required for every heat map. Check how values below and above your chosen boundary will be colored before relying on the result.
- Check the Applies to range and finish. In Manage Rules, inspect Applies to and confirm that the rule covers exactly the cells you want. If you selected the wrong cells, edit the range or close the dialog and select the correct cells before applying the rule again. Test the formatting by changing one value temporarily, then undo the change or restore the original value. Save the workbook when the colors and coverage look right. If the data will grow, consider applying the rule to a sensible range that includes expected new rows, or use an Excel table so you can manage expanding data more conveniently.
On Excel for Mac, select the cells and look for Conditional Formatting on the Home tab, then choose Color Scales; rule editing is also available from the conditional-formatting controls. In Excel for the web, use Home > Conditional Formatting and select a color scale. The web version may offer fewer rule-editing controls than desktop Excel, so open the workbook in the desktop app if you cannot set a particular threshold or scale type.
Common Mistakes
Including headings or totals. A heading can be treated as a nonnumeric cell, while a total may be far larger than the ordinary entries. Either can distract from the pattern or change the colors across the rest of the range. Select the detail values only, and create a separate rule for totals if you want them highlighted.
Comparing unlike measures. A color scale across percentages, dollar amounts, and counts can suggest a comparison that has no useful meaning. Use separate scales for separate measures. The same caution applies when combining data from different periods or groups whose normal ranges are very different.
Assuming a shade has a fixed meaning. Excel’s default scale adjusts to the values in its Applies to range. A dark color may mean 100 in one range and 10,000 in another. If readers need to compare separate tables, set consistent numeric thresholds or put the data into a common range governed by the same rule.
Using color alone to communicate a decision. Some people have difficulty distinguishing certain color combinations, and a heat map may print poorly in grayscale. Include readable labels and, where appropriate, show the numbers themselves. Avoid using a red-to-green palette as the only signal for a critical status.
Applying a second rule without checking the first. Repeatedly choosing Color Scales can leave overlapping conditional-formatting rules. Excel may then display unexpected colors or make later changes difficult to understand. Review Home > Conditional Formatting > Manage Rules and remove or edit old rules when necessary.
Tips
Pick a palette with enough contrast to show differences but not so much that every cell looks like an alert. A subtle, single-hue scale is often easier to read for ordinary magnitude comparisons. A three-color palette can be helpful when a central value matters, such as a target, average, or zero point. Include a short note near the data explaining what the colors represent, especially if readers may mistake the highest shade for the best result.
Keep the selected range consistent with the question. If you want each row to show its own strongest and weakest months, apply a separate scale to each row. If the goal is to compare every month and product against one another, apply one rule across the whole block instead. These choices create different interpretations, so state which comparison the heat map is designed to show.
For a report that will be updated regularly, keep labels and values in a clean table and avoid inserting blank rows inside the data. After adding new records, check whether the conditional-formatting rule expanded to include them. If not, edit Applies to in Manage Rules. For presentations or printed reports, use a high-contrast palette, retain the numeric values, and check the worksheet at the size readers will actually view it.
Troubleshooting
No colors appear: Confirm that the selected cells contain numeric values and that the conditional-formatting rule exists. Open Home > Conditional Formatting > Manage Rules, set Show formatting rules for to This Worksheet if needed, and inspect Applies to. Text-formatted numbers, cells outside the rule range, or a rule with an unintended range can explain the problem. If the cells contain errors, resolve those separately rather than expecting them to behave like ordinary numbers in the scale.
Everything looks almost the same color: One unusually high or low value may be stretching the scale. Check the data for an outlier or an accidental total. If the outlier is valid but not useful to the comparison, adjust the minimum or maximum using a Number or Percentile threshold in Edit Rule. Do not hide a meaningful outlier just to make the colors more dramatic; instead, explain the threshold or create a separate view.
The colors run in the wrong direction: Open Edit Rule and inspect the colors assigned to Minimum and Maximum. Choose the colors in the order you intend, then apply the change and check a known low and high value. Also make sure the palette suits the meaning of the data; the color order itself does not tell Excel which results are favorable.
New rows are not colored: The Applies to range may end at the original last row. Extend it in Manage Rules, or place the data in an Excel table and confirm the rule covers the table’s value columns. Check the result after adding a row, since copied formatting and table behavior can depend on how the new data was entered.
A copied worksheet shows unexpected colors: Conditional formatting may have copied an existing rule or may still use a range that does not match the new data. Open Manage Rules, choose This Worksheet, and check each rule’s Applies to range and order. Remove obsolete rules with Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, taking care to select only the cells whose rules you intend to remove. Then apply the desired scale again.
Frequently Asked Questions
Can I make a heat map in Excel without a chart?
Yes. A color scale is conditional formatting applied directly to worksheet cells, so it is the quickest way to make a cell-based heat map. A chart is optional and is better suited to showing a different kind of summary or relationship.
Can I use a heat map with text labels?
Yes. Keep labels in their own row or column and apply the color scale to the numeric cells only. Excel can leave text cells without scale colors, but excluding labels makes the range and result easier to manage.
Can I set the same colors for multiple tables?
Yes. Use the same minimum, midpoint, and maximum types and values for each rule. If each table uses its own automatic lowest and highest values, the same shade can represent different numbers in different tables.
Will the heat map update when I change a value?
Yes. Conditional formatting recalculates the cell color when a value in the rule’s Applies to range changes. If new cells are outside that range, extend the rule or include those rows in the formatted table.
How do I remove a heat map?
Select the formatted cells, then choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes conditional-formatting rules from the selection; it does not delete the cell values.
Can I make a heat map with fixed color limits?
Yes. Open the rule in Manage Rules, choose Edit Rule, and set the minimum and maximum types to Number with the limits you want. Values beyond the limits receive the endpoint colors, which makes different ranges easier to compare consistently.
Related
- Excel Print Area Greyed Out: Why It Happens and Fixes
- Excel Protect Sheet Greyed Out? Causes and Fixes
- Excel Insert Row Greyed Out? Causes and Fixes
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 Filter by Date Range: Steps and TipsYes. In Google Sheets, select your data, choose Data > Create a filter, then use the date column’s…
- Power Query Excel: Split a Column into PartsIn Excel for Microsoft 365 on Windows, split a column in Power Query by opening the query, selecting…
- Excel Shortcut to Insert a Copied RowIn Excel for Microsoft 365 on Windows, copy the entire row, select the row where the copy should…
- Choose an Excel Data Range: Methods, Tips, and ShortcutsTo choose a data range in Excel for Microsoft 365 on Windows, click the first cell and drag…
- Mail Merge in Word: Practical Questions and AnswersTo run a mail merge in Microsoft Word for Microsoft 365 on Windows, open the Mailings tab, choose…
- How to Indent and Double Space in WordTo indent and double-space text in Microsoft Word, select the paragraphs, open Home > Line and Paragraph Spacing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.