An Excel heat map matrix uses cell colors to show how values vary across two dimensions, such as products and regions. In Microsoft Excel for Microsoft 365 on Windows, select the numeric cells and choose Home > Conditional Formatting > Color Scales; Excel then applies a color gradient based on the values in the selection.
The colors make patterns and outliers easier to spot, but they do not change the underlying data. A useful heat map depends on a well-shaped matrix, a scale that fits the question, and clear interpretation of what each color means.
What an Excel heat map matrix shows
A matrix arranges one category down the rows and another across the columns. Each cell contains the value for one pairing, and conditional formatting adds color according to that value. For example, a sales matrix might have products in rows, months in columns, and revenue in the cells. A color scale can quickly reveal seasonal peaks, weaker products, or unusual combinations.
Excel’s Color Scales feature is conditional formatting, not a separate chart type. The values remain in the worksheet, so you can still calculate with them, sort them, or change their number format. The fill color updates when the cell values change, as long as the conditional-formatting rule still applies to those cells.
A heat map is most useful when the cells are numeric and the row and column categories are meaningful. It can also represent percentages, counts, durations, or scores. Text labels, totals, and notes usually belong outside the colored data area because they do not have a numeric position on the scale.
How to create the matrix
First arrange the data in a rectangular range. Put one category in the first column, the other category in the header row, and the measurements in the cells where they intersect. Keep the row and column labels outside the range you plan to color. For example, if months are in B1:G1, products are in A2:A10, and values are in B2:G10, select B2:G10 rather than including the headings.
- Select the numeric cells in the matrix. Include the full intended data area, but leave out headings, totals, and explanatory notes unless you specifically want them included.
- On the ribbon, choose Home > Conditional Formatting > Color Scales.
- Choose a two-color or three-color preset. Excel immediately applies the scale to the selected cells.
- Check the result against the numbers. If a color does not carry the meaning you intended, edit the rule rather than assuming that red or green has a universal interpretation.
To adjust the scale, choose Home > Conditional Formatting > Manage Rules, select the relevant rule, and choose Edit Rule. In the rule editor, choose a two-color or three-color scale and set the colors and the value types for its minimum, midpoint, and maximum. In Excel for Mac, the corresponding controls are also available from Home > Conditional Formatting; the rule dialog may be laid out differently. The core approach is the same.
Choosing a color scale that fits the data
A two-color scale is useful when the main question is simply which values are lower and which are higher. A three-color scale adds a midpoint, which can make a meaningful center easier to see. A common example is low, middle, and high performance, but choose colors that match the context: a high value may be favorable for revenue and unfavorable for defect counts.
Excel’s preset palettes are convenient, but color meaning is not automatic. Red does not always mean bad, and green does not always mean good. For a customer satisfaction score, for instance, you might choose a palette that clearly distinguishes low scores from high ones and explain the direction in a nearby note. Use a neutral or distinct midpoint when values around the center represent a different condition from either extreme.
Consider the shape and range of the data before settling on a scale. If one value is much larger than all the others, the remaining cells may look nearly identical because most of the gradient is consumed by that outlier. You can set fixed thresholds, investigate the outlier, or use a ranking-based display if the question is about relative position rather than the size of the original differences.
Setting meaningful thresholds
By default, a color scale is based on the values in the cells covered by its rule. In a two-color scale, the low and high ends are tied to the selection’s lower and upper values; a three-color scale also uses a midpoint. That makes the colors responsive to the selected data, but it also means the same number can receive different colors in two different matrices.
For comparisons across separate periods or teams, consider defining fixed numeric limits instead of allowing each selection to determine its own endpoints. In Edit Formatting Rule, choose the appropriate value type for each point and enter the thresholds. For example, if a service score always runs from 0 to 100, use those limits for every comparable matrix. A score of 70 will then have a consistent color wherever it appears, even if one period’s data are generally higher.
For data centered on a meaningful boundary, use a diverging scale with a midpoint at that boundary. A variance matrix might use a midpoint of zero so negative and positive differences fall on opposite sides of the scale. If the most negative value is -20 and the most positive is 80, however, an automatically chosen midpoint may not be zero. Setting the midpoint explicitly helps the colors distinguish losses from gains rather than merely dividing the observed range in half.
Practical examples and interpretation
Imagine a table of monthly sales by product. A heat map can reveal that one product performs consistently while another has strong peaks in particular months. The color alone does not explain why sales changed, so use it to identify cells for further investigation, then check the actual values, dates, promotions, and other relevant information.
A staffing or workload matrix can show hours by employee and day. Here, a high value might signal a capacity issue rather than a success. A grade matrix can make clusters of lower scores visible, while an inventory matrix can help locate items that are unusually low or high. In each case, the reader should be able to identify the units and the direction of the scale without guessing.
Be careful when comparing rows or columns with very different ranges. If one product’s monthly values range from 10 to 20 and another’s range from 100 to 1,000, a single scale across the whole matrix emphasizes the absolute differences. Separate scales for each row would instead emphasize relative variation within each product. Neither view is inherently better; choose the one that answers the question, and label it so readers know whether colors are comparable across rows.
Organizing and maintaining the data
Keep the matrix rectangular and use one consistent measurement in its cells. A blank, a zero, and a missing observation are different things: zero is a numeric value, while a blank may mean that no observation was recorded. If missing data should not be treated like a low result, leave it blank and explain the convention. Do not enter a text marker such as “n/a” in the measurement area unless you have checked how it affects the display.
When you add rows or columns, verify that the conditional-formatting rule covers them. Choose Home > Conditional Formatting > Manage Rules and inspect the rule’s Applies to range. You can adjust that range if new data fall outside it. Excel tables can make expanding datasets easier to manage, but check the applied range after adding data rather than assuming every rule extended exactly as intended.
Totals need deliberate handling. Including a grand-total row in a scale can make ordinary values look compressed if the total is much larger. Often it is clearer to leave totals uncolored or format them separately. Likewise, do not include text headings in the scale just because they sit beside the numbers; keeping the rule limited to the measurement cells makes the matrix easier to read.
Common problems and how to fix them
- All the cells look similar: The values may be tightly clustered, or one extreme value may be stretching the scale. Inspect the actual numbers and consider fixed thresholds or a different scale suited to the comparison.
- The colors seem inconsistent between tables: Each table may be using its own minimum and maximum. Set shared numeric thresholds when readers need to compare colors across separate ranges.
- Headers or totals are colored: The applied range probably includes cells that should not be part of the scale. Review Home > Conditional Formatting > Manage Rules and correct Applies to.
- New data have no color: They may sit outside the rule’s applied range. Expand the range and confirm that the new cells contain numeric values.
- A value appears to be ignored: Check whether it is stored as text rather than as a number. Also check for blanks, text markers, or errors in the range; they do not behave like ordinary numeric measurements in a color scale.
If several conditional-formatting rules overlap, one rule may affect the result or make it harder to understand. Manage Rules lets you review rules for the current selection or worksheet and inspect their order and ranges. Simplifying overlapping rules can make the display more predictable.
Readability and accessibility
Color should support the numbers, not replace them. Keep values visible, use a number format that makes units and precision clear, and include a short legend or sentence explaining what the scale means. A reader should not have to infer whether the darkest shade means the highest value, the lowest value, or a warning.
Choose colors with enough contrast and avoid relying only on a red-versus-green distinction. Some readers have difficulty distinguishing particular color combinations, and a printed worksheet or grayscale screenshot can remove much of the distinction. Labels, the underlying values, and a clear explanation provide useful context when color is hard to perceive.
For a report that will be printed or shared as a PDF, preview it in that format. Check that cell fills remain distinguishable and that the matrix is not split awkwardly across pages. A well-labeled table can still be useful when its color treatment is less visible than it is on screen.
Heat maps compared with other Excel views
A conditional-formatting heat map is a good fit when readers need to scan a grid and see patterns in place. A chart may be better when the goal is to show a trend over time, compare a small number of series, or communicate a relationship to an audience unfamiliar with the table. A heat map preserves the matrix structure; a chart can make a specific comparison more prominent.
Excel also offers icon sets and data bars through Home > Conditional Formatting. Data bars show magnitude within cells and can work well when ranking values is the main goal. Icon sets categorize values into visual groups. These options do not replace a two-dimensional color pattern in every situation, but they may be clearer when the table has few columns or when readers need explicit categories rather than a continuous gradient.
Frequently Asked Questions
Does Excel have a built-in heat map feature?
Excel does not require a separate heat-map chart for a matrix. The usual method is conditional formatting: select the numeric range and choose Home > Conditional Formatting > Color Scales.
Can I make the same value show the same color in two matrices?
Yes. Edit each color-scale rule and use the same fixed numeric minimum, midpoint, and maximum where appropriate. Automatic scales are based on each rule’s own range, so they may assign different colors to the same value.
Can a heat map show negative and positive values?
Yes. Use a three-color scale and set the midpoint to zero when zero is the meaningful boundary. Choosing suitable lower and upper limits helps keep the colors comparable and makes negative and positive values easier to distinguish.
Why are blank cells not colored like zeros?
A blank is not the same as a numeric zero. A blank may indicate missing information, while zero is a measured value. Decide which meaning is correct for your data and use a consistent convention before applying the scale.
Can I create a heat map in Excel for Mac?
Yes. Excel for Mac includes conditional-formatting color scales. Select the data and use Home > Conditional Formatting > Color Scales; the rule-editing dialog may differ visually from the Windows version.
Related
- Excel: Consolidate Text Into One Cell
- Excel Consolidate Tool: How to Combine Data
- Excel Consolidate Tabs: Methods for Combining Worksheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Sorting in Google Sheets: A Practical GuideTo sort data in Google Sheets in a browser, select a cell in the table and choose Data…
- Google Sheets: How to Combine Data From Two ColumnsYes. In Google Sheets, combine two columns row by row with the ampersand operator: if the values are…
- Google Sheets COUNTIF Format: Syntax, Criteria, ExamplesIn Google Sheets, COUNTIF uses the format =COUNTIF(range, criterion) to count cells that meet one condition. For example,…
- How to Lock Cells in Excel So No One Can Edit ThemTo stop people changing particular cells in Excel, first unlock the cells they should still be able to…
- How to Group Rows in Excel and Collapse Them EasilyTo group rows beneath a heading in Excel, select the detail rows by their row numbers, then choose…
- Excel Sheet Jump Shortcut Keys for Fast NavigationIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet tab and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.