How to Create a Correlation Graph in Excel

Correlation graphs help determine the linear relationship between two types of variables. The variables are placed on a vertical and horizontal axis for creating the graphic. To make a perfect correlation graph with a trendline, trendline equation, axis points, axis titles, etc., using Microsoft Excel is the best way. In this article, I will show you how you can create a correlation graph in Excel.

What is a Correlation Graph?

A correlation graph, or a correlation matrix plot, shows a dataset’s relationship between multiple variables. The data is shown as a group of points with the value of one position variable on the horizontal axis and the value of another position variable on the vertical axis.

Introduction to the Dataset

I’ve provided a list of shoe sizes for people of various heights, using US and EU standards.

Easiest Way to Create a Correlation Graph in Excel

The easiest way to create a correlation graph is to use a scatter plot in Excel.

Usage Guide

Step_1: Select cell range A1:A12, press CTRL, and select cell range C1:C12 again.

Here, values from column Height are for the Y-axis, and values from columns Shoe Size (US) and Shoe Size (EU) are for the X-axis. You can select either column Shoe Size (US) or Shoe Size (EU) for X-axis plotting in the correlation graph.

Step_2: Go to the Insert tab.

Step_3: Select the Scatter plot from the Charts group.

The correlation graph will appear on the screen right away.

Step_4: Click on the plus (+) symbol in the top-right corner of the chart.

The Chart Elements drop-down menu will open up.

Step_5: Select the Trendline checkbox from the Chart Elements menu.

Chart elements in Excel

You will see a trendline like this one in the below image:

You can customize the appearance of the chart according to your choice.

Step_6: Right-click on the trendline.

The Context Menu will appear.

Step_7: Select Format Trendline.

Create correlation graph in Excel using format trendline

The Format Trendline task pane will take place on the right side of your computer screen.

Step_8: Select Display Equation on Chart.

Step_9: Select Display R-Squared Value on the Chart.

Display Equation on Chart in Excel correlation graph

Final Result >

A complete correlation graph is ready for you!

Final correlation graph made in Excel

The correlation graph shows an inclining trend in the image above.

Types of Correlation

Types of correlation depend on the meeting point of variables or attributes in the scatter plot. It expresses the closeness between the variable connections. You can find three situations in the correlation plots:

Types of Correlation

1. Positive Correlation

The upward or downward movement of one variable will occur after the increase or decline of another variable if there is a positive correlation. A strong positive correlation is scaled at +1.

2. Negative Correlation

In the case of a negative correlation, the two sets of variables move in the opposite direction. Here, one variable declines when the other rises. A strong negative correlation is scaled at -1.

3. No Correlation

It is an irregular correlation. When the variables do not show any linear relation in their change, it will be considered a no-correlation or zero-correlation situation. A no correlation or zero correlation is scaled at 0.

Conclusion

After reading this article, I hope you can now successfully create a correlation graph in Excel and also determine the nature of the correlation graph according to its type. Please share your thoughts about this article in the comment box. Have a good day!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *