Correlation Chart in Excel
Correlation basically means a mutual connection between two or more sets of data. In statistics bivariate data or two random variables are used to find the correlation between them. Correlation coefficient is generally the measurement of correlation between the bivariate data which basically denotes how much two random variables are correlated with each other.
If the correlation coefficient is 0, the bivariate data are not correlated with each other.
If the correlation coefficient is -1 or +1, the bivariate data are strongly correlated with each other.
r=-1 denotes strong negative relationship and r=1 denotes strong positive relationship.
In general, if the correlation coefficient is close to -1 or +1 then we can say that the bivariate data are strongly correlated to each other.
The correlation coefficient is calculated using Pearson’s Correlation Coefficient which is given by :
r : Correlation coefficient : Values of the variable x. : Values of the variable y. n : Number of samples taken in the data set. Numerator : Covariance of x and y. Denominator : Product of Standard Deviation of x and Standard Deviation of y.
In this article we are going to discuss how to make correlation charts in Excel using suitable examples.
Example 1 : Consider the following data set :
FINDING CORRELATION COEFFICIENT IN EXCEL
In Excel to find the correlation coefficient use the formula :
=CORREL(array1,array2) array1 : array of variable x array2: array of variable y
To insert array1 and array2 just select the cell range for both.
1. Let’s find the correlation coefficient for the variables and X and Y1.
array1 : Set of values of X. The cell range is from A2 to A6.
array2 : Set of values of Y1. The cell range is from B2 to B6.
Similarly, you can find the correlation coefficients for (X , Y2) and (X , Y3) using the Excel formula.
Finally, the correlation coefficients are as follows :
From the above table we can infer that :
X and Y1 has negative correlation coefficient.
X and Y2 has positive correlation coefficient.
X and Y3 are not correlated as the correlation coefficient is almost zero.
Correlation Chart in Excel:
A scatter plot is mostly used for data analysis of bivariate data. The chart consists of two variables X and Y where one of them is independent and the second variable is dependent on the previous one. The chart is a pictorial representation of how these two data are correlated with each other. Three cases are possible on the basis of the value of the correlation coefficient, R as shown below :
Example 2: Consider the following data set :
The correlation coefficients for the above data set are :
The steps to plot a correlation chart are :
- Select the bivariate data X and Y in the Excel sheet.
- Go to Insert tab on the top of the Excel window.
- Select Insert Scatter or Bubble chart. A pop-down menu will appear.
- Now select the Scatter chart.
- Now, we need to add a linear trendline in the scatter plot to show the correlation between the bivariate data. In order to do so, select the chart and from the top right corner click on the “+” button and then check the box of Trendline.
- The trendline is now added and our correlation chart is now ready.
- Now you can format the Trendline by selecting and clicking on the “Format Trendline” option. A dialog box will open where you can change the type and color of the trendline and also show the value in the chart.
You can further format the above chart by making it more interactive by changing the “Chart Styles”, adding suitable “Axis Titles”, “Chart Title”, “Data Labels”, changing the “Chart Type” etc. It can be done using the “+” button in the top right corner of the Excel chart.
Finally, after all the modification the charts look like :
Since the correlation coefficient is R=-0.79, we have obtained a negative correlated chart. The linear trendline will grow downwards.
Since the correlation coefficient is R=0.89, we have obtained a positive correlated chart. The linear trendline will grow upwards.
Since the correlation coefficient is R=0.01, which is approximately 0, so we have obtained a zero correlated chart. The linear trendline will be a straight line parallel to X-axis and it implies the bivariate data X and Y3 are not correlated to each other.