We can individually change the colors of the data points (the colors of the dots) in the scatter chart/plot in Google Sheets. But for conditional coloring the data points in the scatter plot, there is no built-in option in Google Sheets.

Here in this post, to solve the above-mentioned chart issue, you can find two workarounds based on re-formatting the chart source data.

The first workaround is using the scatter plot itself and the second workaround is using the bubble chart.

Even though the second workaround is simple to implement and the plotted chart will look similar to a scatter plot, it has a drawback.

What’s that?

The bubble chart won’t allow you to draw a trendline on the chart.

Though the first workaround supports the trendline, it will be based on the grouping of data points.

Didn’t get?

For conditional coloring scatter plot in Google Sheets, we must group the series and then move the series to different columns based on the group.

This put a restriction that we can only draw trendlines based on groups not based on the entire series on the Y-axis.

You will get more details of how to conditionally color scatter plot from the two examples below.

How to Apply Conditional Coloring to Scatter Plot in Google Sheets

We normally use a scatter plot to show how much one variable is affected by another.

The scatter plots show numeric coordinates along the “X” and “Y” axes.

Here in the below example (please see the image below), the numeric coordinates along the X-axis are the “Hours” and the numeric coordinates along the Y-axis are the “Wind Km/h”.

Here are the step by step instructions to apply conditional coloring to scatter plot in Google Sheets.

Grouping Data Points in Scatter Plot

First, I am going to group the data points “Wind Km/h” based on the below conditions.

B2:B19<=10 – “Group 1”
B2:B19>10 and B2:B19<15 – “Group 2”
B2:B19>=15 – “Group 3”

Using the below formula in cell C1, we can group the scatter plot data points accordingly.

={
     "Group";
     ArrayFormula(
        if(B2:B19<=10,"Group 1",if(B2:B19<15,"Group 2","Group 3"))
     )
}

If you have fewer data points, instead of using my formula or similar formula as per your required grouping, you can manually type the groups in column C too.

Moving Data Points to Columns

Let’s make another set of data to plot the scatter plot. First, in cell D1, enter the below simple formula to copy the X-axis values as it is.

={A1:A19}

Then in cell E1, type “Group 1”, in cell F1, type “Group 2”, and in cell G1, type “Group 3”. Key the following formula in cell E2 and drag it to F2 and G2.

=ArrayFormula(
     if($C$2:$C$19=E$1,$B$2:$B$19,)
)

IF Syntax for Reference: IF(logical_expression, value_if_true, value_if_false)

Even if your grouping formula, especially the logical_expressions in IF, in cell C1 is different, if you use the string “Group 1”, “Group 2”, (value_if_true), and “Group 3” (value_if_false), the above formulas in the cells E2, F2, and G2 will be the same.

If you have more than three groups in your grouping formula, for example, four groups, then in cell H1 type “Group 4” and copy the G2 formula to H2.

Scatter Plot with Conditional Colors

Time to plot the scatter plot.

1. Select D1:G19.
2. Click Insert > Chart.
3. Under the “Chart type” drop-down select “Scatter chart” and voila.

This way we can add conditional coloring to data points in the scatter chart in Google Sheets.

As mentioned at the beginning, in the above chart, you can draw trendlines for individual groups, not a single trendline for the whole set of values on the Y-axis.

It’s a draw-back for those who wish to draw trendlines in scatter charts.

Workaround Using Bubble Chart

Instead of going for the above steps to get conditional coloring in scatter plot, we can use the bubble chart in a much simpler way.

Here also we must first group the data points using a formula or manually as earlier. Let me explain the steps.

Here also I am going to use our sample data in A1:B19 but the columns will be moved.

Keep column A blank by moving the Y-axis values to column C and then the X-axis values to column B.

Then in cell A1 insert the below grouping formula.

={
     "Group";
     ArrayFormula(
        if(C2:C19<=10,"Group 1",if(C2:C19<15,"Group 2","Group 3"))
     )
}

This formula is from our first example (from cell C1) but changed column references as we have moved the columns from A:B to B:C.

The purpose of this formula is to group the data points similar to our earlier example.

In cell D2, insert the below formula.

=ArrayFormula(
     {"Size";if(len(A2:A19),1,);2}
)

The purpose of this formula is to just insert the values 1 in the range D2:D19 and 2 in cell D20.

This will help us to reduce the bubble size as it’s a workaround to apply conditional coloring to scatter plot. So we want the bubbles to look similar to the data points in the scatter plot.

Select the range A1:D20 to plot the bubble chart and click Insert > Chart.

In the chart editor panel, you should follow the below bubble chart settings under the “Setup” tab.

This is a workaround (using bubble chart) to add conditional coloring to scatter plot in Google Sheets.

If you find any issue in plotting the charts (scatter or bubble) as above due to the chart settings under the chart editor panel or the formulas, feel free to copy my sheet below.

Sample_Sheet_31020

That’s all. Thanks for the stay. Enjoy!

Resources

• Google Sheets – Add Labels to Data Points in the Scatter Chart.
• Common Errors That You May Face in Scatter Chart in Google Sheets.