Table of Contents
To calculate rolling correlation in Excel, first select the data points for which you want to calculate the correlation. Then, use the CORREL function to calculate the correlation between these data points. Next, use the OFFSET function to create a rolling window of data points by specifying the number of data points and the starting point for each calculation. Finally, use the CORREL function again to calculate the rolling correlation between the rolling window of data points and the selected data points. Repeat this process for each desired rolling window to obtain a series of rolling correlation values.
Calculate Rolling Correlation in Excel
Rolling correlations are correlations between two time series on a rolling window. One benefit of this type of correlation is that you can visualize the correlation between two time series over time.
This tutorial explains how to calculate and visualize rolling correlations in Excel.
How to Calculate Rolling Correlations in Excel
Suppose we have the following two time series in Excel that display the total number of products sold for two different products during a 20-month period:
To calculate the 3-month rolling correlation between the two time series, we can simply use the CORREL() function in Excel. For example, here’s how to calculate the first 3-month rolling correlation between the two time series:
We can then drag this formula down to the rest of the cells in the column:
Each cell in the column titled “Rolling 3-month correlation” tells us the correlation between the two product sales for the previous 3 months.
Note that we could use a longer rolling time frame if we’d like as well. For example, we could instead calculate the rolling 6-month correlation:
How to Visualize Rolling Correlations in Excel
Once we’ve calculate a rolling correlation between two time series, we can visualization the rolling correlation using a simple line chart. Use the following steps to do so:
Step 1: Highlight the rolling correlation values.
First, highlight the values in the cell range D7:D21.
Next, click the Insert tab along the top ribbon in Excel. Within the Charts group, click on the first chart option in the Line or Area Chart section.
The following line chart will automatically appear:
The y-axis displays the rolling 6-month correlation between the two time series and the x-axis displays the ending month for the rolling correlation.
Feel free to modify the title, axes labels, and colors to make the chart more aesthetically pleasing.
Additional Resources
How to Create and Interpret a Correlation Matrix in Excel
How to Calculate Autocorrelation in Excel
How to Find the P-value for a Correlation Coefficient in Excel