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

## 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 to calculate the correlation. 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 1: Create Scatter Plot

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

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

• As a result, you will see the RSquared 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 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)`

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

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

## Related Articles

<< Go Back toÂ Excel CorrelationÂ |Â Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF