To build a heat map in Excel, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colors cells according to their values, making high and low numbers easier to spot without changing the underlying data.
What an Excel heat map shows
A heat map is a grid in which color represents the value in each cell. In Excel, the quickest way to create one is with conditional formatting: a rule changes a cell’s appearance when its value meets a condition. A color-scale rule uses a gradient, so the cells between the lowest and highest values receive intermediate colors.
For example, imagine a table of monthly support tickets, with teams in rows and months in columns. A color scale can reveal which teams had unusually busy months and whether activity rose or fell over time. The numbers remain visible, and sorting, filtering, and calculations continue to work as usual. The colors are a visual layer, not a replacement for the data.
This is different from an Excel map chart. A heat map colors cells in a worksheet grid; a map chart plots geographic regions such as countries or states. If the goal is to compare values by location on a map, use a map chart rather than a cell color scale.
Prepare the data before applying colors
Color scales are easiest to interpret when the selected cells contain comparable numeric values. Put descriptive labels in a header row or column, and select only the values you want colored. Including labels, totals, or unrelated measurements can skew the scale and produce a misleading result.
Suppose a worksheet contains a sales matrix with product names in column A, month names in row 1, and sales figures in B2:E8. Select B2:E8, not the product names or month headings. If you include a grand-total row, its larger numbers may make ordinary monthly values look almost identical. Consider leaving totals outside the colored range or formatting them separately.
Before applying a rule, check that values are genuinely numeric. Numbers stored as text may not behave as expected in numeric comparisons. Also decide what blanks mean: a blank cell might mean no data was collected, while a zero might mean a measured result of zero. They are not interchangeable, and readers can mistake a colored zero for missing information if the distinction is not explained.
- Use one consistent unit and measurement for each color-scaled range.
- Keep row and column headings outside the range unless you deliberately want them formatted.
- Check whether totals, outliers, zeros, and missing values should be included.
- Add a clear title or note explaining what the values represent and what period they cover.
Apply a built-in color scale
In Excel for Microsoft 365 on Windows, select the cells and go to Home > Conditional Formatting > Color Scales. Choose a two-color or three-color preset from the gallery. Excel applies the rule immediately, and the selected cells display a gradient based on the values in the range.
- Select the numeric cells to format. For a nonadjacent selection, hold Ctrl while selecting additional ranges on Windows.
- Open Home > Conditional Formatting > Color Scales.
- Choose a preset whose light and dark colors are easy to distinguish. A common choice uses a light color for low values and a darker color for high values.
- Inspect the result alongside the values. If the colors imply the wrong direction or obscure the numbers, choose another scale or customize it.
On Windows, you can also press Alt, then H, then L to open the Conditional Formatting menu through the ribbon KeyTips; press the keys in sequence, not simultaneously. Excel for Mac has ribbon commands under Home > Conditional Formatting, but Windows KeyTips do not have a direct Mac equivalent. Excel for the web also provides conditional formatting from the Home tab, though the available controls and dialog layout can vary from the desktop app.
Choose between two-color and three-color scales
A two-color scale maps one endpoint to one color and the other endpoint to a second color. It is useful when the main question is simply which values are relatively low and which are high. For example, a light-to-dark blue scale can show variation in monthly order counts without suggesting that one particular number is a meaningful boundary.
A three-color scale adds a midpoint color. This is useful when the middle of the distribution deserves emphasis, or when values naturally fall into low, middle, and high groups. Excel’s preset scales commonly use three colors, but a midpoint is not automatically a target or a warning threshold. Unless you set a meaningful midpoint yourself, Excel positions it according to the scale’s settings, often between the range’s minimum and maximum.
Choose colors for their meaning as well as their appearance. Red-to-green gradients are familiar to many readers, but color-vision differences can make some combinations hard to distinguish. A single-hue light-to-dark palette or a blue-to-orange palette may be easier to read. Do not rely on color alone for a high-stakes status: keep the actual numbers visible and, where appropriate, include text labels such as On track or Needs review.
Set meaningful scale limits
Excel’s automatic scale is relative to the cells covered by the rule. In a selected range, the minimum and maximum values generally determine the gradient endpoints. This makes patterns easy to see within that range, but it can make comparisons between separate tables deceptive. If one table ranges from 10 to 40 and another from 100 to 400, each can show its own brightest and darkest colors even though their values are on different scales.
To choose fixed limits, select the range and open Home > Conditional Formatting > Manage Rules. Select the relevant rule and choose Edit Rule. In the rule editor, choose a Format Style such as 2-Color Scale or 3-Color Scale, then set the minimum, midpoint, and maximum types and values as appropriate. For example, a satisfaction score that always runs from 1 to 5 can use those fixed endpoints, so a score of 4 has a consistent color across worksheets. The exact labels and dialog layout can differ slightly between Excel versions.
Use Number when the endpoint should be a specific numeric value. Other types, such as Percent or Percentile, describe positions within the selected data rather than a fixed measurement. A 50th-percentile midpoint means the middle of the distribution, not necessarily the arithmetic midpoint between the smallest and largest values. Choose the type that matches the question you want the colors to answer.
Format separate rows or ranges fairly
A single scale across a whole table compares every selected cell with every other selected cell. That is appropriate when all cells are measurements of the same kind and one shared scale is useful. It is not always appropriate when each row is a separate unit, such as comparing the pattern of weekday demand for different stores. A store with much larger overall sales could dominate the shared scale and leave smaller stores with little visible variation.
In that case, apply separate rules to each row or compare values using a normalized measure in a separate range. Separate row rules make colors meaningful within each row, but the same color no longer means the same absolute value across rows. For example, dark blue could mean 100 sales in one row and 900 in another. Label the method or use a shared scale if readers need direct comparisons between stores.
To inspect or adjust the ranges controlled by rules, go to Home > Conditional Formatting > Manage Rules. The Show formatting rules for menu lets you review rules for the current selection or worksheet. Check the Applies to field when copying cells, adding rows, or changing the table layout; a rule can remain attached to an old range or expand in an unexpected way.
Make the heat map readable and accessible
A useful heat map communicates a pattern at a glance, but it should not force the reader to guess what the colors mean. Keep values visible unless the exact numbers are genuinely unnecessary. Use a concise title, identify the units, and state whether darker or warmer colors mean more, less, better, or worse. A color palette alone does not explain the business meaning of a high value.
- Choose a gradient with enough contrast to distinguish neighboring values without making every small difference look important.
- Use a consistent scale for ranges that readers are expected to compare directly.
- Check the worksheet in grayscale or with a color-vision simulation if accessibility is important.
- Use borders or labels sparingly; too many visual elements can compete with the color pattern.
- When printing, check that the colors remain distinguishable and that the numbers are legible.
Conditional formatting is not always carried into every exported or copied format in the same way. If a heat map will be shared as a PDF, printed, or pasted into another application, preview the final output. Keep a copy of the data table in the workbook so that readers can examine values instead of relying on a screenshot or color alone.
Common problems and how to fix them
If the selected cells all look nearly the same color, first check for an outlier. A single very large or very small value can stretch an automatic scale and compress the colors for the rest of the data. Decide whether the outlier belongs in the comparison. If it does, fixed limits or a percentile-based scale may make the rest of the pattern more visible, but the treatment should be clear to readers.
If some cells do not change color, check whether they are text, blank, or outside the rule’s Applies to range. Open Manage Rules to find overlapping rules, inspect their order, and confirm that the intended range is covered. Another conditional-formatting rule may also be affecting the same cells. Editing or removing a rule is safer than repeatedly applying new presets, which can leave multiple rules behind.
If the colors look wrong after adding data, revisit the range and the scale endpoints. A preset may automatically include new cells in a formatted table, while a manually selected range may not. If you paste data from another workbook, it can bring formatting or rules with it. Use Paste Special options when you want to paste values without importing the source formatting, then verify the heat map rule.
When to use another Excel visualization
A heat map is strongest when the data forms a useful grid and the reader needs to find clusters, gaps, or relative highs and lows. It is less useful when exact rank order matters, when there are only a few categories, or when readers must compare precise values. A sorted table, bar chart, or line chart may make those tasks clearer.
For time trends, a line chart often makes direction and turning points easier to follow. For a small number of categories, a bar chart supports more precise comparisons because readers can compare lengths against an axis. For geographic patterns, consider a map chart if the data has recognizable geographic fields. These options can complement a heat map: keep the worksheet grid for detailed scanning and add a chart when a different visual question matters.
Frequently Asked Questions
Can Excel create a heat map automatically?
Yes. Select numeric cells and choose Home > Conditional Formatting > Color Scales. Excel applies a built-in gradient rule. Review the selected range and scale afterward, because automatic colors do not know which values are meaningful business thresholds.
Does a heat map change the cell values?
No. Conditional formatting changes how cells are displayed, not the stored numbers. You can still calculate with, sort, and filter the values. Removing the conditional-formatting rule removes the colors but leaves the data intact.
Can I make the same value the same color in different tables?
Yes, if you set consistent numeric minimum, midpoint, and maximum values for each rule. Automatic scales are generally relative to the range they format, so separate ranges can assign different colors to the same number unless their limits are aligned.
How do I remove a heat map in Excel?
Select the formatted cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Use Clear Rules from Entire Sheet only when you intend to remove conditional formatting across the whole worksheet, not just the heat map.
Why do blank cells or zeros look different?
Blank cells and numeric zero are different values, and a color scale may display them differently. Check whether a blank means missing data or whether zero is a real measurement. If those distinctions matter, consider a separate rule or a note so readers do not interpret missing values as low results.
Related
- Excel Named Range: How to Create One
- 2-Way Data Table in Excel: Setup, Examples, and Tips
- Yes or No Column in Excel: Create and Use a Dropdown List
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Power Query Fast Data Load: Settings and TipsYes—on Excel for Microsoft 365 for Windows, open Data > Get Data > Query Options > Data Load…
- Excel Dropdown List From a Table Column That UpdatesYes. In Excel for Microsoft 365 on Windows, turn the source data into a table, define a workbook…
- PowerPoint Notes: How to Present With Speaker NotesTo present with speaker notes in PowerPoint, add your talking points in the Notes pane, then start the…
- How to Cancel a Mail Merge in Word Without SendingTo stop a mail merge in Word, press Esc or choose Cancel if a progress window offers that…
- Create a DAX Measure in Excel: Step-by-StepTo create a DAX measure in Excel for Microsoft 365 on Windows, add your data to the workbook’s…
- Create an Outlook Email Template: Step-by-StepTo create a reusable email template in classic Outlook for Windows, compose a message, then choose File >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.