When analyzing data, a scatter plot is one of the easiest ways to discover the relationship between two variables. And the best part? This can be done in Google Sheets.
In this guide, we will explain how to create a scatter plot in Google Sheets.
Why a point cloud?
A scatter plot is useful when analyzing data because:
- It helps to define the trend of the data.
- You can actually see the data range, i.e. the maximum and minimum values recorded.
- It reveals both linear and non-linear relationships between variables.
- Its interpretation is simple.
How to Make a Scatterplot in Google Sheets
Creating a scatter plot in Google Sheets is quite simple.
- Highlight the data you want to plot in a chart. To do this, click on the first cell, then drag the mouse over all the other cells you want to graph.
- From the menu at the top of your spreadsheet, select "Insert."
- In the resulting drop-down submenu, click on "Chart". This will launch a chart on the spreadsheet, usually on the right side of your data. A chart editor sidebar accompanies the chart.
- By default, Google is programmed to display the graph it deems best suited to the data. In most cases, it will display a scatter plot. But if not, skip to step five.
- In the sidebar of the chart editor, select "Configuration".
- Click on “Chart type”. A drop-down menu will appear.
- To convert the chart to a scatter plot, scroll down the drop-down menu and select "Scatter plot". It may appear under "Suggested" or "Other", again depending on Google's default analysis of the data.
- At this point, there should be a scatter plot for the data.
How to customize a scatter plot on Google Sheets
The great thing about scatter plots in Google Sheets is that you can customize just about any aspect of a chart. Including:
- Change background color;
- Changed text or added chart title, horizontal axis, and vertical axis title;
- Change the color of the points on the point cloud, for example from blue to red; Where
- Added grids and bars.
To do any of these things, just select "Customize" from the sidebar of the chart editor.
How to Make a Scatterplot in Google Sheets with the Line of Best Fit
Scatter plots generally provide many benefits, but they may not yield much information, especially when analyzing a large data set. To find out if there is a discernible pattern in a given data set, you can add a line of best fit.
A line of best fit, also known as a trend line, is a line that crosses a scatter plot in an attempt to show the general direction your data seems to be heading. In other words, it attempts to plot the data points that best express the relationship between the variables used in your analysis.
A line of best fit is useful in three ways:
- This helps to determine if the variables show strong correlation (co-movement). If the variables are highly correlated, the bulk of the data points will be very close to the line of best fit.
- It reveals the trend of the data. It can easily show whether there is an uptrend or a downtrend.
- It reveals data points that are too far from the line of best fit.
Once you've created a scatter plot in Google Sheets, a line of best fit can be added in a few simple steps:
- In the sidebar of the chart editor, click "Customise".
- In the resulting drop-down menu, click "Series".
- Scroll down and check the box next to "Trendline".
Sometimes the Graph Editor sidebar may disappear after a scatter plot is created. To restart it:
- Click on the point cloud. You should see three small dots in the upper right corner of your scatter plot. These points make up the ellipse of the graph.
- Click on the ellipses.
- Select "Edit Chart".
How to Create a Scatter Chart in Google Sheets
If a bar or line chart makes your data a bit cluttered, a scatter chart might be the perfect solution. Here's how you can create one:
- Highlight the first column of data, hold down the Shift key, then highlight the other columns you want to plot by left-clicking on their names.
- Click the chart icon in the menu at the top of the spreadsheet. In Google Sheets, the chart icon appears as a small square with three vertical bars.
- By default, Google Sheets will open a chart that best matches the data, and in most cases it will be a scatter chart. If another type of chart opens, skip to step 4.
- In the sidebar of the chart editor, select "Configuration".
- Under 'Chart type', click the drop-down menu to select the column chart type.
- To convert the chart to a scatter chart, scroll down the drop-down menu and select "Scatter".
Additional FAQs
How to make an average graph on Google Sheets?
In addition to a scatter plot and a line of best fit, sometimes you may want to add an average line to a graph. This can help distinguish data points above or below the mean. Here's how:
• Insert your data into the Google Sheet.
• Create a new column and name it “Average”.
• Enter the following formula in the first cell of 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.
• Press “Enter”. At this point, Google Sheets will automatically generate the average of the data contained in the specified cells.
• Click on the first cell under the “Average” column.
• With the cursor positioned in the lower right corner of the first cell, drag your mouse over the other cells in the specified range. This will automatically repeat the average value in each of these cells.
• Click on the chart icon in the menu at the top of your spreadsheet. As before, Google Sheets will open a chart that best matches your data. In it, there will be a line chart showing the average value of your data. You can change the chart type by opening the chart editor sidebar.
How to plot a scatter plot in Excel?
To plot a scatter plot in Excel:
• Open the spreadsheet containing the data.
• Highlight data. To do this, click on the first cell containing the data you want to plot in the scatter plot, then drag the mouse over all the other cells.
• Click on “Insert”, then select “Scatter”.
What is a scatter chart in Excel?
A scatter plot, also called a scatter plot, shows the relationship between two variables in two-dimensional space. It helps to show how a set of data from two variables is distributed. For example, it can help you determine if there is a relationship between the number of times customers visit your website and the number of sales made on a given day.
Likewise, it can help determine if there is a relationship between the height and weight of a group of people.
How to create an XY chart in Google Sheets?
• Highlight the data set you want to plot in the chart
• Select “Insert” from the menu at the top of the spreadsheet. You can also click on the "Chart" icon in the toolbar.
How to create a scatter plot with multiple datasets in Google Sheets?
Usually, we use scatter plots when we want to understand the relationship between just two variables: an independent variable, which is plotted on the X axis, and a dependent variable, which is plotted on the Y axis. However , a scatter plot can also contain three or more variables.
To plot multiple datasets in Google Sheets:
• Insert your data into the Google sheet. Be sure to enter the independent variable first. This is because Google Sheets is programmed to interpret the first column of data as an independent variable and all other columns as dependent variables.
• Highlight all data to be plotted in the scatter plot.
• Click on the “Chart” icon in the toolbar.
• If Google Sheets does not open a scatter plot by default, skip to step 5.
• In the sidebar of the graph editor, select “Configuration”.
• Click on “Chart type”. A drop-down menu will appear.
• To convert the chart to a scatter chart, scroll down the drop-down menu and select “Scatter”.
Take data analysis to the next level
The Google Sheets Chart Wizard can help convert a seemingly complicated data set into impressive, easy-to-interpret charts. With this information, nothing prevents you from starting directly to create your first point cloud. How do you use Google Sheets charts to display your data? Do you have any hacks to share with other data enthusiasts? Let's engage in the comments.
