HomeGoogle DocsSpreadsheetConditional Coloring Data Points in the Scatter Plot in Google Sheets

Conditional Coloring Data Points in the Scatter Plot in Google Sheets

Published on

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"))
     )
}
Grouping Data Points in Scatter Chart

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,)
)
Formatting Data to Add Conditional Coloring to Scatter Plot in Google Sheets

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.

Scatter Plot with Conditional Colors

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.

Bubble Chart Workaround to Add Conditional Coloring to 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.

Chart typeBubble
Data rangeA1:D20
ID
X-axisHours
Y-axisWind Km/h
SeriesGroup
SizeSize
Use row 1 as headers

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

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

1 COMMENT

  1. Great article. One workaround to get a trendline for the whole dataset is to add all the data as another series. Then add a trendline only for this “all data” series. Then set the point size to 0. The result is the extra data points are invisible, but a single trendline is added.

LEAVE A REPLY

Please enter your comment!
Please enter your name here