How to Make a Scatter Plot in Google Sheets – Video tutorial:
Visualization tools like charts and graphs are really helpful in giving better insights into your data.
A scatter plot is one such visualization tool that helps you make different types of inferences about your data distribution.
In this tutorial, we will learn how to make a scatter plot in Google Sheets.
Table of Contents
What is a Scatter Plot?
A scatter plot (also called a scatter chart) is a visualization tool that plots data points along a horizontal and vertical axis.
This helps you deduce at a glance a number of different things:
- You can actually see how the data points are distributed.
- You can understand how the data variables relate to one another.
- You can identify trends in the data distribution.
- You can identify how individual data points correlate with respect to the trends in the data.
For example, suppose you have the height and weight data for 20 people.
You can plot this data on a scatter plot in Google Sheets and see how these two are correlated.
The utility of a scatter plot will become more apparent when you see how it’s made.
How to Make a Scatter Plot In Google Sheets
Google Sheets makes it really easy to create attractive and intuitive scatter plots with just a few clicks.
To understand how to make a Scatter plot in Google sheets, we are going to use the height and weight data shown in the image below:
This dataset contains data on the height vs. weight of randomly selected men. We want to create a Scatter plot to understand how the two variables are related to one another.
To make the histogram for the above data, follow these steps:
- Select the data you want to visualize in your scatter plot. You can also include the cells containing the column titles. In our example, let’s select the cell range A1:B22.
- Click the Insert menu from the menu bar.
- Select the Chart option.
- This will display a chart on the worksheet and a Chart editor sidebar on the right side of the window.
- Google usually tries to understand your selected data and displays the chart it thinks is the best representation for it. Ideally, it should display a Scatter Chart. However, if you see some other kind of chart, go to step 6.
- To convert it to a Scatter Chart, select the Setup tab from the Chart editor sidebar and click on the dropdown menu under “Chart type”.
- From the chart options that you see, select the “Scatter chart”. It should be visible under either the “Suggested” or the “Other” category.
- You should now see a Scatter Plot on your worksheet.
You can now see how the height-weight data points are distributed on a 2-dimensional space.
But just looking at points doesn’t really give much insight. So, you can look for patterns in your data by adding a trend line across the Scatter chart.
This will serve three purposes:
- You can see if there really is a strong correlation between height and weight. The closer the bulk of data points are to the straight line, the stronger the correlation.
- You can see trends in the data (if any), whether there is an upward or downward trend in weights as the heights increase.
- You can find out which data points are too far away from the trendline and identify them as outliers.
There are plenty of other inferences that you can make from the scatter chart once you have a trend line calculated and displayed. Google Sheets performs all the background calculations to give you the optimal trend line.
Adding a trend line
To add a trend line to your scatter chart, you will need to use the Chart Editor.
When you create a chart, the Chart Editor is usually available as a side toolbar on Google Sheets. However, sometimes the Chart editor goes away after your chart has been created.
To make it appear again, do the following:
- Click on the graph.
- You should see an ellipsis (or hamburger icon) on the top right corner of the box containing the graph.
- Click on the ellipsis and select “Edit the chart” from the drop-down menu.
- This will make your Chart editor sidebar appear again.
The Chart editor has a ‘Customize’ tab that lets you enter all the customization settings for your chart.
We are going to use this Customize tab to add a trend line to the scatter chart. Follow these steps:
- Click on the Customize tab of the Chart Editor.
- Select the Series drop-down menu.
- Scroll down and you should see three checkboxes – Error Bars, Data Labels, and ‘Trend line’.
- Check the check box next to the ‘Trend line’.
You should now see a trend line displayed on your scatter chart.
Other Trend Line Options
Once you check the Trend line checkbox, you will notice a new set of related options under it. These options let you customize the trend line according to your requirement.
For example:
- You have the option to select the color that you want for your trend line. In our example, we set the trend line to red for contrast.
- You also have the option to set the thickness and opacity of the trend line.
- You can set the type of trend line you want. You have the option to insert a linear, exponential, polynomial, and moving average trend line among others.
- For each type of trend line that you choose, you may get a different set of other options too.
- Finally, the Label option is interesting. You can either set it to ‘none’ (in which case there will be no label), ‘custom’ (in which case you will get a customizable legend for the trend line or ‘Use equation’ (in which case you can actually display the equation for the trend line).
Using the trend line equation, you can easily extrapolate and find y-values for any selected point on the trend line, given an x-value (or vice-versa).
Customizing the Scatter Chart in Google Sheets
Besides adding a trend line, there are plenty of other customization options available for you to apply to your scatter chart. Here are some of the things that you can do:
Change the Chart Style
The Chart style category in the Chart editor lets you change the background color, border color, font style, and size of your chart.
Chart and Axis Titles
This category lets you provide the text and formatting for the chart title and subtitles and axis titles.
Series
The Series category lets you choose the colors for the points in the Scatter plot. This becomes even more helpful when you want to compare different variable distributions in one scatter chart.
For example, you can have different colored scatter points for weights of men and women non one chart, as shown below:
You could use different colors for different variables.
You can also check the Error bars option to see how reliable or ‘on-trend’ each data point is.
Legend
The Legend category, as its name suggests, lets you provide settings and formatting for the scatter chart legend.
Horizontal and Vertical Axes
You can use this category to:
- Change the text color of the entire chart
- Change the font for the horizontal and/or vertical axis.
- Set the font size for the x and/or y-axis values.
- Make the x and/or y-axis values bold and/or italicized.
- Display the x or y-axis as labels, rather than numeric values.
Gridlines and Ticks
This category lets you format the scatter chart to contain major and/or minor gridlines.
You can also set what colors you want the gridlines and ticks to be, or choose to not have them at all.
In this tutorial, we demonstrated how to make a scatter plot in Google Sheets, and how to add a trendline in the scatter chart to draw useful inferences from your data.
I hope you enjoy creating and enhancing your scatter charts on Google Sheets and have fun exploring your data.
Other Google Sheet tutorials you may like:
