Excel Heat Map UK: Create and Format One in Microsoft 365

How-to basics · Updated November 19, 2026

To make a heat map in Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a colour scale. Excel shades each cell according to its value, making patterns such as high and low sales, attendance or response times easier to spot; the UK setting does not change how the feature works.

In Excel, a heat map usually means a table whose cells are coloured according to their numbers, not a geographical map of the United Kingdom. The colour scale is conditional formatting: it updates when the values change, rather than replacing them with manually applied fills. The useful question is not just which colours to pick, but what comparison the colours should communicate.

What an Excel heat map shows

A heat map turns a grid of values into a visual pattern. A pale-to-dark scale might make smaller values look lighter and larger values darker; a three-colour scale can also distinguish values near a middle point. This is useful when a table has too many numbers to scan individually, for example when comparing monthly results across several regions or products.

Colour is a cue, not an explanation. A dark cell means only what the rule and its comparison range define. If the scale is calculated separately for each row, the darkest cell in every row could represent a different number. A reader might otherwise assume that all dark cells indicate the same absolute value. A clear title, meaningful labels and a note about the scale help prevent that misunderstanding.

Excel heat maps are also different from chart-based maps that place values on a geographic outline. Conditional formatting colours the cells in a worksheet table; it does not draw UK boundaries or position data by location.

Create a heat map step by step

Start with a rectangular set of numeric values. Put descriptive row and column labels outside the cells you want to colour, and avoid including totals unless you want those totals included in the colour comparison. Then:

  1. Select the values, such as B2:G13. Do not select the headings if you want only the numbers shaded.
  2. On the ribbon, choose Home > Conditional Formatting > Color Scales.
  3. Choose a two-colour or three-colour option. Excel applies a preview and shades the selected cells according to their values.
  4. Check whether the result communicates the intended comparison. If not, adjust the scale or the selected range rather than choosing colours at random.

The rule remains attached to the cells and recalculates as their values change. To inspect it, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. The Rules Manager lets you review the rule, its range and its settings. If several rules apply to the same cells, check their order and whether a rule is set to stop other rules from applying.

In Excel for Mac, Conditional Formatting and Color Scales are also available from the Home tab, though ribbon layout and dialog details can vary by release. Excel for the web includes conditional formatting in its Home ribbon too; some rule-editing controls may be arranged differently from the Windows desktop version.

Choose a scale that fits the question

Use a two-colour scale when the main message is simply lower versus higher. A three-colour scale is useful when a meaningful midpoint matters, such as values below, around and above a target. Excel offers preset scales, but a preset is not automatically suitable for every dataset. A red-to-green scale, for example, may imply danger versus success even when the numbers are not performance scores.

For a scale with a precise meaning, open Home > Conditional Formatting > Manage Rules, select the rule and choose Edit Rule. The available settings let you set the minimum, midpoint and maximum types and values for a colour scale. Depending on the selected type, a boundary can be based on the lowest or highest value, a number, a percentage, a percentile or a formula. Use a fixed number when the same threshold should mean the same thing across different tables; use the lowest and highest values when the goal is to show the relative spread within one selection.

Be cautious with percentages and percentiles: they do not mean the same thing as a fixed number. A percentile boundary describes a position in the distribution, whereas a number boundary identifies a particular value. For example, if a target is 100 units, setting a numeric midpoint of 100 conveys that target more consistently than using the 50th percentile, which depends on the data.

A practical UK example

Imagine a worksheet with UK sales regions in rows and months in columns. The cells contain monthly sales amounts in pounds, and the aim is to see which region-month combinations are higher or lower within the selected year. Select only the monthly figures and apply a two-colour scale. Add a heading that says what the amounts represent and identify the period. Apply a suitable pound currency format separately if readers need to see the units in the cells; number formatting and conditional formatting are independent.

This example uses a relative scale: the darkest value is the largest in the selected range, not necessarily a successful result. If a business target is, say, £10,000, a relative scale alone may be misleading when every month is below target. A fixed threshold or a separate target column can make the comparison clearer. The same principle applies to UK postcode-area data, school results or weather figures: the colour should express a defined comparison, not imply a conclusion that the numbers do not support.

For comparisons between years, consider whether to apply one rule to all years together or separate rules to each year. A single range makes the colours comparable across years. Separate ranges make variation within each year more visible, but identical colours may then represent different values. State which approach you used when the difference could affect a decision.

UK number formats, dates and regional settings

Excel heat-map colour scales respond to the underlying numeric values, not to whether a cell displays a pound sign, a comma or a date format. In a UK-configured workbook, you might display amounts as pounds sterling and dates in a familiar UK format, while still applying the same Conditional Formatting commands. Changing a cell to display two decimal places does not, by itself, change the stored numeric value used by the scale.

One common source of trouble is numbers imported or pasted as text. A value that looks like 1,250 may not be treated as the number 1250 if it has been imported as text, depending on the source and regional conventions. Such cells may be ignored by the scale or behave differently from numeric cells. Check alignment and number formats, and test a cell by changing its format or using Excel’s error indicators; do not assume that appearance proves the data type.

UK dates can also be confused with dates from other regional formats when data comes from another system. A date displayed as 04/05/2026 can be interpreted differently depending on the source settings. If a date-based heat map is intended, verify the imported dates before applying formatting. Excel stores valid dates as serial numbers, so a colour scale applied directly to dates reflects those serial values, not a calendar-aware measure such as days overdue. For overdue status, a purpose-built rule may be more appropriate than a simple colour scale.

Make the result easier to read

Colour scales can make large tables clearer, but they can also create visual clutter. A few practical choices improve interpretation:

  • Use a restrained palette with enough contrast to distinguish low, middle and high values.
  • Keep labels, units and the period visible so the reader can interpret the numbers without guessing.
  • Do not rely on red and green alone. Some readers have colour-vision differences, and printed or greyscale copies may lose distinctions. Use labels, values or another visual cue as well.
  • Keep the scale consistent across tables when people are expected to compare them.
  • Consider hiding decimals only if the rounded display still supports the decision being made.

Conditional formatting does not add a legend automatically. If the colour meaning is not obvious, add a short explanation near the table, or create a small labelled key. A legend is especially useful for a report shared with people who did not build the workbook.

Be careful when sorting or filtering a formatted range. A correctly applied rule generally follows its cell range, but a range that excludes newly added rows may leave those rows unformatted. Convert a well-structured dataset to an Excel table when appropriate, and verify the rule’s Applies to range after adding data. Tables can make expanding datasets easier to manage, but still check the formatting rule rather than assuming every change has been included.

Common problems and how to fix them

If all cells appear almost the same colour, inspect the values for outliers. One unusually large or small number can stretch a scale so that the rest of the cells look alike. You can choose fixed boundaries in the rule settings when that fits the task, or investigate the unusual value. Do not remove a genuine outlier solely to make the colours look more dramatic.

If some cells do not change colour, check whether they contain text, blanks or errors, and whether the rule’s Applies to range includes them. Check also for another conditional formatting rule that takes precedence or applies a competing format. To clear an unwanted rule, select the affected cells and use Home > Conditional Formatting > Clear Rules, choosing whether to clear rules from the selected cells or the entire sheet. Clearing rules removes conditional formatting; it does not delete the cell values.

Blank cells do not represent zero. If a missing observation should be treated as zero for analysis, enter or calculate a numeric zero deliberately; if it means not reported, leaving it blank may be more honest. A heat map cannot determine why a value is missing. Similarly, a zero may be a real measurement rather than missing information, so decide what the data means before changing it.

Finally, a colour scale can be visually persuasive without being statistically meaningful. A very small difference may receive visibly different colours, while a large outlier may compress the rest of the range. Always read the underlying values and define the comparison range before drawing a conclusion.

When to use another Excel option

A colour scale is a good choice when the task is to scan a dense numeric grid for relative patterns. For a simple pass/fail threshold, Home > Conditional Formatting > Highlight Cells Rules can be more direct, because it highlights cells meeting a condition rather than grading every value along a scale. For categories such as department names, a chart or a consistently labelled table may be clearer than assigning colours to text.

If the goal is to compare a small number of totals, a bar chart can make differences easier to judge than a coloured grid. If the goal is to show how values change over time, a line chart may communicate the trend more clearly. A heat map is not a substitute for choosing the right visual: use it when the grid structure itself matters, such as comparing many locations across many months.

Frequently Asked Questions

Is there a heat map chart button in Excel?
Excel’s common worksheet heat map is made with Conditional Formatting, not a separate chart type. Select numeric cells and choose Home > Conditional Formatting > Color Scales.

Does Excel have a UK heat map setting?
No separate UK heat-map setting is needed. The colour-scale feature works the same way; UK regional settings mainly affect how values, dates and currencies are displayed or interpreted when imported.

Can I make a heat map with fixed thresholds?
Yes. Edit the colour-scale rule through Home > Conditional Formatting > Manage Rules > Edit Rule and set suitable numeric boundaries. Fixed thresholds are useful when the same target should mean the same thing across multiple ranges.

Why are some cells not included in my heat map?
They may be outside the rule’s Applies to range, stored as text, blank, or affected by another conditional formatting rule. Check the range and data before recreating the scale.

Can I print an Excel heat map?
Yes. Conditional formatting appears in print output when the cells are included in the print area, but colour reproduction depends on the printer and whether it prints in colour. Preview the worksheet before printing and include a legend or labels if readers need to understand the scale.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.