Create a Scatter Plot in Google Sheet

  • Reading time:3 mins read
  • Post category:Charts
  • Post comments:0 Comments

Scatter Plot(also known as XY Charts) are useful to understand the raw spread of data points across a two-dimensional plane. It helps understand the data distribution and level of correlation with the between the two datasets mapped on X & Y Axes.

Lets try to understand the step by step process of creating & interpreting a Scatter Plot. We’ll also cover how trendlines can be used in this regard.

So let’s dive in.

Creating a Scatter Plot in Google Sheet

We will be following the below steps to create a scatter plot in Google Sheets:

Step 1: Get the data you want to create a Plot with. We have used a sample Height vs Weight data available on Kaggle.

Data Table in Google sheet

Step 2: Select the data. Click top left cell(B1) hold the cursor and drag down to bottom right cell C17.

Selecting a Data in Google Sheet

Step 3: Goto Menu Options -> Insert -> Charts. 99% of the time Google Sheets should automatically detect the type of data and create a scatter plot. But if it doesn’t follow step 4.

Creating a Scatter Plot in Google sheet

Step 4: Say you got a Column Chart instead of Scatter Plot. To change the chart type you’ll have to goto chart editor and from the Chart Type Dropdown choose Scatter Plot.

Changing Chart Types in Google Sheets

After, following the above 4 steps your plot will be ready.

Basic Scatter Plot in Google Sheets

Formatting the Scatter Plot in Google Sheets

While the plot we created in the last section would work just fine, if you need you can make further formatting enhancements to it. Like:-

  1. Modifying Axis and Char Titles
  2. Improving Fonts Size, Color etc
  3. Changing Color & Size & Shape of the bubble
  4. Editing Min and Maximum Thresholds of the X-Axis and Y-Axis
  5. Adding Trendlines or Error Bars

Most of the above are very easy to execute using the Chart Editor Options.

Below is a Clip of making some of these changes in action.

Formatting a Scatter Plot in Google Sheets

Adding Trendline to Scatter Plot in Google Sheet

Before we move on to interpretation of Scatter Plots, lets learn insert a trendline first.

Under Customize , goto Series -> Weight-> Tick the Trendline check-box.

Adding Trendline in Google Sheets

To also add the trendline equation goto label and select “Use equation”.

Interpreting the Scatter Plot and How to use Trendlines

So, now we know how to create a scatter plot. Let’s now understand how to read and interpret it.

Looking closely at the Triangles, we can see that as the height increases, so does the weigh on an average. This fact is also reflected in the trendline which we have added.

How to read a Scatter Plot in Google Sheets

It’s also clear that most data points are centered below the height 70. Post 70 we only have a two data points indicating low % of population with height more than 70.

Using the trendline equation obtained before, (y = 5.72*x -205), we can simply plugin any value of Height(in x) and get the associate Weight(as y).

Above example was fairly a simple one with lesser number of data points for easy understanding. In real life you can plugin much more points in the scatter plot to get even better insights, like below.

Complex Scatter Plot in Google Sheets

Free Resources

Before we leave, feel free to grab a copy of the templates discussed in this tutorial in your personal Google Drive.

  1. Google Sheet Template.

*Create a copy instead of requesting access

That’s it on this topic. If you still have any questions, then drop a comment below. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply