An Excel table heat map uses Conditional Formatting to color cells according to their values, making high, low, and middle results easier to spot. In Microsoft Excel for Microsoft 365 on Windows, select the numeric cells in a table column, then choose Home > Conditional Formatting > Color Scales and pick a palette.
What an Excel table heat map shows
A heat map is a visual summary, not a separate chart type. In Excel, it is usually a color scale applied to cells: one color marks lower values, another marks higher values, and an optional middle color shows values between them. For example, a sales table might shade smaller monthly totals pale red and larger totals green. The numbers remain in the cells, so readers can still inspect exact results.
Excel tables are useful for this because they organize related data into named columns, provide filters, and can extend as records are added. A color scale can make a long column of figures easier to scan without sorting it or changing the underlying values. Heat maps are especially useful for spotting clusters, unusually high or low entries, and gradual patterns across a list.
The colors are relative to the range defined by the conditional formatting rule. A value colored green in one table is not necessarily larger than a green value in another table. It means that it falls toward the higher end of the scale used for that particular rule.
Create a heat map in an Excel table
- Click a cell in the table and identify the numeric column you want to visualize. If needed, create a table by selecting the data and choosing Insert > Table, then confirm whether the data has headers.
- Select the values in the column, usually excluding its header and any total row. For instance, select the monthly sales figures, not the words in the Sales header.
- Choose Home > Conditional Formatting > Color Scales.
- Pick a two-color or three-color scale. Excel applies the rule immediately, and the cell fills change according to the values in the selected range.
That is enough for a basic heat map. If you select a whole table instead of one column, Excel evaluates the selected numeric cells together. That can be useful when all measures share a scale, but it can be misleading when columns use different units. For instance, comparing revenue in dollars with a percentage in the same color scale may make the shades look meaningful even though the quantities are not comparable.
On Excel for Mac, the comparable command is available from Home > Conditional Formatting > Color Scales. Excel for the web also offers color-scale conditional formatting from the Home tab, although some rule-editing controls may differ from the desktop app.
How Excel assigns the colors
A color-scale rule maps values in its Applies to range onto a gradient. In a typical three-color scale, the lowest value receives the minimum color, a midpoint receives the middle color, and the highest value receives the maximum color. Excel’s presets use defined scale settings; you can inspect or change the thresholds rather than assuming that the midpoint is always the arithmetic average.
The key idea is that the same number can receive a different shade when the range changes. If a column contains 10, 20, and 30, the middle value is near the center of that range. Add 100, and 30 becomes much closer to the low end. A heat map therefore describes the distribution within the rule’s range, not a universal rating of each number.
Color scales work best with numeric data. Text entries are not placed on a numeric gradient, and blank cells generally do not receive a value-based shade. Excel stores real dates as serial numbers, so a scale can also shade date values in order, provided the cells contain actual Excel dates rather than text that only looks like a date. Zeros are numbers and can be colored; check whether zero means a real result or a missing-value placeholder before interpreting the display.
Choose the right range and scale
The range determines what the colors mean, so choose it to match the question. Applying a scale to one column answers which entries are high or low within that column. Applying it to an entire block compares cells across the block. Applying separate rules to separate columns makes each column’s colors relative to its own values.
- Use one scale for a single measure when the goal is to compare records such as sales by region or response time by ticket.
- Use separate scales for unlike measures such as dollars, percentages, and counts. Their magnitudes should not be mixed just because they appear side by side.
- Use a midpoint deliberately when values have a meaningful center, such as zero variance or a target value. Configure the midpoint to reflect that reference rather than relying on a visually appealing preset.
- Consider outliers before choosing minimum and maximum settings. One extreme value can compress the colors for the rest of the records, making modest differences difficult to see.
To adjust the rule, use Home > Conditional Formatting > Manage Rules. Select the relevant rule and choose Edit Rule. In the formatting rule dialog, you can change the scale type, colors, and threshold settings. The precise options depend on the selected rule and Excel version. Choose colors with enough contrast to distinguish categories, but avoid implying that a color has a fixed meaning if the scale is purely relative.
Keep the heat map useful and readable
Color should support the numbers, not replace them. Retain the values and appropriate number formats so readers can verify what a shade represents. A legend or a short note can explain whether darker means larger, smaller, farther from target, or something else. This matters because red and green, for example, can suggest warning and success even when the scale simply runs from low to high.
Think about accessibility when selecting a palette. Red-green distinctions can be difficult for some readers to perceive, and very pale shades may disappear on a screen or in print. A sequential palette with a clear light-to-dark progression is often easier to interpret. Check the result at normal viewing size, and do not rely on color alone to communicate a critical status.
Table banded rows and conditional formatting can compete visually. If a heat map is hard to read, try a lighter scale, turn off table banding from the Table Design tab, or apply the rule only to the cells that need emphasis. Conditional formatting changes the cell appearance while leaving the stored value unchanged; it does not sort the table or modify the data.
Common problems and how to fix them
- The whole column has nearly the same shade: Check for an outlier or a very wide range. Review the minimum and maximum thresholds, or use a rule range that fits the comparison you actually want to make.
- The header or total row is colored: Edit the Applies to range in Home > Conditional Formatting > Manage Rules so it includes only the intended data cells. A table header is text, but a numeric total can be included if the selected range covers it.
- New rows do not show the expected colors: Open Manage Rules and inspect Applies to. Excel may extend a rule as table data grows, but the range is worth checking, particularly after pasting data or changing the table layout.
- Values that look numeric are not shaded: Some may be stored as text. Convert them to numbers, and confirm that the rule covers those cells. Also check whether the cells are blank or contain a formula returning an empty string.
- Zero is unexpectedly colored: A zero is a numeric value, not a blank. If zero represents unavailable data, correct the data representation or use a rule designed for the intended condition rather than treating zero as missing automatically.
- Another format seems to override the scale: Review other conditional formatting rules in Manage Rules. Rules may overlap, and their order or settings can affect the display. Also check for manually applied fills that make the result harder to distinguish.
Heat maps compared with other Excel formatting
Color scales are not the only way to visualize values in a table. Data bars place a bar inside each cell, which makes relative magnitude visible while keeping the number on display. Icon sets use symbols such as arrows or traffic-light indicators to classify values into groups. Both are available through Home > Conditional Formatting, alongside color scales.
Use a color scale when a continuous gradient is helpful, such as comparing many values with gradual differences. Use data bars when magnitude is easier to compare as length. Use icon sets when the purpose is to separate values into a few status bands. For exact category colors, such as assigning a particular fill to each department name, use a rule suited to categories rather than a numeric color scale.
A chart may be a better choice when the reader needs to understand a trend over time, compare a small number of groups, or see a relationship between measures. A heat map is compact and works well inside a detailed table, but it has no axes and can be harder to interpret when the reader needs precise comparisons across many dimensions.
Maintain the rule as table data changes
Tables can expand when you type or paste records directly below them, but conditional formatting rules are managed separately from the table’s data structure. After adding rows, check that the new cells are included in the rule. In Manage Rules, use the rule’s Applies to field to confirm the address covers the intended records. Avoid selecting entire worksheet columns without a reason: a broad range can slow a workbook or include unrelated values in the scale.
When a table is filtered, the heat map usually remains based on the rule’s defined range, including records hidden by the filter. Filtering changes which records are displayed, not necessarily the values used to set the scale. As a result, the visible colors may still reflect hidden high or low values. If the question is about only a filtered subset, be clear about that limitation and consider a separate analysis range or a different summary.
Frequently Asked Questions
Can I make a heat map in an Excel table without a formula?
Yes. Select the numeric cells and choose Home > Conditional Formatting > Color Scales. Excel creates a value-based rule without requiring a worksheet formula.
Can a heat map show high values in red and low values in green?
Yes. Choose a suitable preset or edit the rule’s colors. Confirm the direction after applying it, because different palettes communicate different meanings and the colors do not change the underlying values.
Does filtering a table recalculate the heat map colors?
Filtering hides rows, but the color scale is generally based on the rule’s Applies to range, which can include hidden rows. The visible colors may therefore still be influenced by filtered-out records.
Why are some cells in my heat map not colored?
Check whether those cells are blank, contain text instead of numbers, or fall outside the rule’s Applies to range. Review Manage Rules and verify that the cells contain the kind of values the scale can evaluate.
Can I use a heat map for text categories?
A numeric color scale is intended to compare values, not assign distinct colors to text labels. For categories, use conditional formatting rules that identify each category or apply formatting another way.
Will the heat map include new rows added to the table?
It may expand with the table, but do not assume that every edit or paste will extend the conditional formatting range as intended. Check Applies to in Manage Rules after adding records.
Related
- Excel Return Table Data as JSON: Methods and Limits
- Excel Repeatedly Not Responding? Causes and Fixes
- Excel Remove Duplicates While Keeping the Oldest Date
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Filter by Color in Excel Not Showing? Fix ItIf Filter by Color is missing in Excel, open the filter arrow on the column that actually contains…
- Excel File Format and Extension Error: Causes and FixesThe Excel message “The file format and extension of [filename] don’t match” means Excel cannot confirm that the…
- Excel Error Types: What Each One MeansExcel error types are messages that explain why a formula cannot return its intended result. Common examples include…
- Excel Not Adding Numbers? Fix Text, Formulas, and TotalsIf Excel is not adding numbers, first check whether the entries are stored as text; the SUM function…
- Excel Formula to Remove the #DIV/0! ErrorIn Excel, the quickest way to replace a #DIV/0! error is to wrap the division in IFERROR, such…
- TRIM Excel Not Working? Fix Spaces That TRIM MissesIf TRIM appears not to work in Excel, the text may contain a character that TRIM does not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.