Excel Scatter Plot: Color Points by Value Automatically

Excel can color scatter-plot points by value, but an XY scatter chart does not automatically apply a continuous color scale from a third column. For a chart that updates when values change, split the points into value-based series with helper formulas, then give each series its own marker color; for a one-off chart, format individual points manually.

What Excel can and cannot color automatically

A scatter chart plots each observation from an X value and a Y value. Excel assigns formatting to a data series, so every marker in one series normally shares the same style. It does not have a built-in scatter-chart control that reads a separate numeric column and automatically gives each point a different color on a continuous gradient.

The practical workaround is to make several series from the original observations. For example, one series can contain observations with a score below 50, another can contain scores from 50 through 79, and a third can contain scores of 80 or more. Each series gets a separate marker color. The chart still shows the original X and Y coordinates, while the extra value determines which color group a point belongs to.

This distinction matters when choosing a chart type. A bubble chart can use a third value to control bubble size, but that does not make marker color a continuously mapped third dimension. If color is intended to show exact values rather than broad ranges, use a color legend or labels as well; three bins cannot communicate every difference in the underlying numbers.

Prepare the source data and choose useful ranges

Put each observation on one row and keep the columns together. In the example below, column A contains the horizontal coordinate, column B the vertical coordinate, and column C the measurement that will determine color.

A1: X; B1: Y; C1: Score.
Rows 2 through 101 contain the observations.
Use consistent units and numeric values for X, Y, and Score. Keep headings in row 1, outside the plotted ranges.

Choose boundaries that make sense for the data, not just boundaries that make the chart look balanced. If a score has a meaningful target of 50 and a high-performance threshold of 80, those are reasonable cutoffs. If the values range from 0 to 10, cutoffs of 50 and 80 would put every point in the same group. Check the minimum, maximum, and distribution first, and decide whether the ranges should represent business categories, equal intervals, or another meaningful rule.

Also decide what a missing score means. A blank or text value should not accidentally be classified as low. The formulas below treat a nonnumeric score as unassigned, so a row without a valid score is not plotted in any of the color groups.

Create helper columns for the color groups

Add six helper columns: separate X and Y values for each of the three groups. Enter the labels Low X, Low Y, Mid X, Mid Y, High X, and High Y in D1:I1. Each formula either returns the original coordinate for points in its group or returns NA() for points outside it. Excel charts do not plot #N/A values, which keeps each point out of the other series.

With X in A2:A101, Y in B2:B101, and the numeric score in C2:C101, enter this in D2 for the Low-group X coordinate, then fill it down through D101: =IF(AND(ISNUMBER($C2),$C2<50),$A2,NA()). The matching Low-group Y formula, entered in E2 and filled down, is =IF(AND(ISNUMBER($C2),$C2<50),$B2,NA()).

With the same source layout, enter this Mid-group X formula in F2 and fill down: =IF(AND(ISNUMBER($C2),$C2>=50,$C2<80),$A2,NA()). For Mid-group Y, enter this in G2 and fill down: =IF(AND(ISNUMBER($C2),$C2>=50,$C2<80),$B2,NA()).

With X in A2:A101, Y in B2:B101, and Score in C2:C101, enter the High-group X formula in H2 and fill down: =IF(AND(ISNUMBER($C2),$C2>=80),$A2,NA()). Enter the corresponding High-group Y formula in I2 and fill down: =IF(AND(ISNUMBER($C2),$C2>=80),$B2,NA()). The ranges are inclusive at 50 for Mid and at 80 for High, so a score of exactly 50 is Mid and a score of exactly 80 is High.

For example, if A2 is 10, B2 is 18, and C2 is 42, the Low helper cells return 10 and 18; the Mid and High helper cells return #N/A. If C3 is 63, its coordinates appear only in the Mid helper columns. Check a few rows, including values exactly on each cutoff, before building the chart.

Add the helper ranges as chart series

Select a blank cell or the original X and Y data, then choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Starting with a blank chart avoids accidentally retaining the original data as an extra, uniformly colored series.
Right-click the chart and choose Select Data. In the Select Data Source dialog, choose Add.
For the first series, use the Low X heading or a descriptive series name. Set Series X values to D2:D101 and Series Y values to E2:E101. Remove any automatically included range markers if Excel inserts them, then confirm the series.
Repeat Add for Mid, using F2:F101 for X and G2:G101 for Y, and for High, using H2:H101 for X and I2:I101 for Y. The headings in row 1 can serve as the series names.
Choose OK to close the dialogs. The chart should show the original coordinates, divided among three series.

If the chart displays a single point for a group, or places points on unexpected axes, reopen Select Data and inspect the X and Y ranges. They must be the matching coordinate columns: for instance, Low X is D and Low Y is E, not the two columns from different groups.

Set a different marker color for each series

Click one marker in the Low series. If the whole series is selected, right-click and choose Format Data Series. In the Format Data Series pane, open Fill & Line, expand Marker, and set Marker Options to Built-in if you want to choose a marker shape or size. Under Fill, choose Solid fill and select a color. Under Border, choose Solid line and set a coordinating outline if needed. Repeat for Mid and High.

Choose colors that remain distinguishable at the chart’s actual size. A familiar low-to-high progression can work, but do not rely on red and green alone if readers may have color-vision deficiencies. Different marker shapes, clear series names, and a visible legend provide additional cues. If the legend lists generic names such as Series 1, rename the series through Select Data so it identifies the ranges meaningfully.

For a chart that will be printed in grayscale, use marker shapes or labels in addition to color. Also avoid using a saturated palette that makes a dense chart look like a solid block. Smaller markers, some transparency, or a larger plot area can make overlapping points easier to interpret.

Make the color groups respond to changing values

The helper formulas recalculate when the source values change, so a point can move from one color group to another when its score crosses a cutoff. The chart’s series ranges must still include the rows containing the data. When adding observations, fill the helper formulas down and extend the series ranges if they do not already cover the new rows.

To change the cutoffs, update the comparison values consistently in the relevant formulas. For instance, if the Mid group should end below 75, change its upper-bound test and the High group’s lower-bound test to 75. Keep the ranges nonoverlapping and complete: otherwise a boundary value could land in two groups or in none. For larger workbooks, storing cutoff values in cells and referring to those cells can make maintenance easier, but each helper formula must still use the same boundary definitions.

This approach is dynamic with respect to the group rules, but the formatting is attached to the series. The Low series stays its assigned color even if its current points happen to have high X or Y coordinates. That is expected: color represents the Score column, while position represents X and Y.

Manual coloring, continuous values, and alternatives

If there are only a few points and the data will not change, you can color a point directly. Click a marker once to select its series, then click that same marker again to select just the data point. Right-click and choose Format Data Point, then set its marker fill. This is quick for a presentation or a handful of exceptions, but the color does not automatically follow a changed score. Recheck manual formatting whenever the chart data is replaced or reordered.

For many observations or regularly updated data, the helper-series method is more dependable. It keeps the color assignment connected to a stated rule and makes the legend explain the groups. For a truly continuous numeric color scale, Excel’s standard XY scatter chart does not provide a native third-column color mapping. Consider whether binned colors communicate the decision clearly enough; if exact values are essential, add data labels selectively, include the score in a nearby table, or use a visualization tool designed for continuous color encoding.

Another common misconception is that applying Conditional Formatting to the worksheet cells will recolor the corresponding scatter markers. Conditional Formatting changes cell appearance; it does not automatically bind those cell fills to chart marker colors. Likewise, formatting one series does not format points according to their individual values unless the points have first been separated into series or formatted individually.

Common problems and checks

Every point has the same color: The chart may still contain the original X/Y range as one series. Remove it or build a blank chart and add only the helper series.
Some points are missing: Check for blank, text, or error values in the source columns, and confirm that formulas were filled down. A #N/A result is intentionally omitted; an unexpected #N/A usually means a source value or range needs attention.
A point appears in two groups: Inspect the boundary tests. One group should stop where the next begins, using consistent less-than and greater-than-or-equal comparisons.
Markers are difficult to select: Click a point once to select the series, then click it again to select the individual point. Zooming in or using the chart’s Format tab selection controls can help with tightly packed markers.
The chart omits new rows: Extend the formula ranges and each series’ X and Y ranges. A chart cannot plot rows that are outside its referenced ranges.
The legend is confusing: Use explicit names such as Low score, Mid score, and High score, and state the numeric cutoffs in the legend labels or a note beside the chart.

Frequently Asked Questions

Can Excel color each scatter point from a third column automatically?
Not as a built-in continuous color scale in a standard XY scatter chart. Split the points into value-based series for automatic group colors, or format individual data points for a static chart.

Can I color scatter points based on the Y value?
Yes. In the helper formulas, use the Y column as the condition instead of the separate Score column. For the example layout, that means changing the condition’s references from $C2 to $B2 while continuing to return the X and Y coordinates for qualifying rows.

Why does Excel show #N/A in my helper columns?
The formulas deliberately return NA() for points that do not belong to a particular group. Excel normally omits those entries from the chart, which is how each series contains only its assigned points.

Will the marker color change when I edit a score?
Yes, if the chart uses the helper formulas and its series ranges include that row. The formula moves the point to the appropriate group, whose marker color is already set. A point colored manually does not update according to its score.

Can I use a gradient from low to high?
Not directly for individual points in a standard Excel scatter chart using a third value. You can create more value bands and assign a sequence of colors, but each band is still a separate series and the result is a stepped palette, not a continuous gradient.

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.