Conditional Formatting Scatter Plots in Excel

How-to basics · Updated November 18, 2026

Excel does not apply worksheet conditional-formatting rules directly to scatter-plot markers. To color points dynamically, split the data into helper series using formulas, add each series to the chart, and assign it a marker color; as values change, points move between the series and their colors update.

Why scatter plots need a different approach

Conditional formatting in Excel is designed to change the appearance of cells, such as filling a cell when its value is above a threshold. A chart is a separate object: changing a cell’s fill color does not automatically change the color of the corresponding marker in an XY scatter chart. Applying a worksheet rule to the source range therefore will not, by itself, produce conditionally colored chart points.

The practical workaround is to represent each condition as a separate chart series. A series is a set of X and Y values that Excel plots together and formats with its own marker settings. Helper formulas return the original coordinates when a point meets a condition and return NA() when it does not. Excel does not plot the resulting #N/A values, so the chart displays only the points that belong to that series.

This approach works well for rules such as above versus below a target, ranges such as low, medium, and high, or categories based on a status column. It also updates when the source values change, without a macro.

Plan the data and the rule

Start with two numeric columns for the coordinates. For example, column A can contain an item’s measured temperature and column B its response time. Each row is one point. Suppose the rule is to show response times of 10 or more in orange and lower response times in blue. The threshold is based on Y, but either coordinate—or a separate category field—can determine the color.

Keep the source data together and put helper columns beside it or on another worksheet. Label them clearly so their purpose is easy to recognize. A simple layout is:

  • A: X value
  • B: Y value
  • C and D: X and Y values for the high group
  • E and F: X and Y values for the low group

Decide how blank cells and nonnumeric entries should behave before writing the rules. The formulas below require both coordinates to be numbers, so incomplete rows are not plotted. If your chart should include a point when only one field is blank, revise the criteria to match the meaning of your data rather than letting Excel interpret blanks unexpectedly.

Create formula-driven helper series

With X values in A2:A101 and Y values in B2:B101, enter this formula in C2 for the high group’s X coordinates. It returns the X value only when both coordinates are numeric and Y is at least 10; otherwise it returns #N/A.

=IF(AND(ISNUMBER($A2),ISNUMBER($B2),$B2>=10),$A2,NA())

With the same layout, enter this formula in D2 for the matching high-group Y coordinates.

=IF(AND(ISNUMBER($A2),ISNUMBER($B2),$B2>=10),$B2,NA())

With X in A2:A101 and Y in B2:B101, enter this formula in E2 for the low group’s X coordinates.

=IF(AND(ISNUMBER($A2),ISNUMBER($B2),$B2<10),$A2,NA())

With the same source layout, enter this formula in F2 for the low group’s Y coordinates.

=IF(AND(ISNUMBER($A2),ISNUMBER($B2),$B2<10),$B2,NA())

Copy C2:F2 down through row 101. The dollar signs lock the source columns while allowing the row number to change. For a row with X = 4 and Y = 12, the high-group formulas return 4 and 12, while the low-group formulas return #N/A. That point will appear only in the high series. A row with X = 7 and Y = 8 will instead appear in the low series.

Add the series to an Excel scatter chart

In Excel for Microsoft 365 on Windows, create a chart from the original coordinate columns by selecting the X and Y data, then choosing Insert > Insert Scatter (X, Y) or Bubble Chart and selecting a scatter style. A scatter chart is important because it treats the first range as numeric X coordinates and the second as numeric Y coordinates. A line chart uses a different horizontal-axis model.

To add the helper data, select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, choose Add. Give the new series a useful name, such as High, and set its X values to the helper range C2:C101 and its Y values to D2:D101. Add another series named Low with X values E2:E101 and Y values F2:F101. Keep the ranges aligned: each X value must pair with the Y value from the same row.

If the original all-points series remains on the chart, its markers may sit on top of the colored series and obscure them. In Select Data Source, remove that original series after confirming the helper series display correctly. Alternatively, format the original series with no marker and no line, leaving the helper series visible. For a scatter chart made only of markers, the helper series should also use a marker-only style.

Excel for Mac has comparable chart and series controls, though dialog and pane placement can differ. Excel for the web can handle many chart edits, but its options may be more limited or arranged differently; use the desktop app if you cannot add or format the required series in the browser.

Choose marker colors and handle more than two groups

Click a point in the High series, then open the formatting pane for the series. In the pane, use Fill & Line > Marker to set its marker fill and border. Repeat for Low and any additional groups. Excel formats a series consistently, which is why separating the points first makes the colors manageable. You can also adjust marker shape or size to make the groups distinguishable in grayscale or for readers who have difficulty distinguishing colors.

For three bands, such as below 5, from 5 through 10, and above 10, create three X/Y helper pairs and use mutually exclusive tests. For the middle band, a criterion such as Y greater than or equal to 5 and less than or equal to 10 includes both endpoints. Decide deliberately whether a boundary belongs to one group or another. Overlapping conditions can put the same point into multiple series, while a gap between conditions can leave a point unplotted.

When group membership comes from text rather than a numeric threshold, use the category column in the formula test. For example, if column C contains a status, make a helper series for each exact status value and return the source X or Y coordinate only for matching rows. Use consistent spelling in the source data; a misspelled or unexpected category will not match the intended rule.

Labels, legends, and chart readability

Series names such as High and Low appear in the chart legend and explain the colors. Choose names that describe the actual rule, such as Response time ≥ 10, rather than vague labels like Series 1. If the threshold can change, put it in a dedicated cell and reference that cell in the formulas; then update the cell rather than editing every formula. Keep the criterion understandable to someone reading the chart later.

Data labels are a separate choice from marker color. Excel can label points, but adding labels to a dense scatter plot may make it hard to read. If labels are needed, consider labeling only exceptional points or using a nearby table to identify them. You can also retain a source table with the original values and a status column, so readers can inspect exact observations without relying on marker position alone.

Use axis titles and sensible number formats so the chart communicates units. A marker colored as high is meaningful only if the viewer knows what the axes measure and what the threshold represents. If the chart is shared or printed, check that the legend, marker contrast, and labels remain legible at the final size.

Common problems and how to fix them

  • All points still have one color: The chart may still be plotting the original source series. Open Select Data and check which series are present; format the helper series individually.
  • Points disappear: Check the formulas and the X/Y ranges. A #N/A result is intentional for a point outside a group, but a formula error or an incorrectly selected range can hide points that should be included.
  • Points appear in the wrong group: Verify the criterion references the intended column and that boundary operators are correct. For example, >10 excludes 10, while >=10 includes it.
  • Coordinates do not line up: Ensure the X and Y helper ranges start and end on the same rows and correspond to the same source records. Do not sort one helper column independently of its paired column.
  • Blank records show at zero or look unusual: Add numeric checks such as ISNUMBER to the condition, as in the example formulas, and review whether text-formatted numbers need converting to actual numbers.
  • New rows are missing: The chart and formulas may still refer only to rows 2 through 101. Extend the formulas and series ranges, or convert the source data to an Excel Table and design the chart setup to include the expanding data.

One common misconception is that a conditional-formatting color scale applied to the worksheet can color a scatter plot’s markers automatically. It cannot. The worksheet rule changes cell appearance, while chart markers are formatted through chart series or individual data points. Helper series bridge that difference by making the rule determine which series receives each plotted point.

When to use another method

Helper series are usually the clearest formula-based method when colors should respond to changing values. Manually formatting individual points can work for a small, fixed chart, but those colors do not reliably represent a changing threshold or changing category. If the data changes, a manually colored marker may no longer belong to the group its color suggests.

For a chart with only one or two exceptional observations, selecting and formatting individual data points may be quicker than building several helper columns. For a recurring report, a helper-series design is easier to audit because the formulas make the membership rule visible. A PivotChart may help summarize categorized data, but it is not a direct substitute for an XY scatter chart when the exact numeric X/Y positions matter. VBA is another possible route for specialized chart automation, but it adds maintenance and macro-security considerations that a formula-driven chart avoids.

Frequently Asked Questions

Can I use Excel conditional formatting directly on scatter-plot markers?

No. Worksheet conditional-formatting rules change cell formatting, not chart marker formatting. To get dynamic marker colors, divide points into helper series with formulas and format each series separately.

Will the colors update when I change a value?

Yes, if the helper formulas recalculate and the chart includes their ranges. A point that crosses the threshold will stop appearing in one series and appear in the other, so it takes on that series’ marker color.

Can I color points using a text category?

Yes. Make a separate helper X/Y pair for each category and have its formulas return coordinates only when the row’s category matches. Add each pair to the chart as a named series and assign its marker style.

Why use NA() instead of zero for points outside a group?

A zero is a real numeric coordinate and would plot at the chart origin or at zero on one axis. NA() produces #N/A, which Excel does not plot as a point, so excluded rows do not create misleading markers.

Can I use this method for a bubble chart?

The same idea can be adapted by creating a separate series for each group, but a bubble series also needs a third range for bubble size. Make sure the X, Y, and size ranges all correspond to the same source rows, and verify the chart’s size scaling after adding the series.

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.