Sometimes, a visualization helps to group data and find patterns. That’s why I love a scatter plot in Google Sheets. They’re useful for showing trends and relationships in data when other graphs aren’t up to the task. So how do you make a scatter plot? My guide below shows the step-by-step instructions.
How to Make a Scatter Plot in Google Sheets – Video Tutorial:
This Article Covers:
Why Use a Scatterplot?
Scatter plots are useful when data is a bit all over the place but still represents a trend. They are also very useful for conveying datapoint clusters, which both bar and line charts aren’t as good at.
You can also use trendlines in scatter plots to further demonstrate relationships between data points. Error bars are a useful tool for demonstrating which data points are outliers and visualize exactly how much of an outlier those points are.
For the purposes of this tutorial, we’ll be using a hypothetical list of 50 for sale retail building space against the asking price (a part of the data is shown below).
So the columns we’ll be working with are “Sq. Ft.” representing the square footage of the lot and “Price in $” representing the asking price.
Now let’s see how to make a scatter plot chart in Google Sheets using this data.
How to Make a Scatter Plot In Google Sheets
Google Sheets features scatter plots as a built-in preset graph you can apply to just about any data set. Creating a scatter plot requires little in the way of configuration.
If you’re creating a bar or line chart and the data looks a bit too “all over the place,” this is a good indication that a scatter plot is the graph for the job.
The following steps explain how to create a scatter plot in Google Sheets:
- Highlight the columns you want to compare by holding shift and left-clicking on the column names.
- Open the “Chart editor” by either clicking “Insert” and “Chart” (left) or by clicking the “Chart” icon from the top submenu (right).
- Click on “Chart type” and select “Scatter chart” from the “Chart editor” options menu to create the scatter chart.
Now you’ve created a scatter plot chart for your data.
This information can be useful as is, but Google Sheets has a few built-in features that can further enhance your analysis.
How to Add a Trendline to a Scatter Plot In Google Sheets
Adding a trendline to a Google Sheets scatter plot is a helpful way to demonstrate trends in the data. Fortunately, Sheets makes it easy to add this feature to your chart.
The following steps demonstrate how to add a trendline to a scatter plot in Google Sheets:
- Open the chart editor for your scatter plot by clicking on the three dots icon located at the top right of the graph and select “Edit chart” from the pop-up menu.
- Click the “Customize” tab, expand the “Series” section, and check the box next to “Trendline” to add the feature to your chart.
- Your scatter plot graph will now feature a trendline representing the overall relationship between X and Y coordinate values.
How to Use Error Bars in a Scatter Plot
Error bars are a useful tool on a scatter plot graph for demonstrating how reliable or on-trend a given data point is. In the case of cost for retail space in our example, this can help a prospective buyer avoid overpriced locations.
To add error bars to your scatter plot, follow these steps:
- Open the chart editor for your scatter plot by clicking on the three dots icon located at the top right of the graph and select “Edit chart” from the pop-up menu.
- Click the “Customize” tab, expand the “Series” section, and check the box next to “Error bars” to add the feature to your chart.
- Your scatter plot graph will now feature error bars for each data point representing how close they are to the trendline.
Interpreting Data with a Scatter Plot
Now that we have a scatter plot with trendlines and error bars, let’s look at some of the interesting things this chart tells us.
There are two notable clusters in the dataset: around 2,000 sq. ft. for $125,000 and around 2,500 sq. ft. also for around $125,000.
This information tells us there’s a little extra cost for moving from 2,000 sq. ft. to 2,500 sq. ft. and the properties in those clusters are competitively priced.
By comparison, bar and line charts don’t demonstrate these clusters as effectively.
Additionally, scatter plots do an excellent job of showing data clusters that are off-trend. In our example, the chart makes a case that several of the larger properties are overpriced via large error bars and being far above the trend line.
Frequently Asked Questions
Sometimes, I hear questions about scatterplots. I wanted to answer those, and some on similar subjects, here. If I missed anything, please let me know in the comments!
Can I make a scatterplot in Google Sheets?
Yes! It’s easy to make a scatterplot in Google Sheets. Just select your data, choose to add a chart, and select “scatter chart”. You can also customize it from there.
Can I make a stemplot in Google Sheets?
Yes, you can use any spreadsheet software to create a stem plot or a stem and leaf plot. To do this, you’ll manually select your data and enter it into the sheet. Then, customize the column sizes and remove grid lines to make it look like a stemplot.
Can I add a trend line to my scatterplot?
Google Sheets has an option to add a guide or trend line to your scatter plot. Just go to the chart settings and click the “Trendline” option.
Conclusion
By following the steps in this tutorial, you’ll be creating and enhancing scatter plot graphs in Google Sheets in no time.
However, it’s important to keep in mind that scatter plots won’t work for all datasets. If you see something like a flat trendline or massive error bars, it means the data doesn’t follow a trend.
I hope you found this Google Sheets charting tutorial useful!
Other Google Sheets tutorial you may like:
