To make a heat map scatter plot in Excel, plot your X and Y measurements as an XY scatter chart, then divide the points into value ranges and format each range as a separately colored series. Excel does not apply conditional formatting directly to individual chart markers, so helper columns are the practical way to create a chart whose point colors represent a third variable.
What a heat map scatter plot shows
A standard scatter plot positions each observation using two numeric measurements: one on the horizontal X axis and one on the vertical Y axis. A heat map scatter plot adds a third measurement, such as temperature, sales, risk, or concentration, by changing the points’ colors. For example, a facilities team might plot room locations using X and Y coordinates, then color each room’s point according to its measured temperature.
This is different from a heat map made from a table of colored cells. Excel’s cell heat map uses conditional formatting to shade cells according to their values; it does not place observations at numeric X/Y coordinates. A scatter chart is the better fit when the position of each observation matters and both axes represent continuous measurements.
Color can show a continuous gradient in some charting tools, but Excel’s ordinary XY scatter chart does not provide a built-in rule that dynamically assigns a different marker color to every point based on a third column. Excel formats chart series, not individual markers through a conditional-formatting scale. Splitting the observations into ranges gives you a useful, maintainable approximation.
Prepare the data and choose meaningful ranges
Put one observation on each row, with numeric X, Y, and intensity values in separate columns. In the example below, intensity runs from 0 to 100. Decide what the colors should mean before building the chart: equal-width ranges are easy to explain, while ranges based on operational thresholds may be more useful to readers.
- A: X coordinate, such as distance east or elapsed time.
- B: Y coordinate, such as distance north or a second measurement.
- C: intensity value that determines the point’s color.
Keep the headers in row 1 and the records in rows 2 through 101, or adjust the ranges to match your dataset. Check that X and Y are genuinely numeric and that intensity values use a consistent scale. Text such as “high” cannot be compared with numeric thresholds without first mapping the categories to numbers or using a separate category-based approach.
For a first chart, three bands—low below 33, middle from 33 up to but not including 67, and high at 67 or above—are straightforward. These cutoffs are examples, not statistical rules. If a score of 70 has a specific business meaning, use that threshold rather than choosing boundaries only because they divide the scale evenly.
Create helper columns for the color bands
Add six helper columns: D and E for low-band X and Y values, F and G for middle-band values, and H and I for high-band values. Each formula returns the relevant coordinate when a row belongs to that band and returns NA() otherwise. Excel charts normally omit points whose plotted coordinate is #N/A, leaving each series with only its matching observations.
With X in A2, Y in B2, and intensity in C2, enter this low-band X formula in D2. For a sample row with A2=12, B2=8, and C2=18, it returns 12.
=IF($C2<33,$A2,NA())
With the same layout, enter this low-band Y formula in E2. For that sample row, it returns 8.
=IF($C2<33,$B2,NA())
With X in A2, Y in B2, and intensity in C2, enter this middle-band X formula in F2. For a row with A2=20, B2=14, and C2=52, it returns 20.
=IF(AND($C2>=33,$C2<67),$A2,NA())
With the same layout, enter this middle-band Y formula in G2. For that sample row, it returns 14.
=IF(AND($C2>=33,$C2<67),$B2,NA())
With X in A2, Y in B2, and intensity in C2, enter this high-band X formula in H2. For a row with A2=6, B2=30, and C2=82, it returns 6.
=IF($C2>=67,$A2,NA())
With the same layout, enter this high-band Y formula in I2. For that sample row, it returns 30. Copy all six formulas down through the final data row. The dollar signs keep each formula pointed at columns A, B, and C as it is filled across or down, while the row number adjusts for each record.
=IF($C2>=67,$B2,NA())
These formulas assume intensity is numeric and that values below 33, from 33 to below 67, and at least 67 are the desired bands. If your measurements have blanks or errors, decide how to handle them first; otherwise, errors can flow into the helper columns and make the chart harder to interpret. For values on a different scale, change the thresholds consistently in the formulas and in the legend.
Insert and configure the scatter chart
- Select a blank area of the worksheet. On Excel for Microsoft 365 on Windows, choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. This creates an empty chart that you can populate with the helper ranges.
- Select the chart, then choose Chart Design > Select Data > Add. Give the first series a useful name, such as Low. Set its series X values to the low-band range in column D and its series Y values to column E, excluding the header.
- Use Add again for Middle, using columns F and G, and for High, using columns H and I. The ranges must cover the same rows. If Excel includes a header or treats the values unexpectedly, reopen Select Data and correct the series ranges.
- Choose a scatter subtype that shows markers without connecting lines. Connecting lines imply a sequence or path and can mislead when the observations are independent.
- Format each series separately. Select its markers, open the Format Data Series pane, and use Fill & Line > Marker to set a distinct fill color and marker size. For example, use a cool color for low values, a neutral color for the middle band, and a warm color for high values.
Add a chart title, axis titles, and a legend that spells out the cutoffs—for example, “Low: <33; Middle: 33–<67; High: ≥67.” A legend that says only Low, Middle, and High may leave readers guessing what the categories mean. If the X and Y values represent physical positions, set suitable axis bounds and units so the geometry is not distorted by an arbitrary scale.
Choose colors, markers, and scales carefully
Use a small number of clearly distinct colors. A progression from cool to warm is familiar, but color meaning is not universal, so label the ranges rather than relying on color alone. Avoid combinations that are difficult to distinguish for people with color-vision deficiencies; differences in marker shape or a direct label can provide an additional cue. Keep marker sizes large enough to see, but not so large that dense areas turn into solid blobs.
Scatter charts can overlap points. If many observations share similar coordinates, one marker may conceal another, making the apparent density misleading. Try smaller markers, some transparency if available in the chart-formatting controls, or a carefully chosen jitter applied to a separate display-only coordinate. Jitter shifts points slightly, so document it and do not use it when exact locations are essential.
Axis bounds also affect the visual impression. If a chart uses unequal X and Y scales for data that represents a map or a physical plane, distances and shapes may appear distorted. Set the bounds to the meaningful extent of the data and consider matching the scale where equal distances on both axes should look equal on screen. Do not remove inconvenient points merely to make the pattern more dramatic.
Keep the chart current as data changes
When you add records below a fixed range such as rows 2 through 101, the formulas and chart may not include the new rows automatically. One simple improvement is to convert the source data into an Excel table using Insert > Table, then extend the helper formulas as new observations are added. However, a chart series built from fixed cell references may still need its ranges updated, so verify the chart after expanding the dataset.
For a small report, updating the series ranges manually is often sufficient. For a recurring dashboard, plan the data layout so the source and helper ranges can expand reliably, and test the process with a newly added row. If thresholds change, update every relevant formula and the legend together; otherwise the colors and labels can disagree.
Common problems and how to fix them
- All points appear in one color: The chart may contain one series rather than three. Open Chart Design > Select Data and confirm that Low, Middle, and High are separate series.
- Some points are missing: Check the intensity value and the band boundaries. A value of exactly 33 belongs to the middle band, and exactly 67 belongs to the high band in the formulas above. Also check for blanks, text values, and formula errors.
- The chart shows a line: Change the chart type to a marker-only scatter subtype, or remove the line in the series formatting options. A connected line is usually not appropriate for independent observations.
- Colors do not update when values change: Confirm that the helper formulas were filled down and that each series uses the helper columns, not the original X and Y columns. The formulas recalculate the category, but the legend text and thresholds still need deliberate maintenance.
- A color scale is not available for markers: Conditional Formatting applies to worksheet cells, not as a dynamic per-point color scale for ordinary scatter-chart markers. Use separate series for bands, or choose another visualization.
When to use a different Excel chart
If the data is arranged in a regular grid—such as temperature by day and hour—a cell heat map may be clearer. Select the value cells and choose Home > Conditional Formatting > Color Scales. That colors the worksheet cells according to their values and is not an XY scatter chart, but it can reveal patterns across a matrix quickly.
If exact individual values matter more than color, use a scatter chart with data labels or a table alongside it. If the third variable is a magnitude that should be read as size, a bubble chart can encode it using bubble size, though bubbles overlap and are harder to compare precisely. For a continuous, automatically varying color gradient on every point, a specialized visualization tool or custom development may be more suitable than Excel’s standard scatter chart.
Frequently Asked Questions
Can Excel make a scatter plot with a color gradient for each point?
Not directly through conditional formatting in a standard XY scatter chart. The usual Excel workaround is to divide points into a few bands, create a series for each band, and assign each series a color. That gives discrete colors rather than a continuous gradient.
Can I make a heat map from X, Y, and Z values in Excel?
Yes. Treat X and Y as the scatter coordinates and Z as the value used to assign a color band. Helper columns can separate the coordinates into low, middle, and high series. A regular scatter chart will not automatically turn Z into a point-by-point color scale.
Why does my Excel scatter chart show #N/A in the worksheet?
The helper formulas intentionally return NA() for rows outside a series so those observations are not plotted in that series. The worksheet may display #N/A in those helper cells; the chart uses the other band’s coordinates for those records. Keep the original data columns intact.
Can I use conditional formatting to color the chart markers?
No. Excel conditional formatting changes worksheet cells; it does not apply a cell color scale to individual markers in a standard scatter chart. You can format each chart series manually, or use a chart type or tool designed for point-level color encoding.
What is the difference between a heat map and a heat map scatter plot?
A cell heat map shades a grid of cells, usually with Home > Conditional Formatting > Color Scales. A heat map scatter plot places observations by numeric X and Y coordinates and uses color to show another value. Choose based on whether the grid layout or the individual coordinates matter.
Related
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
- Excel Table Data Filtering: How to Narrow Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Wrap Text in Google Sheets on a PC: A GuideTo wrap text in Google Sheets on a PC, select the cell or cells, then choose Format >…
- Google Sheets Distinct Values: Find, Filter, and Count ThemTo list distinct values in Google Sheets, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your…
- GPT for Excel Data Analysis: Uses, Limits, and TipsYes. GPT tools can analyze Excel data: upload a workbook to ChatGPT with data-analysis features enabled, or use…
- Google Sheets Filter View: How to Lock ItGoogle Sheets has no separate lock switch for a filter view. To keep people from changing a saved…
- Excel Cells Showing Number Signs? Causes and FixesAn Excel cell showing number signs (####) usually means its column is too narrow to display a numeric…
- Superscript in Excel: How to Format Text and NumbersTo make text superscript in Excel for Microsoft 365 on Windows, select the characters in the cell, press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.