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.
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.
- 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.
- After naming the chart it will look like the following.
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.
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.
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!