Google Sheets Cheat Sheet

How To Make A Scatter Plot In Google Sheets

Highlight your columns of data and go to InsertChart making sure that in the Chart editor sidebar that appears when your chart is created, the Chart type is Scatter chart.

To add a trendline select the Customize tab in the Chart editor sidebar, go to the Series section, and scroll down to check the Trendline checkbox.

For more detail, read on...

Scatter plots are great charts to visualize trends and clusters in large data sets:

example of a scatter plot chart in google sheets with a trendline and circled clusters that deviate from the trend

In a single scatter plot you can see:

  • Every data point (quickly identifying the min and max)
  • A trendline that indicates positive correlation
  • Clusters that deviate from the trend

Click what you'd like to learn, how to:

How To Make A Scatter Plot In Google Sheets

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

You'll need at least two columns of data. For this example the columns are temperature and ice cream sales.

The first row of these columns can be a label that will be automatically added to the scatter plot. This is optional.

The first column should be the data you want to appear on the horizontal x-axis.

The second column will be data plotted on the vertical y-axis.

If your data has an independent (controlled) variable and a dependent (measured) variable the independent goes on the x-axis with the dependent on the y-axis.

When you have your data, highlight both columns completely by clicking and dragging on the column labels:

how to select columns of data for a scatter plot by clicking and dragging on column labels

Then either:

  • Click on the Insert chart icon in the toolbar, or
  • In the main menu go to InsertChart
shows the location of the insert menu and chart option within and the chart icon located in the main toolbar

A chart will be created using the type Google Sheets thinks is best for the data you've selected and the Chart editor will appear on the right of the screen.

When the data is suitable for a scatter plot, Google Sheets usually gets things right:

shows a successfully inserted scatter plot chart with chart editor sidebar

However, sometimes Google Sheets guesses the wrong chart type and you need to change it yourself.

In the Chart editor sidebar that appears when your chart is created, choose Scatter chart from the Chart type dropdown:

how to change the chart type to scatter plot using the chart editor sidebar
hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

You'll get updates from me with an easy-to-find "unsubscribe" link.

How To Add A Trendline To A Scatter Plot In Google Sheets

If the Chart editor sidebar isn't visible:

  1. Click on the chart
  2. Click on the three dots in the top right corner of the chart, and
  3. Select Edit chart from the dropdown
how to access the chart editor sidebar by editing a chart

In the Chart editor sidebar:

  1. Select the Customize tab
  2. Click on the Series section
  3. Scroll down and check the Trendline checkbox
  4. [Optional] Edit the options for type, color, opacity, thickness, and label
shows how to add a trendline to a scatter plot by opening the chart editor sidebar, going to customize, series, and checking the trendline checkbox

Now your scatter plot has a trendline:

shows a scatter plot with a trendline successfully added to it

How Do You Find The Slope Of A Scatter Plot In Google Sheets

A linear trendline has the following equation: y = ax + b where:

  • a is the slope of the line
  • b is the line's intercept with the y-axis

There are two easy ways to find the slope (a in the above equation):

  1. Using the chart's trendline
  2. Using the SLOPE function

The Trendline

If you've added a trendline to your scatter plot, in the 'Label' options you can choose Use Equation:

shows where to find the use equation label option in the chart editor sidebar

A legend will appear on your chart:

shows where the equation legend appears in a chart

It shows the trendline's equation is 16.4*x + -674.

Immediately you can see the slope, a = 16.4

This is the slope rounded to one decimal place.

You can use this equation to predict how much ice cream will be sold at a given temperature both within the data set (interpolation) and outside the data (extrapolation).

For example, if it's 70°F (for which there is no data point) you can say:

Sales = 16.4 * Temperature + -674 = 16.4 * 70 + -674 = $474

You would expect sales to be around $474 when it's 70°F (based on the other data).

What about when it's 90°F (which is outside of the data set)?

Sales = 16.4 * Temperature + -674 = 16.4 * 90 + -674 = $802

You can extrapolate out and expect sales to be around $802 when it's 90°F.

The SLOPE Function

Google Sheets has a built-in function that allows you to calculate the slope of scatter plot data:

=SLOPE(data_y, data_x)
  • data_y = The dependent data (second column)
  • data_x = The independent data (first column)

Here's how that looks with the ice cream vs temperature data:

ABC
1Temperature (°F)Ice Cream SalesSlope
257.6$239=SLOPE(B2:B,A2:A)
361.5$34716.39148238
453.4$174
559.4$350
665.3$399
771.8$516
866.9$397
977.2$636
1074.1$543
1164.6$404
1272.7$420
1363.0$393

You get the same answer as using the chart's trendline but it's not rounded to one decimal place.

Scatter Plot With Two Sets Of Data In Google Sheets

Creating a scatter plot with more than one set of data is as simple as selecting more columns when making a scatter plot.

With soup sales added to the data above you simply select the extra column/s:

how to select data for a scatter plot with two data sets by clicking and dragging on the column labels

Then you can click on the Insert chart icon in the toolbar or, in the main menu, go to InsertChart.

If Google Sheets chooses the wrong chart type you can choose Scatter chart from the Chart type dropdown in the Chart editor sidebar.

Here's what multiple data sets looks like:

shows a successfully created scatter plot that shows two data sets

Scatter plot correlation is:

  • Positive when it moves from low to high (temperature and ice cream sales)
  • Negative when it moves from high to low (temperature and soup sales)
a short animation with shows when a trendline indicates negative and positive correlation based on its slope

Remember:

  1. There is a stronger correlation between the variables when the points are all closer to the trendline
  2. Correlation does not imply causation

How To Make A Scatter Plot In The Google Sheets iPhone & iPad App

STEP 1: Select the columns you want to include in your chart by tapping on the column label and dragging the selection handles.

STEP 2: Click on the plus sign (+) at the top of the screen and choose Chart from the menu that appears.

STEP 3: Change the default options using the Type, Legend, Titles, and Color menus.

If the default 'Type' isn't a Scatter chart, select the Type menu and scroll down until you find the Scatter chart icon under the 'Scatter' subheading.

STEP 4: Click the tick () in the top left to create your scatter plot.

shows the complete process of creating a scatter plot chart in the google sheets iphone and ipad app

More advanced options like trendlines are only available using the desktop version of Google Sheets.

How To Make A Scatter Plot In The Google Sheets Android App

STEP 1: Select the columns you want to include in your chart by tapping on the column label and dragging the selection handles.

STEP 2: Click on the plus sign (+) at the top of the screen and choose Chart from the menu that appears.

STEP 3: Change the default options using the Type, Legend, Titles, and Color menus.

If the default 'Type' isn't a Scatter chart, select the Type menu and scroll down until you find the Scatter chart icon under the 'Scatter' subheading.

STEP 4: Click the tick () in the top left to create your scatter plot.

shows the complete process of creating a scatter plot chart in the google sheets android app

More advanced options like trendlines are only available using the desktop version of Google Sheets.


hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

You'll get updates from me with an easy-to-find "unsubscribe" link.

🗙