**How to** **Calculate ****the ****Correlation Coefficient**** in Excel**

**How to**

**Calculate**

**the**

**Correlation Coefficient**

**in Excel**

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

The correlation coefficient helps you determine the **relationship **between different variables.

Looking at the actual formula of the **Pearson product-moment correlation coefficient **would probably give you a headache.

Fortunately, there’s a function in Excel called **‘CORREL’ **which returns the correlation coefficient between two variables.

And if you’re comparing more than two variables, Excel also has a **‘Correlation’ tool **inside the **‘Analysis ToolPak’**.

In this tutorial, we’ll show you how you can easily calculate the correlation coefficient using Excel.

Let’s get started! 😊

**What is the ‘CORREL’ function?**

The ‘CORREL’ function is an **Excel statistical function **that *calculates the Pearson product-moment correlation coefficient *of two sets of variables.

Unlike its formula, the Excel function has a simple syntax:

**=CORREL(array1, array2)**

Parameters:

**‘array1’**– required; cell range reference**‘array2’**– required; cell range reference

**How to interpret the correlation coefficient**

The value of the correlation coefficient is between **-1 **and **+1**.

**Closer to +1**: A coefficient of 1 represents a*perfect positive correlation*. The closer the coefficient is to 1, the higher the correlation.*As the independent variable increases, the other variable increases as well.***Closer to -1**: A coefficient of -1 represents a*perfect negative correlation*. The closer the coefficient is to -1, the lower the correlation.*As the independent variable increases, the other variable decreases.***0**: A correlation coefficient near 0 indicates*no correlation*.

**How to use the ‘CORREL’ function**

As discussed earlier, the ‘CORREL’ function calculates the correlation coefficient of **2 sets **of variables.

You can’t use the ‘CORREL’ function to determine the relationship of more than 2 variables.

For this example, we’ll be using this data set with the ‘X Variables’ and ‘Y Variables’:

To determine the correlation coefficient between these 2 sets, all we have to do is **supply the parameters **with the correct cell references:

**=CORREL(A2:A11,B2:B11)**

A correlation coefficient of **0.998829 **means there’s a **strong positive correlation **between the two sets. As the ‘X Variables’ increase, the ‘Y Variables’ increases also.

**How to use the ‘Correlation’ tool in the Analysis ToolPak**

The ‘Correlation’ tool inside the Analysis ToolPak is what you use if you need to calculate the correlation coefficient of **more than 2 variable sets**.

For this example, we’ll be using a similar data set with the one above with the addition of ‘Z Variables’.

For you to be able to use the ‘Correlation’ tool, you need to load the Excel Analysis ToolPak.

If you’re not sure how to load the toolpak, here’s a summary of how to load the Analysis ToolPak:

**Step 1**: Click ‘File’ from the tab list**Step 2**: Click ‘Options’ on the bottom of the left-hand sidebar**Step 3**: Click ‘Add-ins’ on the left sidebar of the window**Step 4**: Click the ‘Go’ box to manage the add-ins**Step 5**: Check the ‘Analysis ToolPak’ and click ‘OK’

Feel free to visit the data analysis tutorial to know more about it.

To know if the toolpak is already loaded on your Excel, **click the ***‘Data’ ***tab **from the tab list.

Then, check if there’s an *‘Analysis’ ***group **on the right-most side of the ribbon with the *‘Data Analysis’*** icon**.

Once you see it, click it, **choose **** ‘Correlation’ **from the list, and

**click**

**.**

*‘OK’*After that, you’ll see the correlation dialogue box.

The options in the dialogue box are pretty easy to understand:

**‘Input’**: Contains all the options related to the input**‘Input Range’**: The cell ranges with the data values on it including the labels in the first row**‘Grouped By’**: Choose if the values are grouped in columns or in rows**‘Labels in First Row’**: Check this if you included the labels in the first row on the ‘Input Range’**‘Output options’**: Contains all the options related to the output**‘Output Range’**: Output the correlation coefficient results on the same worksheet on a specified range**‘New Worksheet Ply’**: Output the correlation coefficient results on another worksheet**‘New Workbook’**: Output the correlation coefficient results on another workbook

For our example, let’s set our options like the following:

**‘Input Range’**: $A$1:$C$11**‘Grouped By’**: Columns**‘Labels in First Row’**: Check**‘Output Range’**: $E$2

After clicking **‘OK’**, you’ll immediately see the result table:

From the table, we’re able to determine the following correlation coefficient:

- ‘X Variables’ and ‘Y Variables’:
**0.99882903 (Positive)** - ‘X Variables’ and ‘Z Variables’:
**0.999146601 (Positive)** - ‘Y Variables’ and ‘Z Variables’:
**0.997661072 (Positive)**

**Wrapping things up…**

Computing the correlation coefficient used to be hard especially when done on paper. With Excel’s help, you can quickly whip up the results within seconds!

It wouldn’t matter if you have 2 sets or 20 sets. The ‘Correlation’ tool will solve it for you faster than the time it took you to read this sentence. How cool is that? 😎