Excel Scatter Plot Colors by Group: A Step-by-Step Guide

To color an Excel scatter plot by group, put each group in its own data series, then assign a marker color to each series. Excel does not automatically color points in one scatter series according to a separate category column; helper columns make the groups easy to plot and keep the chart updateable.

What “color by group” means in Excel

A scatter plot displays paired numeric values: one value determines each point’s horizontal position and another determines its vertical position. A category such as “North” or “South” is not a third coordinate. Excel can distinguish categories visually by drawing them as separate series, with a different marker color for each one.

For example, suppose a worksheet has customer advertising spend in column A, sales in column B, and region in column C. You want one point per customer, with North customers in blue and South customers in orange. The X and Y values remain the spend and sales; region determines which series receives each point.

Keeping groups as separate series also gives the chart a useful legend. Each legend entry represents a category, and selecting that entry can highlight its points. This is different from manually coloring individual markers: manual formatting can distinguish points, but it does not create a reliable category-based series or legend.

Prepare the source data and helper columns

Start with a simple source table: one row per observation, numeric X and Y values, and a group label. For the example, use A1:C1 as headings—Spend, Sales, and Region—and put the records in rows 2 through 100. Make sure values in the group column are consistent. “North,” “north,” and “North ” (with an extra space) may be treated as different labels.

Next, create a pair of helper columns for each group. In this layout, F1 contains North, G1 is headed North X, and H1 is headed North Y. Put South in I1, with South X and South Y in J1 and K1. The group labels in F1 and I1 act as criteria; the X and Y columns hold only the values for their matching group.

With group names in C2:C100, X values in A2:A100, and the North label in F1, enter this in G2 to return North’s X values: =IF($C2=F$1,$A2,NA()).
With the same source layout and North in F1, enter this in H2 to return North’s Y values: =IF($C2=F$1,$B2,NA()).
With South in I1 and the same source columns, enter =IF($C2=I$1,$A2,NA()) in J2 for South X values and =IF($C2=I$1,$B2,NA()) in K2 for South Y values.

Fill each formula down through row 100. A matching row returns its numeric coordinate; a row in another group returns #N/A. Excel charts omit #N/A values, so those rows do not appear in the wrong series. The error is intentional in the helper area; if seeing it on the worksheet is distracting, place the helper columns on a separate sheet.

Create the scatter chart with separate series

In Excel for Microsoft 365 on Windows, select the relevant X and Y helper columns and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Because the helper pairs are separated by other columns, adding series individually through the chart’s data controls is often clearer and less error-prone:

Choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter and select a scatter chart with markers.
Select the chart, then choose Chart Design > Select Data.
In the Select Data Source dialog, choose Add. Set Series name to the relevant group label cell, such as F1 for North.
Set Series X values to the North X helper range, such as G2:G100, and Series Y values to H2:H100. Exclude the header row from both value ranges.
Repeat Add for South, using I1 as the name, J2:J100 for X, and K2:K100 for Y. Confirm the dialogs.

Check that each series has the intended X range and Y range. A common setup error is to select a group label or heading as part of a coordinate range, or to reverse the X and Y columns. You can then add titles through Chart Design > Add Chart Element > Chart Title or Axis Titles. Label axes with units, such as “Spend ($)” and “Sales ($),” so readers can interpret the positions.

On Excel for Mac, the chart types and basic series approach are similar, though ribbon placement and dialog appearance can vary by release. Excel for the web has chart creation and formatting features, but the desktop version generally offers more complete controls for managing series. If a web interface does not expose the series ranges you need, open the workbook in desktop Excel.

Choose colors and marker styles that communicate well

After creating the series, select a marker belonging to one group and open Format Data Series. In the formatting pane, use Fill & Line to set the marker fill and border. Repeat for each series. Exact pane labels can vary slightly with chart type and Excel release, but format the series rather than changing the chart’s overall color theme if you need precise group colors.

Choose colors that remain easy to distinguish when markers overlap. A restrained palette—such as blue, orange, green, and purple—is usually more readable than several similar shades. Do not rely on color alone if the chart may be printed in grayscale or viewed by people with color-vision differences. Different marker shapes, clear legend labels, and direct annotations can provide additional cues.

Use marker size large enough to see, but not so large that dense areas become solid blocks.
Set a contrasting marker border when points sit on a similarly colored background.
Keep the same group-to-color mapping across related charts so readers do not have to relearn it.
Use concise, meaningful group names in the legend rather than generic labels such as Series 1.

For a busy chart, partially transparent markers can make overlapping observations easier to notice, if the formatting options in your Excel version provide transparency. Transparency is not a substitute for sensible marker size or a manageable number of groups. If there are dozens of categories, a single scatter chart may become difficult to read even when every category has a unique color.

Keep the chart current when data changes

If you add records below row 100, the chart and helper formulas will not necessarily include them when the ranges were set to end at row 100. One practical option is to convert the source data into an Excel table with Insert > Table, then use table-aware formulas or extend the helper formulas and series ranges as new records are added. Check the chart after expanding the data; a formula-filled helper column does not automatically guarantee that the chart’s plotted range has expanded.

For a small, stable dataset, fixed ranges are simple and reliable. For a recurring report, keep the source and helper areas organized, label every series, and include enough rows for expected additions. If categories are added later, create a new X/Y helper pair, fill the formulas down, and add that pair as another chart series. If a group label changes, update the matching criterion and series name so the chart remains consistent.

You can also filter the source table to inspect records, but filtering alone is not a dependable way to assign a permanent color to every category in one series. A filter changes which rows are visible; it does not turn category values into distinct chart series. For a chart intended to show all groups together, separate series are the clearer structure.

Alternatives: manual point colors and other chart types

For a one-off chart with only a few observations, you can format points individually. Select a series once, then select the particular marker again to target that data point; use the Format Data Point pane to change its marker fill. This is useful for calling attention to an outlier or a single important observation, but it takes maintenance: if the data changes or points are reordered, the formatting may no longer identify the intended category. Individual point colors also do not automatically produce a category legend.

If the group is actually a continuous measurement—such as temperature or time—consider whether a color scale or a different visualization is more appropriate. A standard Excel scatter series assigns one marker style to the whole series, not a graduated color to every point based on a third numeric field. Separate categories are a good fit for a limited set of discrete groups; a very large number of groups can be clearer in small multiples or in separate charts.

A line chart is not automatically a better choice just because the points have an order in the worksheet. Use a scatter chart when the horizontal values are numeric and their spacing matters, such as elapsed time or concentration. Use a line chart when the horizontal axis represents ordered categories and equal spacing between those categories is appropriate. Choosing the chart type based on the meaning of the X values prevents a misleading display.

Common problems and how to fix them

Every point has the same color: The chart may contain only one series. Add one series per group and verify each series’ X and Y ranges.
A group is missing: Check the helper formulas, group spelling, and chart ranges. Confirm that the group’s X and Y helper columns both cover the intended rows.
Points appear in the wrong place: Confirm that the X range contains the intended horizontal values and the Y range contains the vertical values. Also check for text-formatted numbers in the source.
The chart shows no points for a group: Verify that the group label in the criterion cell exactly matches the source labels. A formula returning #N/A for every row means none matched.
New observations do not appear: Extend the helper formulas and the series ranges, or redesign the ranges to include future records.
Legend labels are unhelpful: Edit each series name in Select Data to refer to the group label cell or enter a clear name.

One important misconception is that changing a series’ color will make Excel recognize the category column. It will not: formatting changes appearance, while the series ranges determine which points belong together. If categories must be consistently colored, establish the series structure first and format those series afterward.

Frequently Asked Questions

Can Excel color scatter plot points by category automatically?
Not simply by assigning a category column to a single scatter series. Make a separate series for each category, commonly with helper columns that return the group’s X and Y values and return NA() for other rows.

Why does my scatter plot show #N/A in the worksheet?
The helper formulas use NA() to mark rows that do not belong to a particular group. Those cells may display #N/A, while Excel omits the corresponding values from the chart. Keep the helper area separate if the worksheet display is distracting.

Can I color each point differently without helper columns?
Yes. You can format an individual data point by selecting it and changing its marker formatting. This is convenient for a few highlights, but it is manual and does not create a durable category-based legend.

How do I add another group to an existing scatter chart?
Add an X/Y helper pair for the new group, fill its formulas down, and use Chart Design > Select Data > Add to create a series with those ranges. Give the series a clear name and choose its marker color.

Can I use different shapes as well as colors?
Yes. Format each series’ marker options and choose a different marker shape where appropriate. Shape differences help distinguish groups when colors are hard to see or when the chart is printed without color.

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.