Excel Scatter Plot: Color Points by Value

In Excel for Microsoft 365 on Windows, the reliable way to color scatter-plot points by a value is to split the points into separate data series, one for each category or value range, and give each series its own marker color. Excel does not automatically apply a continuous color scale to the points in a standard scatter chart, but helper columns make useful value-based color groups straightforward.

How scatter-plot coloring works

A scatter chart plots each observation using an X value and a Y value. Excel applies marker formatting to a series, so points in one series ordinarily share a color. If your data has a third field—such as score, temperature, or sales—you can use it to decide which series each observation belongs to. A point’s position still comes from its X and Y values; the third field controls which colored series contains it.

This distinction matters because changing a cell’s fill color or applying Conditional Formatting to the worksheet does not automatically change the corresponding chart marker. The chart has its own formatting. To connect a value to a marker color in a repeatable way, arrange the chart data so that each color group is a separate series.

Categories: use one series per label, such as product type or region.
Numeric bands: group a score into ranges, such as low, medium, and high.
Exact numeric gradient: use individual point formatting or automation; a standard scatter chart does not link a continuous color scale to a value column automatically.

Color points by category

For a small number of categories, create separate X and Y columns for each category. Put the original X and Y values in those columns only when the row belongs to that category; use =NA() for rows that do not. Then add each X/Y pair to the chart as a separate series. Because the series have separate formatting, you can assign each category a distinct marker color.

Arrange the source data with columns for X, Y, and category. For example, columns A, B, and C might contain distance, response, and group.
Make a pair of helper columns for each group. The X helper should return the row’s X value for a matching category, and the Y helper should return its Y value; otherwise, each should return =NA().
Select the chart, then choose Chart Design > Select Data > Add. Enter the series name and select the matching helper ranges for the series’ X values and Y values.
Repeat for the remaining groups. Select a series and use Format Data Series to set its marker fill and outline.
Give the series clear names so the legend identifies the categories.

Using =NA() is generally preferable to returning zero: zero is a real numeric coordinate and would create a plotted point, while the chart does not plot the #N/A result. The cells may display #N/A, but those values are serving as exclusions from the chart rather than as measurements.

Example: color points by a numeric score

Suppose A2:A101 contains X values, B2:B101 contains Y values, and C2:C101 contains a score. You want scores below 50 to be blue, scores from 50 up to but not including 100 to be orange, and scores of 100 or more to be green. Create six helper columns: an X and Y pair for each band. The formulas below go in row 2 of those columns; fill them down through row 101.

With X in A2:A101, Y in B2:B101, and score in C2:C101, enter this low-band X formula in D2:

=IF($C2<50,$A2,NA())

With the same source layout, enter this low-band Y formula in E2:

=IF($C2<50,$B2,NA())

With X in A2:A101, Y in B2:B101, and score in C2:C101, enter this middle-band X formula in F2:

=IF(AND($C2>=50,$C2<100),$A2,NA())

With the same source layout, enter this middle-band Y formula in G2:

=IF(AND($C2>=50,$C2<100),$B2,NA())

With X in A2:A101, Y in B2:B101, and score in C2:C101, enter this high-band X formula in H2:

=IF($C2>=100,$A2,NA())

With the same source layout, enter this high-band Y formula in I2:

=IF($C2>=100,$B2,NA())

For example, if row 2 has X = 12, Y = 7, and score = 72, the middle-band formulas return 12 and 7; the low- and high-band formulas return #N/A. Add D/E, F/G, and H/I as three chart series, then set their marker colors. The ranges do not overlap, so each observation appears in only one color group. Adjust the thresholds to suit the meaning and spread of your own scores.

Add and format the chart series

Start with an XY scatter chart, not a line chart. Select the chart and choose Chart Design > Select Data > Add. In the Edit Series dialog, specify a series name, the X helper range, and the corresponding Y helper range. Repeat for every group. If Excel guesses the ranges incorrectly, open Select Data and edit the series rather than rebuilding the chart.

To change a group’s appearance, select one of its markers and open Format Data Series. Under Marker, choose the built-in marker options and adjust the fill and border. If you select a single marker rather than the whole series, Excel opens formatting for that data point, allowing a one-off color change. This is useful for highlighting an outlier, but it does not create a rule that will keep the point colored appropriately if the data changes.

On Excel for Mac, the chart concepts and helper-column approach are the same, although ribbon labels and dialog layouts can vary by release. Excel for the web can display and edit many charts, but chart-series editing options may be more limited than in the Windows desktop app; use desktop Excel if you cannot add or format the series you need.

Choosing value bands or individual colors

Value bands are usually the practical choice when a score has a meaningful interpretation. A three-color grouping makes a chart easier to scan and keeps the legend understandable. Set the limits based on meaningful cutoffs where possible; arbitrary bands can make small differences look more important than they are. If the data is highly skewed, equal-width ranges may leave one color with most of the points, so check how many observations fall into each group before settling on thresholds.

For a continuous value such as temperature, a smooth color gradient may seem more precise. Standard Excel scatter charts do not automatically map each row’s numeric field to a marker color scale. You can format individual points one at a time for a small, stable chart, but those manual choices need revisiting when values or row order change. For regularly refreshed data, use a manageable set of bands, a carefully maintained automation method, or a visualization tool that supports continuous color encoding directly.

More color is not always better. Use a palette with clear contrast, keep the same meaning for each color throughout the workbook, and do not rely on color alone to communicate a critical distinction. A legend, labels, marker shapes, or a table can make the chart more accessible and easier to interpret when printed in grayscale.

Common problems and fixes

Points disappear: check that each row meets one of the group tests and that the X and Y formulas use the same condition. A typo in a threshold can exclude observations.
Points appear at zero: the helper formula may be returning 0 for nonmatching rows. Return =NA() instead so those rows are not plotted.
Only one color appears: the groups may have been added as one series, or the same X/Y ranges may have been assigned to each series. Check each series in Select Data.
The legend is confusing: rename each series with a concise label such as Low, Medium, and High. The series name can come from a cell or be typed into the series dialog.
New rows are not included: helper formulas and chart ranges may stop at the original last row. Extend them and update the series ranges, or use an Excel Table for the source data and review the chart ranges as the table grows.

Also confirm that the chart type is an XY scatter chart. A line chart treats the horizontal axis differently and may not represent numeric X distances as intended. If points overlap, a correct color setup can still hide observations; smaller markers, partial transparency where available, or a different chart design may help.

Alternatives for continuous color and large datasets

For a chart with only a handful of observations, selecting a point individually and changing its marker fill is quick. It is a manual presentation choice, though, not a value-driven rule. Sorting the source data later or replacing a point can make manual formatting difficult to maintain.

For a larger dataset, helper series are more dependable when a few discrete groups are enough. If dozens of colors are needed, the legend becomes unwieldy and users may struggle to distinguish similar shades. Consider whether color should instead represent a few meaningful ranges, whether labels or small multiple charts would explain the data better, or whether a specialist visualization tool is a better fit. Excel’s bubble chart is another option when a third variable should control bubble size, but bubble size is not the same as color and can be harder to compare precisely.

Frequently Asked Questions

Can Excel color each scatter point automatically from a value column?
Not as a built-in continuous color scale for a standard scatter chart. Use helper series for value bands, or format individual points manually. The helper-series method is easier to update when the source data changes.

Can I use Conditional Formatting to color chart markers?
No. Worksheet Conditional Formatting changes cell appearance; it does not set the marker color of a scatter chart based on those cells. Create separate chart series or format chart points directly.

Why use NA() instead of a blank or zero?
A zero is a real X or Y coordinate and may appear as a point. NA() returns #N/A, which Excel charts generally do not plot, making it a convenient way to exclude rows from a helper series.

Can I use more than three colors?
Yes. Add another X/Y helper pair and a chart series for each additional category or range. Keep the number of groups small enough that the colors and legend remain readable.

Will the colors update when I change a score?
With helper formulas, the point moves to the series whose condition matches the new score, and therefore takes that series’ color. Manual point formatting does not automatically follow a changed value.

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.