Heat Map in Excel — Free Template + Conditional Formatting Guide (2026)

A table of 96 numbers takes minutes to scan; the same table as a heat map takes two seconds. In 35 minutes you’ll learn every heat-map technique Excel offers: 3-color scales with conditional formatting, custom midpoints and percentiles, single-color scales, when icon sets beat colors, a GitHub-style calendar heatmap, a correlation-matrix heatmap for analysts, and the formatting details that make the result genuinely readable. No add-ins, no VBA — works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is a Heat Map?

A heat map encodes numbers as colors: each cell in a grid is shaded by its value, so big values glow one way, small values another, and your eye reads the whole matrix at once. Where a table forces you to compare digits cell by cell, a heat map exploits the fastest pattern-recognition machinery humans have — pre-attentive color perception — to surface hot spots, cold spots, stripes, and clusters in literal seconds. It is one of the highest information-density visualisations there is, which is why dashboards, risk matrices, and analytics tools lean on it so heavily.

In Excel, the surprise for many people is that a heat map is not a chart. There is no “heat map” entry in the Insert Charts gallery. Instead, Excel’s heat maps are built with conditional formatting color scales applied directly to a range of cells: Excel inspects the minimum, maximum, and (optionally) a midpoint of the selection and interpolates a background color for every cell in between. The result behaves better than a chart in several ways — it lives in the grid alongside labels and formulas, it recolors itself instantly when values change, it copies, sorts, filters, and prints like ordinary cells, and it costs essentially nothing to build.

Heat maps shine wherever you have a two-dimensional matrix of one metric: sales by region × month, website traffic by hour × weekday, support tickets by product × severity, test scores by student × topic, correlations by variable × variable. The two dimensions become the rows and columns; the metric becomes the color. Patterns that are invisible in the numbers — “the North region sags every Q3”, “traffic spikes Tuesday mornings”, “these two KPIs move together” — become impossible to miss.

The craft, and what separates an agency-grade heat map from the default, is in the scale decisions: which colors, where the midpoint sits, whether the extremes are anchored to the data or to fixed meaningful values, and whether the comparison should run across the whole matrix or within each row. Those choices change what story the map tells — sometimes dramatically — and they are exactly what Steps 3 through 8 of this guide cover. The interactive widget further down lets you drag a midpoint and flip color schemes live so you can feel the effect before you make the choices in your own workbook.

Before You Start

Heat maps need almost no preparation, but the three checks below prevent the three most common disappointments.

One metric, real numbers

Every cell in the matrix must hold the same metric (all revenue, or all units — never a mix) stored as genuine numbers. Values imported as text look identical but are silently ignored by color scales. Quick test: select the range and glance at the status bar — if Sum and Average appear, you have numbers.

Know your outliers

Color scales stretch from the range’s minimum to its maximum. One freak value — a £1m one-off in a grid of £10k cells — compresses every other cell into near-identical shades and the map dies. Scan for extremes now (sort, or =MAX(range) vs =LARGE(range,2)); Step 3’s percentile anchors are the cure.

Decide the comparison you actually want

One rule over the whole matrix compares every cell against every other — right for “where are the global hot spots?”. One rule per row compares each region only against its own months — right for “what is each region’s seasonal shape?” when regions differ wildly in size. Decide which question you are answering before you format; it changes the story completely.

1.Step 1 — Lay Out the Matrix Data

A heat map wants a cross-tab, not a flat list. Put one categorical dimension across the columns (months in B1:G1, say), the other down the rows (regions in A2:A9), and the metric in the body (B2:G9) — one number per cell, every cell filled. For this guide’s running example, imagine monthly sales (£k) for eight sales territories over six months: an 8×6 grid of 48 values.

If your data arrives as a flat transaction list — date, region, amount, one row per sale — pivot it first. Either insert a PivotTable with regions on Rows, months on Columns, and Sum of amount in Values (our pivot table guide walks through it), or build the cross-tab with a formula in every body cell:

…where column D holds amounts, B holds regions, and C holds month labels. The mixed anchoring ($A2, B$1) lets one formula fill the whole grid: drag right and down and every cell picks up its own row’s region and column’s month. The formula-built cross-tab has one advantage over a pivot for heat maps: conditional formatting on a plain grid never gets disturbed by pivot refreshes.

Keep the matrix compact — a heat map works best when the whole grid fits on screen. If you have 200 rows, the map still works mechanically, but consider grouping into a coarser dimension first; nobody pattern-scans 200 rows.

2.Step 2 — Apply a 3-Color Scale

This is the whole trick, and it takes four clicks. Select the body of the matrix — values only, B2:G9, not the labels and emphatically not any totals row — then:

Home → Conditional Formatting → Color Scales → Green–Yellow–Red Color Scale (top-left preset).

Instantly, the highest value in the selection turns full green, the lowest turns full red, the median sits at yellow, and every other cell gets a smoothly interpolated shade based on where its value falls. Your 48 numbers are now a picture. Hover over the presets before clicking — Excel live-previews each scheme on your actual data, which is the fastest way to choose.

Two things to understand about what just happened. First, the scale is relative to the selection: min, midpoint, and max are computed from the selected cells, so adding the totals column to the selection would hijack the scale (totals are always the “hottest” cells, washing everything else out). Second, the formatting is live: change any value and its color — and potentially every other cell’s color, if the min/max moved — updates immediately. There is no refresh step.

3.Step 3 — Customize the Midpoint & Use Percentiles

The default scale is honest but dumb: green = best in this range, red = worst in this range, yellow = median. Often that is not the story you need to tell. Open the engine room: select the range, Home → Conditional Formatting → Manage Rules → select the scale → Edit Rule. You get six controls — a Type, Value, and Color for each of Minimum, Midpoint, and Maximum — and they are where every serious heat-map decision lives.

Anchor the midpoint to a meaning, not the median

Change the Midpoint Type from Percentile to Number and enter your target — say 50 (£50k). Now yellow means on target, green means above target, and red means below target, regardless of how the data happens to be distributed. This single change converts a “relative ranking” map into a “performance vs plan” map — usually the map management actually wanted. You can also point the Number at a cell (type =$J$1) so the whole map re-pivots when you change the target in one cell — exactly what the midpoint slider in the widget below simulates.

Use percentile anchors to tame outliers

Set Minimum to Percentile 5 and Maximum to Percentile 95. Everything at or beyond those percentiles gets the full end color, and the gradient spends itself on the middle 90% of the data where the real variation lives. This is the standard cure for the “one giant value makes everything else the same color” problem flagged in the prep section — and it is how professional tools scale their heat maps by default.

Or hard-code the ends

Set Minimum and Maximum to fixed Numbers (0 and 100 for percentages, −1 and +1 for correlations) when the scale should mean the same thing across different sheets, months, or reports. Data-relative scales make this month’s map incomparable with last month’s; fixed scales make them a series.

4.Step 4 — Single-Color (2-Color) Scales

Not all data has a good end and a bad end. Page views by hour, transactions by branch, headcount by team — these are magnitudes, and painting the small ones red quietly tells your reader they are failures. For magnitude data, use a 2-color scale: Home → Conditional Formatting → Color Scales, bottom row of presets, or Edit Rule and switch the Format Style to 2-Color Scale.

The professional recipe is white to a single saturated hue: set Minimum’s color to white and Maximum’s to a strong blue (or your brand color). Intensity now encodes size — darker simply means more — with no moral judgment attached. This is the “sequential” palette in visualisation language, versus the “diverging” green-yellow-red of Step 2, and choosing correctly between them is the single most telling sign of whether a heat map was made thoughtfully:

The blue-white-red diverging scale deserves special mention: it reads correctly for the ~8% of men with red-green colour-vision deficiency, it photocopies legibly because the ends differ in lightness, and for signed data (anything that can be negative) white-at-zero is the natural anchor. The widget below lets you flip between green-yellow-red and blue-white-red on the same data — notice how the blue-white-red version makes the “below midpoint” cells feel cool rather than alarming.

5.Step 5 — Icon Sets vs Color Scales

Conditional formatting offers a second visual language: icon sets — traffic lights, arrows, ratings — under Home → Conditional Formatting → Icon Sets. They answer a different question than color scales, and knowing which to reach for matters.

A color scale is continuous: it shows where in the distribution each value sits, with infinite gradations. An icon set is discrete: it buckets every cell into 3–5 statuses. Use color scales when the reader should perceive an overall pattern across the matrix (the heat-map use case); use icon sets when each cell needs an unambiguous verdict — pass/warn/fail against fixed thresholds — as in a KPI scorecard where “orange-ish green” is not an acceptable answer.

If you use icons, fix the thresholds immediately: Edit Rule, change each threshold Type from Percent to Number, and enter real boundaries (green ≥ 95% of target, amber ≥ 80%, red below). The default percent thresholds re-bucket themselves as data changes — meaning a cell can “go red” because other cells improved, which is indefensible in a business review. Also tick Show Icon Only if the number itself is secondary.

The two combine well: a color scale across a sales matrix for pattern, plus an icon-set rule on the totals column for verdicts. They are separate rules and coexist happily on different ranges. (For full scorecard patterns, see our KPI dashboard guide.)

6.Step 6 — Build a Calendar Heatmap

The calendar heatmap — GitHub’s contribution graph is the famous one — shows a daily metric as a grid of week × weekday squares, and it is superb at exposing weekly rhythm and seasonal drift simultaneously. Excel builds it from three ingredients: a date grid, a lookup, and a color scale.

The date grid. Put your start date (a Monday) in B1. Lay weekday labels Mon–Sun across C1:I1 and week numbers (or week-commencing dates) down B2:B53 with =B2+7. Each body cell’s date is its week start plus its weekday offset — in C2: =$B2+(COLUMN()-COLUMN($C2)), dragged across and down. Hide this layer later, or compute dates inline.

The values. With your daily data in two columns (dates in Dates, metric in Values), each calendar cell pulls its day’s figure:

The look. Apply a white-to-green 2-color scale (Step 4 — daily activity is magnitude data). Then make the cells square: select the grid’s columns and set the width to ~3, and the rows to a matching height. Finally hide the numbers so color carries the message: Ctrl+1 → Custom → format code ;;; — the values vanish visually but stay in the cells, so the color scale and the formula bar still see them.

The result reads instantly: vertical stripes are weekday effects (“we’re dead on Sundays”), horizontal bands are seasonal (“August is quiet”), and isolated dark squares are events worth investigating. It is a remarkable amount of insight for one formula and one formatting rule.

7.Step 7 — Build a Correlation-Matrix Heatmap

The analyst’s heat map. When you have several numeric series — marketing spend, web sessions, sales, returns, weather — a correlation matrix shows how every pair moves together, and a heat map is the only sane way to read one bigger than 4×4.

List your variable names both across the top (B1:F1) and down the side (A2:A6) of a square grid. Each body cell computes the Pearson correlation for its row/column pair. With the underlying series in named ranges, B2 is =CORREL(Sales,Sales), C2 is =CORREL(Sales,Spend), and so on. To fill the whole matrix with one draggable formula, point INDEX at the data block (series in columns B:F of a Data sheet, 100 rows):

(Alternatively, Data → Data Analysis → Correlation generates the matrix in one step if the Analysis ToolPak is enabled — though its output is static.) Now the crucial formatting decision: this is the textbook case for fixed anchors from Step 3. Edit the color-scale rule and set Minimum = Number -1 (blue), Midpoint = Number 0 (white), Maximum = Number +1 (red). Never let a correlation map auto-scale — a matrix whose correlations span only 0.2 to 0.6 would otherwise paint 0.2 as fully “cold”, wildly overstating the spread.

Reading it: the diagonal is always 1 (deep red — each variable with itself); strong red off-diagonal cells are variables that rise together; strong blue cells move oppositely; near-white is noise. Format the numbers to two decimals (0.00). Correlation flags relationships but proves nothing about cause — when a pair lights up and you want to model the relationship properly, our regression analysis in Excel guide is the next step.

8.Step 8 — Make It Readable: Numbers, Borders, Legend

The gap between “cells with colors” and an agency-grade heat map is five formatting decisions, each thirty seconds of work.

Round the numbers. Six significant digits fight the colors for attention. Show £k with the custom format #,##0,"k" or one decimal at most; for percentages, whole points. If the matrix is dense (calendars, big correlation grids), hide the numbers entirely with ;;; and let color do all the work.

Add separating borders. Adjacent gradient fills smear into each other. Give every cell a thin border in the background color (white on a light sheet, near-black on dark): select the matrix, Ctrl+1 → Border, pick the color, apply outline + inside. The cells become discrete tiles and the map sharpens dramatically.

Shape the cells. Heat-map tiles read best square-ish — widen rows (16–22pt) and trim columns so tiles approach squares. Center the values horizontally and vertically.

Add a legend. Colors without a key are decoration. Build one in 60 seconds: in a spare row, enter the scale’s min, midpoint, and max values in three cells, apply the same color-scale rule to those three cells, and label them (“below target / on target / above target”). Place it directly beneath the matrix.

Title the comparison. One line above the map saying what the colors mean — “Sales vs £50k target, by region and month” — pre-empts the only question every viewer otherwise asks. State whether the scale is global or per-row if there is any chance of confusion.

Try It: Live Heat Map

Here is an 8×6 sales matrix (£k by territory and month) rendered exactly as Excel’s conditional formatting would render it. Use the controls the way you would use the Edit Rule dialog: flip between the green–yellow–red and blue–white–red diverging schemes, and drag the midpoint slider — the equivalent of changing the Midpoint Number in Step 3 — to watch the entire map re-pivot around your “target”. Slide it low and almost everything looks healthy; slide it high and the same numbers look like a crisis. Same data, different story: that is why the midpoint is a decision, not a default.

Advanced Heat Map Tips

Heat-map a pivot table

Color scales work on pivot bodies, with one catch: a plain rule can detach when the pivot refreshes or re-shapes. When you apply the rule, click the formatting-options icon that appears and choose to apply it to all cells showing the value field — the rule then follows the field through refreshes, pivots, and drill changes.

Drive the midpoint from a cell

In the Edit Rule dialog, the Number boxes accept references: type =$J$1 as the Midpoint value, put the target in J1, and the entire map re-pivots whenever the target changes — perfect for “what does the map look like at a £60k target?” conversations. (This is precisely what the widget’s slider does.)

Conditional formats are paintable

Format Painter copies conditional formatting rules along with everything else. Build one perfect heat-mapped block, then paint the rule onto other matrices — far faster than rebuilding the rule, and it guarantees identical scales when that is what you want.

From static map to live dashboard

A heat map in a workbook still requires opening the workbook. Upload the data to DataHub Pro and it becomes a live, shareable heat map that recolors itself as new data lands — with the midpoint, scheme, and percentile-clipping options from this guide available as toggles rather than dialog spelunking.

Worked Example: Finding the Story in 48 Numbers

Take the widget’s data: eight territories, six months, sales of £18k–£88k. As a plain table, a diligent reader can extract maybe two facts a minute. Apply the default green-yellow-red scale and three patterns surface in seconds. A hot row: South East glows green across all six months — the territory is simply bigger, and (per the prep-section point) if you wanted its monthly shape instead, you would re-apply the rule per-row. A cold column drifting warm: January is the reddest column with the whole grid warming left to right — a seasonal recovery every territory shares. One anomaly: North West, comfortable all spring, drops sharply in June while every neighbour rises. That one rogue cool cell in a warming grid is exactly what heat maps exist to catch — in the table it was just “31”.

Now make it a management report. The company’s monthly target per territory is £55k, so per Step 3 you set the Midpoint to the Number 55: yellow now means on-target. The story sharpens — only two territories are reliably above plan, three hover at it, and three have never reached it. Slide the widget’s midpoint to 55 and watch it happen. Then slide to 40 (last year’s target) and the same grid turns reassuringly green; slide to 70 (the stretch goal) and it turns alarming. Nothing about the business changed between those three pictures — only the question being asked. Choosing the midpoint is choosing the question.

Finally the scheme: this matrix is performance-vs-target, so a diverging palette is right, and if the deck will be printed or the audience is large, the blue-white-red variant is the safer diverging choice. Add the white tile borders, #,##0,"k" number format, a three-cell legend labelled “below / on / above target”, and the title “Monthly sales vs £55k target” — and the 48 numbers have become a slide that briefs itself.

Common Errors & Fixes

All the cells look the same color

An outlier (or an accidentally included totals row/column) is stretching the scale. Re-select the body cells only, and set Minimum/Maximum to Percentile 5/95 in Edit Rule so the gradient spends itself on the real variation.

Some cells have no color at all

Those values are text, not numbers — color scales skip text silently. Convert with Data → Text to Columns → Finish on the column, or multiply by 1, and the colors appear. (Cells outside the rule’s Applies To range also stay uncolored — check Manage Rules.)

New rows aren’t picking up the heat map

The rule’s Applies To range is fixed and the data outgrew it. Either extend the range in Manage Rules, or — better — convert the data to an Excel Table first so the formatting extends automatically with the data.

The colors changed when I sorted/refreshed

That is correct behaviour: the scale is relative, so any change to the range’s min/max re-shades everything. If colors must be stable and comparable across time, anchor Minimum, Midpoint, and Maximum to fixed Numbers (Step 3).

Copy-paste broke the heat map

Pasting values over a heat-mapped range keeps the rule, but pasting a heat-mapped range elsewhere with plain paste brings the rule along and can fragment it into dozens of overlapping rules. Use Paste Special → Values when you only want numbers, and periodically tidy Manage Rules → Show formatting rules for: This Worksheet.