To apply a formula to an Excel heat map, select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a rule, and choose a fill color. For a continuous heat map, use Home > Conditional Formatting > Color Scales instead; a formula-based rule is best when you want specific cells to meet a condition.
How Excel heat maps work
A heat map uses color to make patterns in a grid of values easier to spot. In Excel, the usual approach is conditional formatting: Excel evaluates each cell and changes its appearance when the rule’s conditions are met. The values remain in the cells, so you can still calculate with them, sort them, or change the number format without replacing them with colored symbols.
There are two common meanings of “apply a formula to a heat map.” You might want Excel to color values along a continuous scale, such as pale yellow for low values and dark red for high values. Or you might want a formula to identify particular cells, such as values above a target or the maximum in a grid. Color Scales handles the first case directly; a formula-based conditional-formatting rule handles the second.
This is different from typing a formula into a worksheet cell. A worksheet formula calculates a result, while a conditional-formatting formula determines whether Excel should apply a format. You can use both together, but you normally do not need a new formula in every cell just to create a heat map.
Apply a built-in color scale
For a quick heat map, select the numeric range and choose Home > Conditional Formatting > Color Scales. Select a preset, such as a two-color or three-color scale. Excel assigns colors according to the values in the selected range: a three-color scale commonly uses one color for low values, a midpoint color, and another color for high values.
To adjust how the scale works, open Home > Conditional Formatting > Manage Rules, select the relevant rule, and choose Edit Rule. In the formatting style options, you can set the minimum, midpoint, and maximum types and values. For example, choosing Number lets you define meaningful endpoints instead of letting Excel use the range’s current lowest and highest values. The available controls depend on the selected scale style.
- Select only the cells that contain the values you want to compare, such as B2:F6.
- Choose Home > Conditional Formatting > Color Scales.
- Choose a scale, then check that its colors make low, middle, and high values easy to distinguish.
- If the default endpoints are misleading, edit the rule and set suitable minimum, midpoint, or maximum values.
A color scale is often the right choice for comparing many values at once. It updates as values change and conveys relative magnitude without requiring you to decide on a separate rule for every shade. However, if the data range changes substantially, an automatic scale can also change what a particular color means. Set fixed numeric thresholds when readers need consistent interpretation between reports.
Apply a formula-based formatting rule
Use a formula rule when the heat map needs a specific test rather than a continuous gradient. On Excel for Microsoft 365 on Windows, select the target range, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, choose Format, set a fill color, and confirm the rule. Excel evaluates the formula for each cell in the selected range.
Suppose B2:F6 contains a numeric grid and you want to highlight every cell equal to the grid’s maximum. Select B2:F6, with B2 as the active, top-left cell. For that layout, the formula below returns TRUE for a cell containing the maximum value (96 in this example) and FALSE for other values. Enter it in the formula-rule box:
=B2=MAX($B$2:$F$6)
The reference B2 is relative, so Excel adjusts it as the rule is evaluated across the selected cells. The dollar signs in $B$2:$F$6 lock the comparison range. Without those dollar signs, the range could shift as Excel evaluates cells elsewhere in the selection, producing unexpected results.
To highlight values at or above a target stored in H1, select B2:F6 and make sure B2 is the active top-left cell. With H1 set to 80 and B2 containing 94, this formula returns TRUE for B2, so the chosen format applies to that cell:
=B2>=$H$1
The reference to B2 moves relative to each cell, while the reference to H1 remains fixed. This makes the target easy to change: update H1 and the rule recalculates without editing the conditional-formatting formula. Choose a different fill for this rule if you want to mark the qualifying cells distinctly from the rest of a color-scale heat map.
Use multiple colors or exclude nonnumeric cells
A formula rule can apply one format when a test is true; it does not create a continuous gradient by itself. To make several discrete bands, create separate rules with different tests and fills. For example, a report might use one fill for values meeting a high target and another for values below a lower threshold. Make the thresholds clear to readers, and inspect the rule order in Manage Rules if rules overlap. The Stop If True option can affect whether later rules are applied, so use it only when the order is intentional.
If the selected range may contain headings, notes, or other nonnumeric entries, add a number check so those cells are not colored by a threshold rule. For a range B2:F6 with the cutoff in H1, and with H1 set to 80, this formula returns TRUE for a numeric B2 value of 94 and FALSE for a blank or text value:
=AND(ISNUMBER(B2),B2>=$H$1)
Use it as the formula for the same selected range, with B2 at the top left. This guards against accidental formatting of text and makes the intended test explicit. If your worksheet uses formulas that return numeric values, ISNUMBER recognizes those results as numbers.
For a conventional continuous heat map, a Color Scales rule is generally simpler than building many formula rules. You can also combine approaches: apply a color scale to the whole numeric grid, then add a formula rule to outline or fill a special category such as the maximum. If two rules change the same formatting property, check their order and the preview in Manage Rules to confirm which appearance takes precedence.
Choose a useful range and color scheme
The selected “Applies to” range matters as much as the formula. If you select the wrong cells, Excel may color labels or leave out the last row. In Home > Conditional Formatting > Manage Rules, review the Applies to box and confirm that the rule covers the intended grid. If you add rows later, you may need to extend the range or create the rule while selecting a larger area that includes expected additions.
Think about what the colors are meant to communicate. A diverging scale, with a neutral midpoint and contrasting colors on either side, can help when values above and below a meaningful center have different interpretations. A sequential scale, with shades progressing from light to dark, is usually more suitable when larger values simply mean more. Avoid relying on color alone: keep labels, units, and an explanation of important thresholds near the data.
Color choices also affect accessibility and printing. Make sure adjacent colors are distinguishable, and do not assume every reader can identify red and green equally well. A legend or short note can explain which end represents higher values. If the worksheet may be printed in grayscale, check that the values remain understandable without the fills.
Common problems and fixes
- Every cell gets the same result: Check that the formula uses a relative reference for the cell being tested, such as B2, and that B2 is the active top-left cell when the rule is created.
- The comparison range shifts: Lock a fixed comparison range with dollar signs, as in $B$2:$F$6. Lock a threshold cell such as $H$1 if the same target should be used throughout.
- Headers or blanks are colored: Select only the data cells, or use a formula that checks for numeric values before comparing them.
- The colors look inconsistent after adding data: Review the rule’s Applies to range and, for a color scale, confirm its minimum and maximum settings. Automatic endpoints can move as the data changes.
- A formula rule appears to do nothing: Confirm that the rule is attached to the intended range, that the formula returns TRUE for at least one cell, and that the chosen fill is visible against the worksheet.
Conditional-formatting formulas use ordinary Excel formula references, but their starting point is important. Excel interprets relative references based on the active cell at the time you create the rule. If a rule was made from a different starting cell, its references may be offset. Edit the formula or recreate the rule with the correct top-left cell active rather than changing every reference blindly.
Excel desktop, Mac, and web differences
In Microsoft 365 for Windows, the ribbon path for a color scale or a formula rule is Home > Conditional Formatting. On Excel for Mac, conditional formatting is also available from the Home tab, although dialog layouts and some controls can differ. The underlying idea is the same: select the range, create or edit the rule, and verify the range and formula references.
Excel for the web also offers conditional formatting, but some advanced rule-editing controls may be easier to find or manage in the desktop app. If you cannot create or adjust the formula rule you need in the browser, open the workbook in desktop Excel and use the Windows steps above. The displayed colors are part of the workbook formatting; they are not calculated values that need to be copied into the cells.
Frequently Asked Questions
Can I use a formula to make a gradient heat map?
A formula rule tests whether a condition is true and applies a format; it does not calculate a continuous gradient. Use Conditional Formatting > Color Scales for a gradient. Use formulas for thresholds, exceptions, or categories.
Does a heat map formula go in every cell?
No. A conditional-formatting formula is stored as a rule for the selected range. Excel adjusts relative references as it evaluates each cell. You only need formulas in worksheet cells if you are calculating the underlying data itself.
How do I highlight the highest value in a table?
Select the numeric range and create a formula rule that compares each cell with the maximum of the fixed range. Keep the tested cell reference relative and the range inside MAX fixed, then choose a format for matching cells.
Why does my formula-based rule color the wrong cells?
The most common cause is a mismatch between the formula’s starting reference and the selected range’s top-left cell. Also check the Applies to range, dollar signs on fixed references, and whether another conditional-formatting rule changes the same fill.
Can I change the colors after applying a heat map?
Yes. Use Home > Conditional Formatting > Manage Rules to edit or remove a rule. For a color scale, edit its scale settings; for a formula rule, change the format or formula. Check the resulting colors against the values and thresholds you want readers to notice.
Related
- Excel Geographical Heat Map: Create and Troubleshoot One
- Excel Get Data From Table or Range: A Practical Guide
- Excel Frequently Not Responding? Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find Unique Names in Excel: 3 Reliable MethodsTo find a list of distinct names in Excel, enter =UNIQUE(A2:A100) in an empty cell if you use…
- Excel Date Range Between Two Dates: Formulas and ExamplesTo calculate the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- COUNTIFS in Excel: How to Count OR CriteriaCOUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply.…
- Compare Two Sheets in Excel: Methods, Formulas, and TipsTo compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across…
- VLOOKUP Exact Match in Excel: How to Use ItTo make VLOOKUP find an exact match in Excel, set its fourth argument to FALSE (or 0). For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.