how to

Build Conditional Formatting Heatmaps in Excel and Sheets

· 2 min read spreadsheetsconditional-formattingvisualizationformatting

A heatmap makes outliers jump out of a dense table without adding a single formula. Both Excel and Google Sheets ship color scales for this, but the defaults often mislead because they scale to whatever range happens to be selected. This guide builds a heatmap that stays honest as data changes.

What you need

  • A rectangular block of comparable numbers (one metric across categories)
  • Excel or Google Sheets
  • Decisions on what counts as good versus bad for this metric

Step by step

  1. Select only the numeric body of the table, excluding headers, totals, and any text. Including totals in the selection skews the entire color scale toward one extreme cell.
  2. Apply a preset first to see the shape of the data: Excel via Home > Conditional Formatting > Color Scales; Sheets via Format > Conditional formatting > Color scale.
  3. Replace automatic min/max with fixed anchors when your metric has natural bounds. For a utilization percentage, set minimum 0, midpoint 0.8, maximum 1.0 so colors mean the same thing every month instead of re-scaling to the current data.
  4. Choose a perceptually ordered palette: light-to-dark single hue or yellow-to-red. Avoid rainbow scales where mid-values become visually meaningless.
  5. Add a discrete rule for thresholds that matter operationally. Use "format cells if greater than" with a solid red fill for values over your alert limit; rule-based fills sit on top of color scales and give crisp boundaries a gradient cannot.
  6. Reverse direction for inverted metrics like cost or churn, where low is good. A green-high default on a cost column tells the opposite story.
  7. Handle blanks explicitly: add a rule that formats blank cells with gray fill, otherwise they inherit the lowest color and look like zeros.
  8. Check the result from arm's length. If you cannot identify the top three and bottom three cells within two seconds, reduce the number of simultaneous rules.

Common mistakes

  • Including total rows in the selected range, compressing all real variation into one shade
  • Leaving min/max automatic, which makes week-to-week comparisons impossible
  • Stacking five rules on one range, producing unpredictable precedence instead of a readable map

Related guides

Heatmaps work best on clean inputs; enforce valid values at entry time with [[dropdown-data-validation-lists]]. To present the same data as charts rather than colored cells, follow [[chart-design-rules]].

← All guides