Excel incorporates pretty much every calculation aspect from numerous branches of science. As a result, itâ€™s easier to calculate the correlation between two stocks in Excel than in other mediums. Users can use the **CORREL function, Toolpak Add-in**, or conventional **Statistics Correlation Formula**.

Letâ€™s say we have the price data of two stocks stretching a time period couple of weeks. And we want to calculate the correlation between them.

In this article, we demonstrate the use of the **CORREL function, Toolpak Add-in** as well as conventional **Statistics Correlation Formula** to calculate correlation between two stocks in Excel.

**Table of Contents**hide

**What Is Correlation?**

In statistics, **Correlation** or **Correlation Coefficient** is the parameter to show coherence between two variables in response to the continuous fluctuating quantity of another. Its value ranges from** -1** to **1**. Therefore, it has three states of defining variable relationships. They are,

**Â ****(i) -1 indicates a Negative Correlation:** The relationship between the assigned variables is negative. That means the variables changes in the opposite direction.

**(ii) +1 indicates a Positive Correlation:** When variables change in the same direction, itâ€™s called they have a positive correlation.

**(iii) 0 indicates No Correlation:** No apparent movements in any direction of a variable upon changing other variableâ€™s values is known as no correlation.

**How to Calculate Correlation between Two Stocks in Excel: 3 Easy Methods**

Follow any below-described method to calculate the correlation between two stocks in Excel.

**Method 1: Using CORREL Function to Calculate Correlation between Two Stocks**

Excel offers the **CORREL** **function** which takes arrays or ranges as its arguments. As a result, users can assign arrays or ranges to calculate correlations between them.

**Step 1:** Insert the following formula in any blank cell (i.e., **F5**).

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

The formula takes **C5:C14** as **array1** and **D5:D14** **array2** argument.

**Step 2:** Press** ENTER** to display the correlation amount between the stock prices.

For convenience, the **stock prices** are in a currency amount, you can use percentages to depict changes in them. Irrespective of data types, the** CORREL function** calculates the correlation between them.

**Read More: **How to Do Correlation and Regression Analysis in Excel

**Method 2: Calculating Correlation between Two Stocks Using Toolpak Add-in**

Excelâ€™s **Toolpak Add-in** provides multiple **Analysis Tools Correlation** is one of them. Users can perform a **Correlation Analysis** using the **Toolpak Add-in**. But users need to activate the **Toolpak Add-in** first.

**Step 1:** Go to Worksheetâ€™s **File** > **Options**.

**Step 2:** Excel brings the **Options** window. From the window, Select **Add-ins** (right side of the window) > Choose **Excel Add-ins** in the **Manage** command box then click on **Go**.

**Step 3:** The **Add-ins** dialog box opens up. Tick the **Analysis Toolpak** under **Available Add-ins**. At last, click on **OK**.

**Step 4:** Now, return to the worksheet, then move to the **Data** tab > Select **Data Analysis**.

**Step 5:** Instantly, Excel opens up the **Data Analysis** dialog box. In the dialog box, Select **Correlation** as **Data Analysis Tools**. After that, click on **OK**.

**Step 6:** The **Correlation** command box appears. Assign respective data in the boxes such as the entire range (i.e., **C4:D14**) as** Input Range**, **Columns** as **Grouped By**, and **F5** as **Output Range**. Finally, click on **OK**.

ðŸ”¼ The **Toolpak Add-in** calculates the correlation among the assigned range and returns the value as shown in the below image.

**Read More:** How to Calculate Cross Correlation in Excel

**Method 3: Using Conventional Statistics Formula to Calculate Correlation between Two Stocks**

The **Correlation** or **Correlation Coefficient** has its own generic formula in **Statistics**. The formula consists of two portions one is **Each Value** (**X** or** Y**) (here it is **P**) **â€“ Mean Value** (**X** or** Y**) (here it is **Q**) the other one is **Square Root** of **Squared Summation** of **Each Value** (**X** or** Y**) (here it is **P**)** â€“ Mean Value** (**X** or** Y**). So, the formula becomes,

`Correlation or Correlation Coefficient=âˆ‘(X(i)- Mean(X))*(Y(i)-Mean(Y))/âˆš âˆ‘(X(i)-Mean(X))2* âˆ‘(Y(i)-Mean(Y))2`

**ðŸ”„ Setting Up Data**

Before commencing any calculation, you have to generate the required entities from the given data.

**Geeting the Means:Â **

`Mean Calculation= Sum of the X or Y's/Number of the Data Rows`

**Subtract Values: **

`P=X(i)- Mean(X)= X values- Mean(X)`

` Q=Y(i)- Mean(Y)= Y values- Mean(Y)`

**Product of the Subtraction: **

`P*Q=X(i)- Mean(X)*Y(i)- Mean(Y)`

**Sum of the Product: **

`âˆ‘(P*Q)=âˆ‘(X(i)- Mean(X)* Y(i)- Mean(Y))`

**Square of the Subtraction: **

`P2 or Q2=(X(i)-Mean(X))2Â or (Y(i)-Mean(Y))2`

**Product of the Squared Subtraction Sum: **

`âˆ‘P2*âˆ‘Q2=âˆ‘(X(i)-Mean(X))2* âˆ‘(Y(i)-Mean(Y))2`

Conduct the above operation to prepare the required entities mentioned in the formula. After wrapping up the data settings, the dataset may look like the below depiction.

^{}

**Step 1:** As mentioned in the formula,** Correlation** is the ratio between the **Sum of the Products of the Subtraction Value âˆ‘(P*Q) **and the

**Square Root Product of the Squared Summed the Subtraction Value**. Execute the formula

*âˆ‘P2*âˆ‘Q2*`Correlation or Correlation Coefficient=âˆ‘(P*Q)/âˆšâˆ‘P2*âˆ‘Q2`

`=G15/SQRT(H16)`

In the formula, **G15** is the **Sum of the Products of the Subtraction Value** and the **SQRT function** square root of the **Product of the Squared Summed the Subtraction Value**.

**Step 2:** Hit** ENTER** to display the **Correlation** value that appears as depicted in the below screenshot.

â§ Notice that the **Correlation** value is the same throughout the iterations. This happens because Excel does the same calculation in backends making functions like **CORREL** or **Add-ins** convenient.

**Download Excel Workbook**

**Conclusion**

In this article, we demonstrate the **CORREL** function, **Toolpak Add-in**, and conventional **Statistics Formula** to calculate **Correlation** between two stocks in Excel. Hope you find these above-mentioned methods compatible with your instances. Comment, if you have further inquiries or have anything to add.

## Related Articles

- How to Make a Correlation Scatter Plot in Excel
- Find Correlation Between Two Variables in Excel
- How to Make Correlation Graph in Excel
- How to Make a Correlation Table in Excel
- How to Make a Correlation Matrix in Excel
- How to Interpret Correlation Table in Excel
- How to Make Correlation Heatmap in Excel
- How to Calculate Partial Correlation in Excel
- How to Calculate Autocorrelation in Excel