How to Calculate Rolling Correlation in Excel

This guide will explain how to calculate rolling correlation in Excel using the CORREL function.

The rules for using the CORREL function in Excel are the following:

  • The CORRE function ignores inputted arguments containing text, logical values, or empty cells. However, the function will support cells with zero values.
  • When the inputted arguments for array1 and array2 have different numbers of data points, the function will return a #N/A error. 
  • When the arrays are empty, or the standard deviation of their values is equal to zero, the function will return a #DIV/0! error. 

Since it has several built-in tools and functions, Excel is a popular tool for statistical calculations and data analysis. For example, Excel is an excellent tool to use for correlation analysis. 

In this case, we will focus on performing a rolling correlation in Excel. So a correlation measure the linear relationship between two variables. When we measure and relate the variance of each variable, the correlation will indicate the strength of the relationship between the two variables.

Furthermore, there are many types of correlations. However, we will focus on the rolling correlation. And a rolling correlation is a type of correlation between two different time series on a rolling window. 

Additionally, a rolling correlation is beneficial when we want to visualize the correlation between two different time series over time. And this would usually be a long and difficult process that would be prone to mistakes. But, we can easily perform this using the built-in function in Excel. 

Let’s take a sample scenario wherein we need to calculate rolling correlation in Excel.

Suppose we have a sales report for two different products. And this report shows the number of sales for each product over 10 months. So you want to calculate the rolling correlation of the two products for 4 months. To make your work easier, you utilized the CORREL function. 

Thus, you determine the correlation between the two product sales for the previous 4 months. 

Before we move on to a real example of calculating rolling correlation in Excel, let’s first learn the syntax of the CORREL function.

 

The Anatomy of the CORREL Function

The syntax or the way we write the CORREL function is as follows:

=CORREL(array1, array2)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we activate any function in Excel.
  • CORREL() refers to our CORREL function. And this function is used to return the correlation coefficient between two data sets.
  • array1 is a required argument. So this refers to a range of cell values which should be numbers, names, arrays, or cell references that contain numbers.
  • array2 is another required argument. And this refers to the second range of cell values that should contain numbers, arrays, or cell references that have numbers.

Great! Now we can move on and dive into a real example of calculating rolling correlation in Excel.

 

A Real Example of Calculating Rolling Correlation in Excel

Let’s say we have a sales report data set containing the number of sales or two different products during 10 months. And we want to calculate the rolling correlation between the two products. So our initial data set would look like this:

Initial data set

 

Essentially, a rolling correlation is a type of correlation between two different time series. And this is often used when we want to visualize the correlations between two different time series. 

For instance, we want to calculate the 4-month rolling correlation between the two different time series. To do this, we will utilize the CORREL function in Excel. So the CORREL function is used to get the correlation between two data sets.

Furthermore, the CORREL function will return a correlation coefficient closer to +1, a positive correlation, or -1, a negative correlation. So a positive correlation would mean the values in one array would increase if the values in the second array increase as well. 

However, a correlation coefficient that is closer to 0 would indicate no or weak correlation. Additionally, we can use a longer rolling time frame if we want. To do that, we simply input a longer time frame in our CORREL function.

After using the CORREL function to calculate the 4-month rolling correlation of our data set, we can proceed to visualize our rolling correlations.  To do this, we can easily insert a simple line chart.

In our line chart, the y-axis will display the rolling 4-month correlation between the two different time series, while the x-axis will display the ending month for the rolling correlation.

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can explain the steps of how to calculate the rolling correlation in Excel using the CORREL function.

 

How to Calculate Rolling Correlation in Excel

In this section, we will explain the step-by-step process of how to calculate the rolling correlation in Excel with the help of the CORREL function. Furthermore, each step has detailed instructions and pictures.

To apply this method in your work, simply follow the steps below.

1. Firstly, we will create a new column to input the correlation coefficients. Then, we will utilize the CORREL function to determine the correlation between the two different time series. To do this, we will type in the formula “=CORREL(C2:C5,D2:D5)”.

Lastly, we will press the Enter key to return the result.

Rolling Correlation in Excel

 

2. Secondly, we will drag down the Fill Handle tool to copy the formula and apply it to the rest of the cells.

Copy formula down

 

3. And tada! We have successfully calculated the rolling correlation in Excel.

Final result

 

4. Furthermore, we can proceed to visualize the rolling correlations of our data set by inserting a simple line chart. To do this, we will highlight the column containing the rolling correlations and go to the Insert tab. Next, we will select Line.

Lastly, we will click the Line icon under 2-D Line found in the dropdown menu.

Rolling Correlation in Excel

 

5. And tada! We have successfully visualized our rolling correlations in Excel.

Rolling Correlation in Excel

 

And that’s pretty much it! We have explained how to calculate the rolling correlation in Excel. Furthermore, we also explained how to visualize the rolling correlations using a line chart. You can apply this method to your work whenever you need to get the rolling correlation.

Are you interested in learning more about what Excel can do? You can now use the CORREL function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like