In statistical analysis, calculating **cross-correlation** is a 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 brilliantly and fast. So you will learn 2Â quick ways to **calculate cross-correlation** in Excel from this article with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**What Is Cross Correlation?**

Cross-correlation is a statistical term. It defines the **relationship between two or more variables** in terms of time. It tracks how one or more variables change with the change of another variable. We can also use multiple time series to track the amount of variation. From different time scales, we will find out the maximum similarity or dissimilarity.

The amount of correlation coefficient ranges from **+1** to **-1**.

**+1** means the variable is identical and **-1** means opposite one another. And when this is **0** means, there is no relationship between the variables.

The mathematical formula for calculating the cross-correlation is given below:

Here,

**r** = Cross-correlation coefficient

**n** = Number of data available

**xy** =Sum, and product of the data of the first and second variables.

**x **= Sum of the first variable

**y **= Sum of the second variable

**x2 **=Square sum of each data of the first variable

**y2 **=Square sum of each data of the second variable

In simple words, we can explain the cross-correlation. We consider we have a dataset containing columns **X**, **Y**, and **Z**. Here, **X** is independent and **Y **and **Z** are dependent. If **X** and **Y** are positively correlated, then when **X** increases **Y** will also increase.

Again, if **X** and **Z** are the same positively co-related, **Z** will increase as **X** increases. So, **Y** and **Z** are said to be cross-correlated, as both of them show the same behavior concerning **X**.

**2 Ways to Calculate Cross Correlation in Excel**

In this article, we will show 2 examples to calculate cross-correlation in Excel. Those examples are based on the **CORREL function** and **Add-In**s of Excel. Those examples are based on the **CORREL** function and **Add-Ins** of Excel. We will consider the following dataset.

We will calculate the cross-correlation of **Production** and **Revenue** based on the **Investment**.

**1. Calculate Cross Correlation Without Time Lag**

We can calculate cross-correlation without time lag in 2 ways- the **CORREL** function and the **Data Analysis** tool.

#### i. Using Excel CORREL Function

**CORREL function**returns the correlation coefficient between two data sets.

Here, we will use the **CORREL** function to calculate cross-correlation without time lag. As we will not consider time lag, we will consider the whole dataset for calculation.

**ðŸ“Œ ****Steps:**

- We add new rows in the dataset to find the correlation efficiency.

- First, we will
**calculate the correlation coefficient**between**Investment, Production**, and**Investment, Revenue**by applying the following formulas.

**On Cell D16:**

`=CORREL(C5:C14,D5:D14)`

**On Cell D17:**

`=CORREL(C5:C14,E5:E14)`

We can see both cases correlation coefficient is close to **1**. This means **Production** and **Revenue** are both positively co-related with **Investment**. Now, we will find out the cross-correlation coefficient between **Production** and **Revenue**.

- Put the following formula on
**Cell D18**.

`=CORREL(D5:D14,E5:E14)`

This result is also close to **1**. So, **Production** and **Revenue** will show similar behavior. If **Production** increases, **Revenue** will also increase.

We can also plot the following dataset in a line chart.

This chart clearly indicates that the other two variables **Production** and **Revenue** are positively related to the **Investment**.

**ii. Using the Data Analysis Tool of Analysis-ToolPak**

Here, we will use default **Add-ins** of Excel to calculate the cross-correlation.

**ðŸ“Œ ****Steps:**

- Go to
**File >> Options >> Add-ins**. - After that, select
**Add-ins**and then the**GoÂ**button.

- The
**Add-ins**window appears. - Choose
**Analysis Toolpak**add-in from the list. - Then, press
**OK**.

This add-is has been attached to the main tab of Excel.

- Now, click on the
**Data Analysis**option in the**DataÂ**tab.

- Select the
**Correlation**option from the**Data AnalysisÂ**window. - Finally, press the
**OK**button.

- The
**Correlation**window appears. - Choose the
**Input Range**from the dataset. We choose**Investment**,**Production**, and**RevenueÂ**columns. - Tick the
**Labels in first row**option. - After that, select a cell as the
**Output Range**.

- Then, click on the
**OKÂ**button.

We can see the correlation coefficients are shown here.

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

**Similar Readings**

**How to Calculate Pearson Correlation Coefficient in Excel (4 Methods)****How to Make a Correlation Matrix in Excel (2 Handy Approaches)****Find Spearman Rank Correlation Coefficient in Excel (2 Ways)****How to Calculate Intraclass Correlation Coefficient in Excel****How to Calculate Partial Correlation in Excel (3 Suitable Ways)**

**2. Calculate Cross Correlation with Time Lag Using CORREL Function**

Here, we will consider time lag to calculate the correlative coefficient. It is assumed that investment in the company will not reflect immediately. It may be seen that after a particular time period investment dominate production and revenue.

**ðŸ“Œ ****Steps:**

- First, we calculate the correlative coefficient considering different lags in the
**Range H5:I9**using the**CORREL**function.

We can see for **lag 3**, we get the maximum coefficient value.

- Now, use
**lag 3**for calculating the cross-correlative coefficient using the following formula.

**Â**

`=CORREL(D5:D11,E8:E14)`

We calculated the cross-correlation for **lag 3**.

Here is a graph for cross-correlation with time lag 3.

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

**Conclusion**

In this article, we showed **2** ways to calculate the cross-correlation in Excel with proper explanation. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy**Â and give your suggestions in the comment box.

this post does not deal with cross-correlation

Hi Don. Thanks for letting us know about this. ðŸ™‚ We have updated the blog post. Regards

-ExcelDemy Team