How to Show Relationship Between Two Variables in Excel Graph

In this article, we will learn to show the relationship between two variables in an Excel graph. In our life, especially in the field of business and science, we often need to predict the future result for taking the necessary steps. But to do that, we need to know the relationship among variables first. Today, we will demonstrate step-by-step procedures. Using these steps, you can easily show the relationship between two variables in a graph in Excel. So, without further delay, let’s start the discussion.


Download Practice Book

You can download the practice from here.


What Is Correlation Between Two Variables?

The relationship between two variables is generally called the correlation. It indicates how strongly the variables are connected. To measure the correlation, a coefficient is used. This is called the correlation coefficient.

If the correlation coefficient is 0, then the variables are not correlated. That means they have zero correlation. Hence, a change in one variable will not affect the other. The graph of zero correlation is a straight line.

But, if the correlation coefficient is between -1 to +1, then the variables are related. For negative values, we say the variables are negatively correlated. So, we get a negative correlation and a graph like the picture below. From the graph, we can say that if one variable increases, then, the other one will decrease.

If we get a positive correlation coefficient, then the variables are positively correlated. The graph of the positive correlation is an increasing line. It denotes that if one variable increases, then another one will also increase.


Step-by-Step Procedures to Show Relationship Between Two Variables in Excel Graph

To explain the steps, we will use a dataset that contains information about the Height and Weight of some employees of a company. Here, we have two variables. We will plot the Height on the xaxis and the Weight on the yaxis. Generally, the Scatter Chart describes the relationship between two variables. Using the graph, we will show the relationship between Height and Weight. So, without any delay, let’s follow the steps below.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 1: Create Scatter Plot

  • First of all, we need to create a scatter plot using the dataset.
  • To do so, select the range C4:D13.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • After that, go to the Insert tab and select the Insert Scatter icon. A drop-down menu will appear.
  • Select the Scatter Plot icon from there.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • As a result, you will see the graph on the sheet.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 2: Add Chart and Axis Title

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 3: Insert Trendline

  • Thirdly, click on the chart. A plus (+) icon will appear.
  • Now, click on the plus (+) icon to see the options.
  • Check Trendline from there.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • Instantly, you will see a linear trendline on the graph.
  • From the trendline, we can say that the two variables are positively correlated.
  • That means if one variable increases, then the other one also increases.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 4: Display R-Squared Value

  • In the following step, we will display the RSquared value on the graph. We can use this value to generate the correlation coefficient, R.
  • For that purpose, doubleclick on the trendline. It will open the Format Trendline settings on the right side of the sheet.
  • Check ‘Display Rsquared value on chart’ from there.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • As a result, you will see the RSquared value on the chart.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 5: Show Trendline Equation on Chart

  • In step 5, we will add the equation of the trendline. This equation gives us an idea of how the trendline changes.
  • To add the equation of the trendline, check the ‘Display Equation on chart’ option in the Format Trendline settings.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • After that, you will see the equation of the linear trendline.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 6: Find Correlation Coefficient

  • In this step, we will find the correlation coefficient and see how strongly the variables are related.
  • In step 4, we found the RSquared value which is 0.6618.
  • We can use the SQRT function to get the correlation coefficient, R.
  • For that purpose, select Cell D5 and type the formula below:
=SQRT(0.6618)

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel

  • After that, press Enter to see the result.
  • You can see the correlation coefficient is 0.8135 which indicates a positive correlation.

Step-by-Step Procedures to Show Relationship Between Two Variables in Graph in Excel


STEP 6: Check Correlation Coefficient

  • You can also check the value of the correlation coefficient using the CORREL function to make sure the result from the graph is correct.
  • For that purpose, select Cell D16 and type the formula below:
=CORREL(C5:C13,D5:D13)

The CORREL function finds the value of the correlation coefficient of two variables. You need to enter the data series of the Xvariable in the first argument and Yvariable in the second argument.

  • Finally, press Enter to see the result.
  • You can see both values of the correlation coefficient are the same.


Final Decision

  • After completing the above steps, we will get the correlation coefficient, R, and the equation of the trendline.
  • After comparing the trendline with the graph of zero, positive, and negative correlation, we can say that the two variables have a positive correlation.
  • Also, the value of the correlation coefficient, R denotes a strong positive correlation.
  • That means if you increase one variable, the other one will also increase.


Conclusion

In this article, we have discussed step-by-step procedures to Show the Relationship Between Two Variables in an Excel Graph. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo