To make a heat map in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colors each cell according to its value; you can adjust the scale, range, and formatting afterward to make comparisons clearer.
Build a basic heat map
A heat map is a grid in which color makes patterns in numeric values easier to see. In Excel, the quickest way to create one is with conditional formatting. The colors update when the underlying numbers change, so the map can remain useful as you replace or add data.
- Arrange the values in a rectangular range. Put descriptive row and column headings outside the range of values you want colored.
- Select only the numeric cells to format. For example, if monthly totals are in B2:G13, select B2:G13 rather than including the month names and category labels.
- On the Home tab, select Conditional Formatting > Color Scales.
- Choose a two-color or three-color preset. Excel applies the scale immediately.
- Check that the colors make sense for your data, then adjust the rule if needed through Conditional Formatting > Manage Rules.
For instance, a table of daily temperatures can use a cool-to-warm scale so lower readings appear in one color and higher readings in another. A three-color scale can add a midpoint color, making values around the center of the range stand out differently. The exact color meaning depends on the preset you choose, so do not assume that red always means bad or green always means good.
On Excel for Mac, select the range and use Home > Conditional Formatting > Color Scales as well. In Excel for the web, the equivalent commands are available from Home > Conditional Formatting, although the rule-editing options can differ from the desktop app. Menu wording and available controls may vary slightly by version.
What Excel’s color scale is showing
A color scale maps numbers to colors based on a minimum, midpoint, and maximum. With a two-color scale, Excel blends between a low-value color and a high-value color. With a three-color scale, it also uses a midpoint color. By default, Excel bases the scale on the values in the selected range, rather than on a universal standard.
That distinction matters when you compare separate tables. Suppose one table contains sales figures from 10 to 100 and another contains figures from 1,000 to 10,000. If each table has its own automatically determined scale, the highest cell in each may look equally intense even though the underlying amounts differ greatly. A heat map is therefore excellent for showing relative patterns within a range, but colors alone do not make values from differently scaled ranges directly comparable.
Conditional formatting is not a chart or a separate image. It is a display rule attached to worksheet cells. The numbers remain in the cells, can still be sorted and calculated, and are visible when you select a cell or read its value in the formula bar. Removing the rule removes the colors, not the data.
Choose a useful color scale
Choose colors according to the question the table is meant to answer. A sequential scale, which moves from a lighter shade to a darker shade, suits values that run from low to high and have a natural order, such as units sold or response times. A diverging scale, with contrasting colors on either side of a midpoint, is better when the midpoint has a meaningful interpretation, such as zero change or a target value.
- Use a two-color scale when the main goal is to distinguish relatively low from relatively high values without emphasizing a center.
- Use a three-color scale when a middle value has meaning or when it helps readers distinguish low, middle, and high groups.
- Choose sufficient contrast so adjacent values are distinguishable, but avoid a palette so vivid that it overwhelms headings and numbers.
- Consider accessibility by not relying on color alone. Keep the numbers visible and add labels or explanations where the colors communicate an important status.
Do not select a midpoint simply because Excel offers one. The midpoint can be a numeric value, a percentage of the range, or a percentile, depending on the rule settings. If zero means no change, using the numerical value zero as the midpoint may be more informative than letting Excel place the midpoint halfway between the smallest and largest values. If your entire range is positive, a red-to-green scale centered on zero may not communicate what you intend.
Customize the rule and its limits
To change a preset, go to Home > Conditional Formatting > Manage Rules. In the rules manager, select the relevant rule and choose Edit Rule. In the formatting-rule dialog, select a two-color or three-color scale and set the colors and threshold types for the minimum, midpoint, and maximum. Depending on the threshold type, you can base a point on the lowest or highest value, a number, a percentage, a percentile, or a formula.
Automatic minimum and maximum settings are convenient when the purpose is to show relative variation in one dataset. Fixed numeric limits are more appropriate when values must be judged against a standard or when multiple periods need to share the same visual scale. For example, if a service rating always runs from 1 to 5, fixed endpoints keep a rating of 4 visually consistent across different tables. If Excel instead scales each small table separately, a value of 4 may appear near the top of one scale and less prominent in another.
In a three-color rule, choose a midpoint deliberately. A midpoint set to the 50th percentile represents the middle of the distribution, while a numeric midpoint represents a particular value. Those are not necessarily the same. A skewed dataset with a few very large values can have a median far from the arithmetic center between its minimum and maximum, so the choice changes which cells receive the strongest colors.
The rules manager is also useful when a worksheet has several conditional-formatting rules. Check the Applies to range to confirm the rule covers the intended cells. If you created a rule while a single cell was selected, it may not apply to the whole table; if you selected too much, it may color labels or unrelated data.
Prepare the worksheet for a readable map
Good source data makes a heat map much easier to interpret. Use one value per cell, keep categories consistent, and avoid merging cells inside the data grid. Put units in a heading, such as Revenue (dollars) or Delay (minutes), rather than mixing explanatory text into numeric cells. A column that looks like numbers but is stored as text may not behave like the rest of the range.
Keep labels outside the colored range unless you have a specific reason to format them. For example, a small matrix could place product names in A2:A10, week names in B1:G1, and the values in B2:G10. Selecting B2:G10 colors the measurements while leaving the labels easy to read. If a table includes totals, decide whether they belong in the heat map: including totals can change the scale and make ordinary observations look less varied.
Think about blanks and zeroes separately. A blank can mean missing information, while zero is a real measurement in many datasets. Color scales generally format numeric values and do not treat an empty cell as the same thing as zero. Before applying a rule, decide whether a zero should receive the low-end color and whether missing entries should remain uncolored. If a cell contains a formula that returns an empty string, its visual behavior can differ from a truly empty cell, so inspect the results rather than assuming all apparent blanks are identical.
Read the pattern without losing the numbers
A heat map helps readers spot clusters, unusually high or low areas, and changes across rows or columns. Imagine a table of weekly support requests by issue type. A band of darker cells in one row may suggest that a particular issue is recurring; a darker column may indicate a busy week across several categories. The color is a scanning aid, not an explanation of why the pattern occurred.
Retain the cell values or provide another way to inspect them. A printed or projected grid may make small numbers hard to read if the fill is dark. Use a font color with adequate contrast, widen columns enough for values, and include a clear title and units. If the exact value is central to the task, consider displaying labels in a chart or keeping the formatted table alongside a summary rather than expecting readers to infer quantities from shade alone.
Heat maps can also create false emphasis. A single unusually large value may stretch an automatic scale so that most other cells look nearly alike. Conversely, a scale recalculated independently for each row can make small differences look more important than they are. Decide whether the comparison should be across the entire table, within each row, or against fixed thresholds, and configure the rule to match that comparison.
Common problems and how to fix them
- Nothing changes after choosing a scale: Check that the selected cells contain numeric values and that the rule’s Applies to range includes them. Text-formatted numbers may need to be converted to real numbers.
- Headings are colored too: Edit the rule and narrow its Applies to range to the data cells, or reselect the values without the headings before creating the rule.
- All cells look almost the same: Check for an outlier, a very wide value range, or a mistaken scale limit. Consider fixed thresholds if the data has meaningful bounds.
- Colors change unexpectedly after adding data: Review the rule range and its minimum and maximum settings. New values inside the range can change an automatically calculated scale.
- Two tables seem incomparable: Give both rules the same fixed numeric endpoints and midpoint when a direct visual comparison is required.
When multiple rules overlap, use Home > Conditional Formatting > Manage Rules to inspect their order, scope, and settings. The manager lets you review rules for the current selection or worksheet, depending on the option chosen in the dialog. If the workbook has been copied from another file, inspect existing rules before adding a new one; an old rule may still cover the cells and affect the result.
Heat maps compared with other Excel formats
A color scale is best when the goal is to compare many values in a compact grid. Data bars, found under Home > Conditional Formatting > Data Bars, show magnitude as a bar inside each cell and can be easier to compare when values have a clear zero baseline. Icon sets use symbols to group values into levels, which is useful for status categories but can hide fine differences. These formats are alternatives, not the same as a color-scale heat map.
For a small dataset, a regular chart may communicate the message better, especially when readers need to compare a trend over time or see category labels clearly. A heat map is particularly useful when two dimensions matter at once, such as product by month or region by metric. If the data is geographic, a worksheet grid is not a map of locations; use a map chart or an appropriate mapping tool when actual geography is the point.
Excel’s built-in color scales are generally the simplest approach for a changing table. A manually colored grid may look similar but requires repeated updates and can become inaccurate when values change. If you need a highly customized legend, special treatment for missing values, or a layout beyond cell formatting, a chart or a more specialized visualization may be a better fit.
Frequently Asked Questions
Can I make a heat map in Excel without a formula?
Yes. Select the numeric range and choose Home > Conditional Formatting > Color Scales. The built-in rule handles the color mapping; a formula is not required for a basic heat map.
How do I make the same value use the same color in different tables?
Edit each color-scale rule and set matching numeric minimum, midpoint, and maximum values. Automatic limits are recalculated from each range, so they may assign different colors to the same number.
Can I include text labels in a heat map?
Keep row and column headings outside the formatted numeric range for the clearest result. Color scales are intended to show the relative values of numeric cells, not to assign meaningful color levels to descriptive text.
Why did my heat map colors change when I added data?
The rule may use automatic minimum and maximum values. Adding a new high or low value can shift the scale and therefore change the colors of existing cells. Fixed numeric thresholds prevent that particular shift.
Can I create a heat map in Excel for the web?
Yes. Select the values and use Home > Conditional Formatting > Color Scales. Some rule-editing controls may be more limited or arranged differently than in Excel for Microsoft 365 on Windows.
Related
- Heat Map Scatter Plot in Excel: How to Build One
- Hide and Password-Protect Columns in Excel
- Hide Column C in Excel: Steps to Hide and Unhide It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Merge Cells and Combine Text: How They DifferIn Excel, merging cells joins a selected area into one larger cell, but it does not combine the…
- How to Freeze Selected Columns in Excel Without ScrollingTo keep columns visible while you scroll across a worksheet, select the cell immediately to the right of…
- Excel Shortcut to Insert a Row: Windows and MacIn Excel for Microsoft 365 on Windows, select the row where the new row should go and press…
- Excel Insert Shortcut: Add Cells, Rows, and ColumnsIn Excel for Microsoft 365 on Windows, select the cells, row, or column where you want space, then…
- Excel Print a Certain Area: Set a Print AreaTo print only part of a worksheet in Excel for Microsoft 365 on Windows, select the cells you…
- Dependent Dropdowns in Google Sheets: Setup and FixesTo make a dependent dropdown in Google Sheets, create a named range for each group of choices, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.