How to Make Correlation Graph in Excel (with Easy Steps)

In Microsoft Excel Calculating correlation is one of the simplest tasks to do. A correlation graph shows the relation between two or more variables. In this article, I will share with you how you can make a correlation graph in Excel.

Introduction to Correlation Graph in Excel

A Correlation Graph is a measurement between two sets of data or variables. It is mostly used in economics, statistics, and social science. It is used to see the differences or measure relations between two or more variables in a graph.

Direction of Correlation:

There are two types of direction in correlation. In the following check out both directions-

Positive– When the correlation produces an upward slope that means the correlation is positive. If variable 1 increases, variable 2 will also increase – and vice versa.
Negative– When the correlation produces a downward slope that means the relationship between the variables is inversely proportional. This is called a negative correlation. If Variable 1 increases, variable 2 will decrease – and vice versa.

In the following, I will show you some quick steps to make a correlation graph in Excel.

Step 1: Creating a Correlation Dataset

Suppose we have a dataset of monthly average temperature and air conditioners sold every month.

Select the two variables of the dataset and go to “Scatter chart” from the “Insert” option.

Step 2: Inserting and Naming Coordinates to Make Correlation Graph

A scatter chart will appear.
Click on the chart and press on the “plus” sign to appear options.
From the options click “Axis Titles” to name the axis.

After naming the chart it will look like the following.

Step 3: Formatting Correlation Graph

In the chart, click on any point and then right-click the mouse button.
Choose “Add Trendline”.

From the “Format Trendline” option select “Linear”.
Put a tick mark by clicking on the “Display Equation on Chart” and “Display R-squared value on chart”.

As you can see we have successfully made our correlation chart in Excel.

Things to Remember

The correlation graph is not able to distinguish between dependent and independent data. So, when applying data be aware of the data you are supplying.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Make Correlation Graph.xlsx

Conclusion

In this article, I have tried to cover all the steps to make a correlation graph in Excel. You can make it and design the chart according to your choice. Don’t forget to share your opinion in the comment section below. Enjoy!

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

Get FREE Advanced Excel Exercises with Solutions!