How to Calculate Cross Correlation in Excel

Cross correlation is a way to measure the degree of similarity between a time series and a lagged version of another time series.

This type of correlation is useful to calculate because it can tell us if the values of one time series are predictive of the future values of another time series.

In other words, it can tell us if one time series is a leading indicator for another time series.

The following example shows how to calculate the cross correlation between two time series in Excel.

Example: How to Calculate Cross Correlation in Excel

Suppose we have the following two time series in Excel that show the total marketing spend (in thousands) for a certain company along with the the total revenue (in thousands) during 12 consecutive months:

We can calculate the cross correlation for various lags between the two time series by typing the following formula into cell E2:

=CORREL(OFFSET(A$2:A$13,0,0,COUNT(A$2:A$13)-D2,1),OFFSET(B$2:B$13,D2,0,COUNT(B$2:B$13)-D2,1))

We can then click and drag this formula down to the remaining cells in column E:

cross correlation in Excel

From the output we can see:

The correlation at lag 0 (i.e. normal correlation between the two time series with no lag) is 0.77.
The correlation at lag 1 is 0.93.
The correlation at lag 2 is 0.95.

And so on.

Note that the correlation is highest with a lag value of 2 between the two time series.