Mursalin Ibne Salehin
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 x–axis and the Weight on the y–axis. 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 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.
- 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.
- As a result, you will see the graph on the sheet.
STEP 2: Add Chart and Axis Title
- In the second step, you can change the Chart Title and Axis Title to make the graph more understandable.
Read More: How to Create Activity Relationship Chart 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.
- 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 4: Display R-Squared Value
- In the following step, we will display the R–Squared value on the graph. We can use this value to generate the correlation coefficient, R.
- For that purpose, double–click on the trendline. It will open the Format Trendline settings on the right side of the sheet.
- Check ‘Display R–squared value on chart’ from there.
- As a result, you will see the R–Squared value on the chart.
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.
- After that, you will see the equation of the linear trendline.
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 R–Squared 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)
- After that, press Enter to see the result.
- You can see the correlation coefficient is 0.8135 which indicates a positive correlation.
Read More: How to Create a Relationship Between Tables in Excel (3 Ways)
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 X–variable in the first argument and Y–variable 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.
Read More: How to Manage Relationships in Excel (with Detailed Steps)
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.
