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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


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 measure relations or to see the differences between variables in a graph.

Make Correlation Graph in Excel

Direction of Correlation:

There are two types of direction in correlation. In the following check out both the 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.

3 Easy Steps to Make a Correlation Graph in Excel

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


Step 1: Create a Correlation Dataset

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

Make Correlated graph in excel

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

Step 2: Insert and Name the 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.

Make Correlation Graph

  • After naming the chart it will look like the following.

Make Correlated Graph

Step 3: Format the 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.

Read More: How to Find Correlation between Two Variables 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.

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!


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo