In statistical analysis, calculating cross-correlation is a too common task. It may be quite tricky and time-consuming if you try to find it in the traditional mathematical way. But in Excel, you can do it in a brilliant and fast way. So you will learn 2 quick ways to calculate cross correlation in Excel from this article with proper illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice independently.
What Is Cross Correlation?
Cross-correlation will express how two sets of data are related to each other. The range of correlation factors is from -1 to +1. There are three kinds of correlations-
- Positive Correlation: If one variable increases then the other follows the same.
- Negative Correlation: If one variable increases then the other decreases.
- No Correlation: One variable’s movement is not related to others.
2 Ways to Calculate Cross Correlation in Excel
Let’s get introduced to our dataset first. It represents the growth rate in the stock market of Facebook, Youtube, Twitter, and Netflix.
1. Use Excel CORREL Function to Calculate Cross Correlation
Now we’ll apply the CORREL function in Excel to calculate cross-correlation for the above dataset.
1.1 Positive Correlation
First, we’ll calculate the positive correlation between the data of Facebook and Twitter.
- Type the following formula in Cell C13–
- Then just press the Enter button to get the output.
And have a look at the output, the coefficient factor is tense to +1 which means if Facebook’s growth increases then the growth of Twitter also increases.
I have created an Excel Chart to show the positive correlation visually.
1.2 Negative Correlation
Now we’ll determine a negative correlation between Facebook and Youtube using the same CORREL function.
- In Cell C13, write the following formula-
- Finally, press the Enter
Here the correlation factor is -0.55. So we can understand that their growth is negatively related- if one increases then the other will decrease.
The Excel Chart shown below expresses the same thing graphically.
1.3 No Correlation
Let’s use the CORREL function again to calculate No Correlation. Here we’ll find it between Facebook and Netflix.
- Write the following formula in Cell C13–
- Hit the Enter button to finish.
The correlation coefficient is tense to zero here which says there is no relation between the data of Facebook and Netflix. So you will not be able to predict Netflix by analyzing the data of Facebook.
We can easily understand it from the Excel Chart too.
2. Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel
The CORREL function can’t calculate cross-correlation greater than two sets of data. So if you have data greater than two sets then you will have to use the Data Analysis ToolPak to create a Correlation Matrix. It is an Add-in of Excel. If you haven’t activated yet then follow the steps to activate it.
- Click File beside the Home tab.
- Then click Options and a dialog box will open up.
- Next, click Add-ins.
- Then select Analysis ToolPak and press Go.
- Later, from the appeared dialog box, mark Analysis ToolPak and press OK.
The tool is activated, now let’s apply it.
- Click as follows: Data > Data Analysis.
- Select Correlation from the Analysis Tools and press OK.
- After that, set the Input Range and mark Columns from the Grouped By section.
- Mark Labels in first row.
- Finally, set the Output Range from the Output Options and press OK.
Soon after you will get the output like the image below. Here also we got the same result. The plus point is that it shows all the combinations of correlations between them like the correlation of Twitter and Netflix with Youtube, and Netflix with Twitter. Twitter is negatively correlated with Youtube, Netflix has no correlation with Youtube, and Netflix is less positively correlated with Twitter.
The output is clearly understandable from this Excel Chart.
You will get a practice sheet in the Excel file given above to practice the explained ways.
I hope the procedures described above will be good enough to calculate cross-correlation in Excel. Feel free to ask any question in the comment section and please give me feedback.