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.
Read More: How to Make Correlation Heatmap in Excel
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.
Read More: How to Make a Correlation Table 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.
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!
- How to Calculate Autocorrelation in Excel
- How to Calculate Cross Correlation in Excel
- How to Calculate Correlation between Two Stocks in Excel
- How to Do Correlation and Regression Analysis in Excel
- How to Make a Correlation Matrix in Excel
- How to Interpret Correlation Table in Excel
- How to Calculate Partial Correlation in Excel