How to Make a Scatter Plot in Google Sheets

When analyzing data, a scatter plot is one of the easiest ways to discover the relationship between two variables. And the best part? It can be done in Google Sheets.

In this guide, we are going to explain how to create a scatter plot in Google Sheets.

Why a Scatter Plot?

A scatter plot is useful during data analysis because:

1. It helps to define the trend in data.
2. You can actually see the range of data, that is, the maximum and minimum values recorded.
3. It helps to reveal both linear and nonlinear relationships between variables.
4. Its interpretation is straightforward.

How to Make a Scatter Plot in Google Sheets

Creating a scatter plot in Google Sheets is quite straightforward.

1. Highlight the data you want to plot in a chart. To do so, click on the first cell and then drag the mouse over all the other cells you intend to graph.
   
2. In the menu at the top of your spreadsheet, select “Insert.”
   
3. In the resulting dropdown submenu, click on “Chart.” This will launch a chart on the spreadsheet, usually on the right-hand side of your data. Accompanying the chart is a chart editor sidebar.
   
4. By default, Google is programmed to display the chart it deems best for the data. In most cases, it will display a scatter plot. But if it doesn’t, proceed to step five.
   
5. From the chart editor sidebar, select “Setup.”
   
6. Click on “Chart type.” A dropdown menu will appear.
   
7. To convert the chart to a scatter plot, scroll down the dropdown menu and select “Scatter plot.” It may appear under “Suggested” or “Other,” again depending on Google’s default analysis of the data.
   
8. At this point, there should be a scatter plot for the data.
   

How to Customize a Scatter Plot on Google Sheets

The good thing with scatter plots in Google Sheets is that you can customize just about any aspect of a graph. That includes:

1. Changing the background color;
   
2. Changing the text or adding chart title, horizontal axis and vertical axis title;
   
3. Changing the color of dots on the scatter plot, for instance from blue to red; or
   
4. Adding gridlines and sticks.
   

To do any of these things, simply select “Customize” from the chart editor sidebar.

How to Make a Scatter Plot in Google Sheets with Line of Best Fit

Scatter plots generally offer many advantages, but they may not give much insight, especially when analyzing a large set of data. To find out if there’s a discernible pattern in a given set of data, you might want to add a line of best fit.

A line of best fit, also called a trend line, is a line that runs through a scatter plot in an attempt to show the general direction your data appears to follow. In other words, it attempts to plot data points that best express the relationship between the variables used in your analysis.

A line of best fit is useful in three ways:

1. It helps determine if variables show evidence of a strong correlation (co-movement). If the variables are strongly correlated, the bulk of data points will be very close to the line of best fit.
2. It reveals the trend in the data. It can easily show whether there’s an upward trend or a downward trend.
3. It reveals data points that are too far away from the line of best fit.
   

Once you come up with a scatter plot in Google Sheets, a line of best fit can be added in a few simple steps:

1. In the chart editor sidebar, click on “Customize.”
   
2. From the resulting dropdown menu, click on “Series.”
   
3. Scroll down and check the box next to “Trend line.”
   

Sometimes, the chart editor sidebar may disappear once a scatter plot is created. To relaunch it:

1. Click on the scatter plot. You should see three small dots in the top right corner of your scatter plot. These dots constitute the ellipsis of the graph.
   
2. Click on the ellipsis.
   
3. Select “Edit chart.”
   

How to Make a Scatter Plot Graph in Google Sheets

If a bar or line chart makes your data look a bit cluttered, a scatter plot graph could be the ideal solution. Here’s how you can make one:

1. Highlight the first column of data, hold the shift key, and then proceed to highlight the other columns you want to plot by left-clicking on their names.
   
2. Click on the chart icon in the menu at the top of the worksheet. In Google Sheets, the chart icon appears as a small square with three vertical bars.
   
3. By default, Google Sheets will open a graph that best fits the data, and in most cases, it will be a scatter plot graph. If some other kind of chart opens, proceed to step 4.
4. From the chart editor sidebar, select “Setup.”
   
5. Under “Chart type”, click on the dropdown menu to select column chart type.
   
6. To convert the chart to a scatter plot graph, scroll down the dropdown menu and select “Scatter.”
   

Additional FAQs

How Do You Make an Average Graph on Google Sheets?

In addition to a scatter plot and line of best fit, sometimes you may want to add an average line to a graph. This can help single out data points that are above or below the average. Here’s how you can do this:

• Insert your data in the Google Sheet.

• Create a new column and name it “Average.”

• Enter the following formula in the first cell under the “Average” column:

a) =average(B1:B10)

b) B1 and B10 in this case represent the cells containing the first and last data points, respectively.

• Hit “Enter.” At this point, Google Sheets will automatically generate the average of the data contained in the cells specified.

• Click on the first cell under the “Average” column.

• With the cursor positioned at the bottom right corner of the first cell, drag your mouse over the other cells within the specified range. This will auto-repeat the average value in each of these cells.

• Click on the chart icon in the menu at the top of your worksheet. As before, Google Sheets will open a chart that best fits your data. In it, there will be a line graph indicating the average value of your data. You can change the chart type by opening the chart editor sidebar.

How Do You Plot a Scatter Plot in Excel?

To plot a scatter plot in Excel:

• Open the worksheet containing the data.

• Highlight the data. To do this, click on the first cell containing the data you want to plot in the scatter, and then drag the mouse over all the other cells.

• Click on “Insert,” and then select “Scatter.”

What Is a Scatter Chart in Excel?

A scatter chart, also known as a scatter graph, shows the relationship between two variables in a two-dimensional space. It helps to show how a data set from two variables is distributed. For example, it can help you to determine whether there’s a relationship between the number of times clients visit your website and the number of sales made on a given day.

Similarly, it can help to determine whether there’s a relationship between body height and weight for a group of people.

How Do You Make an XY Graph in Google Sheets?

• Highlight the data set you want to plot in the graph

• Select “Insert” in the menu at the top of the worksheet. Alternately, you may click on the “Chart” icon in the toolbar.

How Do You Make a Scatter Plot with Multiple Data Sets in Google Sheets?

Usually, we use scatter plots when we want to understand the relationship between just two variables: one independent variable, which appears on the X-axis, and one dependent variable, which plots on the Y-axis. However, a scatter plot can also accommodate three or more variables.

To plot multiple data sets in Google sheets:

• Insert your data in the Google Sheet. Be sure to enter the independent variable first. That’s because Google Sheets is programmed to interpret the first column of data as the independent variable, and all the other columns as the dependent variables.

• Highlight all the data to be plotted in the scatter.

• Click on the “Chart” icon in the toolbar.

• If Google Sheets doesn’t open a scatter plot by default, proceed to step 5.

• From the chart editor sidebar, select “Setup.”

• Click on “Chart type.” A dropdown menu will appear.

• To convert the chart to a scatter plot graph, scroll down the dropdown menu and select “Scatter plot.”

Take Data Analysis to the Next Level

Google Sheets’ chart wizard can help convert a seemingly complicated data set into impressive charts and graphs that are easy to interpret. With this information, nothing prevents you from jumping right in to create your first scatter plot. How do you use Google Sheets charts to display your data? Do you have any hacks to share with fellow data enthusiasts? Let’s engage in the comments.