Excel Scatter Plot by Third Column: Color Points by Group

Yes—color an Excel scatter plot by a third column by making a separate chart series for each category in that column, then assigning each series a color. Excel does not automatically map a third column to marker colors in a standard XY scatter chart, so category-specific helper columns are the most dependable built-in method.

How color works in an Excel scatter plot

An XY scatter chart uses one column for horizontal values and another for vertical values. A third column can describe each observation—such as region, product type, or test group—but Excel does not treat that column as a color variable when it creates a basic scatter chart. If you select three columns and insert a scatter chart, the third column is not automatically used to color individual points by category.

The practical workaround is to divide the observations into separate series. Each series contains the X and Y values for one category and returns #N/A for rows belonging to other categories. Excel does not plot those error values as markers, so each series displays only its own group. You can then format each series with a distinct marker color and add a legend.

This approach is intended for categorical data: for example, three named regions. If the third column contains a continuous measure such as temperature or revenue, a standard scatter chart will not automatically create a continuous color gradient from it. You can instead group the values into ranges or format individual points manually.

Prepare the data and helper columns

Keep the original data in three columns, with headers in row 1: X values in A, Y values in B, and category labels in C. For example, rows 2–7 could contain these pairs and groups: (1, 4, North), (2, 5, South), (3, 3, North), (4, 7, South), (5, 6, North), and (6, 2, South). The labels need to be consistent; “North” and “north” or a label with an extra space may not match as expected.

Add two helper columns for every category. For this example, use D and E for North’s X and Y values, and F and G for South’s X and Y values. Put descriptive headers in row 1, such as North X, North Y, South X, and South Y.

With X values in A2:A7, Y values in B2:B7, and category labels in C2:C7, enter =IF($C2="North",$A2,NA()) in D2. It returns 1 for the first sample row because that row is North; for a South row it returns #N/A.
With the same source layout, enter =IF($C2="North",$B2,NA()) in E2. It returns 4 for the first sample row and #N/A for a South row.
With the same source layout, enter =IF($C2="South",$A2,NA()) in F2. It returns #N/A for the first sample row and 2 for the second row.
With the same source layout, enter =IF($C2="South",$B2,NA()) in G2. It returns #N/A for the first sample row and 5 for the second row.

Fill each formula down through row 7. The dollar signs keep the category column fixed while the row references adjust. For more categories, create another X/Y helper pair and change the quoted category label in the formulas. NA() is a real Excel function; using a blank string instead can lead to less predictable chart behavior, so the error-value method is a useful way to ensure unwanted points are not plotted.

Create the scatter chart from the helper data

In Excel for Microsoft 365 on Windows, insert a blank chart using Insert > Insert Scatter (X, Y) or Bubble Chart, then choose the scatter option with markers. Click the chart and select Chart Design > Select Data. In the Select Data Source dialog, choose Add to define the first series.

For the North series, enter North as the series name, set the series X values to D2:D7, and set the series Y values to E2:E7. Add another series named South with X values F2:F7 and Y values G2:G7. Select each range using the range-picker button in the dialog if it is easier than typing it. The worksheet name may appear in the full references, but the ranges must still point to the appropriate helper columns. Click OK to finish.

A scatter chart series requires paired numeric X and Y ranges of equal length. Do not use the category column as an axis range, and do not include the header row in the values. If the chart appears empty or has an unexpected axis, return to Select Data and check both ranges for each series.

Assign a different color to each group

Select a marker belonging to one group, then open its formatting controls. To select the whole series, click one marker once; if necessary, click again carefully to select an individual point instead. Right-click the selected series and choose Format Data Series. In the formatting pane, open Fill & Line > Marker > Fill and choose a solid fill color. Set the North series to one color, then repeat for South using a contrasting color.

If the chart includes lines connecting observations, open the series’ Line settings and choose No line when the data represents separate observations rather than a path. A scatter chart can show markers, lines, or both; the appropriate choice depends on what the points mean. Keep the legend visible so readers can identify the categories. Give the chart a meaningful title and label the axes with units where relevant.

On Excel for Mac, the same general approach applies: create the scatter chart, use the chart’s Select Data controls to add or edit series, and format a selected series through the formatting pane. Ribbon wording and pane layout can vary slightly by release. Excel for the web can handle basic chart work, but if a chart-formatting control is missing or awkward to use, open the workbook in desktop Excel.

When the third column contains numbers

A numeric third column can represent either categories or a continuous scale. If the values have meaningful bands, make a helper label column first—for example, Low, Medium, and High—and create one series for each label using the method above. Decide the thresholds based on the meaning of the data, not simply on what makes the chart look balanced.

With X values in A2:A7, Y values in B2:B7, and a numeric measure in C2:C7, enter =IF($C2<10,"Low",IF($C2<20,"Medium","High")) in D2 to assign values below 10 to Low, values from 10 up to but not including 20 to Medium, and values of 20 or more to High. For example, a C value of 14 returns Medium. Fill the formula down, then use column D as the category column for the helper-series setup. Adjust the cutoffs and labels to suit the data; this formula is only an example.

If each numeric value should have its own shade on a continuous gradient, the built-in scatter chart does not provide a direct third-variable color scale. Possible alternatives include grouping values into a few defensible bands, manually formatting points, or using a visualization tool that supports color as a continuous encoding. A bubble chart is not an automatic solution: bubble size represents an additional value, but it does not make a scatter chart’s marker color follow a third column.

Choosing between separate series and manual colors

Use separate series when the data has a modest number of meaningful categories and the chart should remain understandable as the data changes. Each series gets a legend entry and can be formatted consistently. This is easier to maintain than coloring many points one by one, although each added category requires another pair of helper columns and another series.

Manual formatting can be reasonable when there are only a few points, or when colors are intentional annotations rather than a recurring category rule. To change one point, select the series and then select the individual marker, open Format Data Point, and change its marker fill. The drawback is that manual colors are not driven by the source value. If the data is sorted, replaced, or expanded, colors may no longer communicate the intended groups.

Choose separate series for repeatable category colors, a usable legend, and easier updates.
Choose manual point formatting for a small, mostly fixed chart where only particular observations need emphasis.
Choose category bands for a continuous measure when a few labeled ranges are meaningful and easy to explain.

Common problems and how to fix them

If points disappear, inspect the helper formulas and category spelling. A formula will return #N/A whenever the label in column C does not exactly match the label in the formula. Check for trailing spaces, inconsistent capitalization, or a different spelling. Also confirm that the formulas were filled through every data row.

If all points appear in one color, the chart may contain one series rather than one series per category. Open Chart Design > Select Data and verify that the series list has a separate entry for each group. Conversely, if the legend shows several groups but the colors look alike, format each series explicitly and select colors with enough contrast.

If the chart uses category labels as numbers or displays the wrong shape, check the X and Y assignments. The X range must refer to the helper X column and the Y range to its matching helper Y column. Make sure both ranges cover the same rows and contain numeric values. Text-formatted numbers can also cause plotting problems; convert them to numeric values before charting.

When new observations are added, copy the helper formulas into the new rows and confirm the series ranges include them. If you use an Excel table, formulas can fill down automatically, but chart series references may still need checking as the table grows. More categories also mean more colors and a busier legend; combine groups only when that makes sense for the analysis, not just to simplify formatting.

Frequently Asked Questions

Can Excel color scatter points automatically from a third column?
No, not as a direct color-variable setting in a standard XY scatter chart. Split the points into one series per category, or format individual points manually.

Can I use a text column as the third variable?
Yes. Use each distinct text label as a category and create matching helper X/Y columns for each label. Check that the labels are consistent so the formulas match them.

Why does Excel show #N/A in my helper columns?
That is intentional for rows that do not belong to a particular series. The chart uses the numeric values for matching rows and omits the nonmatching points. If a point that should appear is missing, check its category label and formula references.

Can I make the marker color a gradient based on a number?
Not directly with the standard Excel scatter chart’s built-in series formatting. You can use bands to create a few color-coded series or set colors point by point, but neither is an automatic continuous color scale.

Will the colors update when I change a category?
With helper formulas, the point moves to the series whose category condition it matches, so it takes that series’ color. Manually formatted point colors are not linked to the category value and may need to be reapplied.

How many categories should I use?
There is no fixed limit, but a chart becomes harder to read as the legend and palette grow. Use the number of groups that the chart can distinguish clearly and that the data’s purpose supports.

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.