How to Calculate Cross Correlation in Excel (2 Quick Ways)

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.

Steps:

  • Type the following formula in Cell C13
=CORREL(B5:B11,C5:C11)
  • 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.

Use Excel CORREL Function to Calculate Cross Correlation

I have created an Excel Chart to show the positive correlation visually.

Read More: How to Calculate Pearson Correlation Coefficient in Excel (4 Methods)


1.2 Negative Correlation

Now we’ll determine a negative correlation between Facebook and Youtube using the same CORREL function.

Steps:

  • In Cell C13, write the following formula-
=CORREL(B5:B11,C5:C11)
  • 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.

Use Excel CORREL Function to Calculate Cross Correlation

The Excel Chart shown below expresses the same thing graphically.

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


1.3 No Correlation

Let’s use the CORREL function again to calculate No Correlation. Here we’ll find it between Facebook and Netflix.

Steps:

  • Write the following formula in Cell C13
=CORREL(B5:B11,C5:C11)
  • 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.

Use Excel CORREL Function to Calculate Cross Correlation

We can easily understand it from the Excel Chart too.

Read More: How to Do Correlation in Excel (3 Easy Methods)


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.

Steps:

  • Click File beside the Home tab.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

  • Then click Options and a dialog box will open up.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

  • Next, click Add-ins.
  • Then select Analysis ToolPak and press Go.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

  • Later, from the appeared dialog box, mark Analysis ToolPak and press OK.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

The tool is activated, now let’s apply it.

  • Click as follows: Data > Data Analysis.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

  • Select Correlation from the Analysis Tools and press OK.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

  • 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.

Apply Data Analysis ToolPak to Create Cross Correlation Matrix in Excel

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.

Read More: How to Find Correlation between Two Variables in Excel


Practice Section

You will get a practice sheet in the Excel file given above to practice the explained ways.


Conclusion

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo