Google Sheets: Plot Multiple Data Ranges on a Single Chart

Google Sheets: Plot Multiple Data Ranges on a Single Chart
Reading Time: 3 minutes

In this article, you’re going to learn how to plot multiple data ranges on a single graph along with their equations of linear regression.

Background

You can plot multiple data ranges on a single graph in Google Sheets. This can help you study the relationship between variables by looking at the graph, for all the ranges plotted. In this article, you will run through the process of plotting multiple data ranges.

  • In this article, we will run through how you can plot multiple data ranges on a single chart using Google sheets. 
  • But before jumping in, you must put the dependent variable data on the Y-axis and the independent variable data on the X-axis. 
  • If you follow the steps as described in this article, this is what a sample output would look like :
Plot of multiple data ranges
Figure 1: Plot of multiple data ranges of profits against respective sales along with their respective trend lines and equations of regression.

Step to Plot Multiple Data Ranges on a Single Chart

Step 1:- Prepare a new dataset

  • Select the data common to both data ranges.
  • Paste the X-axis variable to create a new common column (i.e. Sales).
  • Copy and Paste the Y-axis variable to different columns (i.e. Profits 1 and Profits 2).
Create a new dataset with multiple columns for multiple data ranges
Figure 2: Create a new dataset with Sales as the common column and Profits with their new columns

Step 2:- Select the dataset of interest

  • Select the dataset you want to perform linear regression on.
  • Left-click on cell A11 and drag it down to C25 along with the titles of the variable columns as shown below:
Select the dataset
Figure 3:- Select the dataset of interest

Step 3:- Insert Chart to represent the regression

  • After selecting the cells, click on the Insert tab from the navigation bar and select the Chart option to create a graphical representation of the linear regression.
Figure 4: Select the Chart option from the Insert bar

Step 4:- Create a scatter plot for both datasets

  • After clicking on the Chart button, navigate to the Chart type drop-down menu.
  • Select Scatter chart as the chart type and select the Scatter plot, as shown below :
Selecting scatter plot to see scatter plots of individual data ranges
Figure 5: Selecting scatter plot to see scatter plots of individual data ranges

Step 5:- Customize the graph settings to visualize regression

  • To plot the trendline, select the Customize tab and go to the Series tab for plotting trendline :
Steps to plot the trendline of both data ranges
Figure 6: Steps to plot the trendline of both data ranges

Step 6: Plot trendline, equation of graph, and method used for regression analysis

  • After selecting the Series tab, checkmark the Trendline option to see the Trendline of the points as seen in the graph below.
  • Navigate to the Label drop-down menu and select the Use Equation option to display the equation of the graph, as displayed on the top.
  • Tick mark the Show R2 checkbox to demonstrate the R2 value of the graph. The higher the R2 value, the higher is correctness to predict any value and fit to the model.
Select the Trendline and Equation of Linear regression
Figure 7: Select the highlighted checkboxes and drop-down menu to have the best visual of the individual data ranges

References