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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

xy dataset for the correl function

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’
loading the analysis toolpak in 5 clicks

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.

data 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.

data analysis button on analysis category

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

correlation option from the data analysis window

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

correlation dialogue box with all the available options

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
correlation dialogue box filled-out with appropriate parameters

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

correlation results from xyz dataset using the correlation tool from the analysis toolpak

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? 😎

Kasper Langmann, Co-founder of Spreadsheeto