Plot Multiple Data Ranges on a Single Chart in Google Sheets

Reading Time: 3 minutes

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

Setting the plot
Steps to plot multiple data ranges in a single chart
Customising the chart
Adding trendline and equation

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 :

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).

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:

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 :

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 :

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 R² checkbox to demonstrate the R² value of the graph. The higher the R² value, the higher is correctness to predict any value and fit to the model.

Figure 7: Select the highlighted checkboxes and drop-down menu to have the best visual of the individual data ranges

See also

Some other articles that you may find useful.

https://blog.tryamigo.com/how-to-perform-linear-regression-using-google-sheets/

https://blog.tryamigo.com/correl-function-in-google-sheets/

https://blog.tryamigo.com/how-to-create-a-semi-log-graph-in-google-sheets/

References