Finding the correlation coefficient in Excel is a fundamental skill for anyone working with data analysis, statistics, or business insights. It helps you understand the relationship between two sets of data, indicating whether they are positively or negatively correlated. In this article, you will learn the multiple methods to find the correlation coefficient in Excel, including using the CORREL function, Data Analysis ToolPak, and PEARSON function. These methods apply to Excel 2010, 2013, 2016, 2019, and Office 365. 

What is Correlation in Excel

Correlation is a statistical measure that describes the relationship between two or more variables. It indicates how one variable changes when another variable changes. Correlation can help identify patterns or associations in data and is used widely in fields such as finance, science, and social studies.

Types of Correlation

1. Positive Correlation: When one variable increases, the other variable also increases. For example, as height increases, weight tends to increase.
2. Negative Correlation: When one variable increases, the other decreases. For example, as the price of a product increases, the demand for it might decrease.
3. No Correlation: There is no relationship between the variables. For example, the amount of rainfall and a person’s age are unrelated.

What is Correlation Coefficient in Excel?

The correlation coefficient is a numerical value ranging from -1 to +1 that quantifies the strength and direction of the relationship between variables:

• +1 indicates a perfect positive correlation.
• -1 indicates a perfect negative correlation.
• 0 indicates no correlation.

Correlation helps in understanding relationships in data, making it a valuable tool for data analysis and decision-making.

If the correlation coefficient is 0, the bivariate data are not correlated with each other.

If the correlation coefficient is -1 or +1, the bivariate data are strongly correlated with each other.

r=-1 denotes strong negative relationship and r=1 denotes strong positive relationship.

How to Calculate Correlation Coefficient in Spreadsheets?

In general, if the correlation coefficient is close to -1 or +1 then we can say that the bivariate data are strongly correlated to each other. 

The correlation coefficient is calculated using Pearson’s Correlation Coefficient which is given by :

Correlation Coefficient

It shows that,

• r: Correlation coefficient.
• $$x_i : Values of the variable x.
• y_i : Values of the variable y.
• n: Number of samples taken in the data set.
• Numerator: Covariance of x and y.
• Denominator: Product of Standard Deviation of x and Standard Deviation of y. 

Different Methods to Calculate the Correlation Coefficient in Excel with Example

There are several methods for calculating the correlation coefficient in Excel. Let’s explore them step by step:

• Method 1: Using CORREL function
• Method 2: Using Data Analysis
• Method 3: Using PEARSON Function 

Method 1: Using CORREL Function

In this article, we are going to see how to find correlation coefficients in Excel. Consider the following data set :

In Excel to find the correlation coefficient use the formula :

=CORREL(array1,array2) array1 : array of variable x array2: array of variable y To insert array1 and array2 just select the cell range for both.

1. Let’s find the correlation coefficient for the variables X and Y1.

Correlation coefficient of x and y1

array1 : Set of values of X. The cell range is from A2 to A6.

array2 : Set of values of Y1. The cell range is from B2 to B6.

Similarly, you can find the correlation coefficients for (X, Y2) and (X, Y3) using the Excel formula. Finally, the correlation coefficients are as follows :

From the above table we can infer that :

X and Y1 have negative correlation coefficient. 

X and Y2 have positive correlation coefficient.

 X and Y3 are not correlated as the correlation coefficient is almost zero.

Example: Now, let’s proceed to the further two methods using a new data set. Consider the following data set :

Method 2: Using Data Analysis

We can also analyze the given dataset and calculate the correlation coefficient: To do so follow the below steps:

Step 1: First you need to enable Data Analysis ToolPak in Excel to enable

1. Go to the File tab in the top left corner of the Excel window and choose Options.
2. The Excel Options dialog box opens. Now go to the Add-Ins option and in the Manage select Excel Add-ins from the drop down.
3. Click on the Go button.
4. The Add-ins dialog box opens. In this check the option Analysis ToolPak.
5. Click OK!

Data Analysis tab added

Step 2: Click on the Data tab and select Data Analysis.

Go to Data tab in the menu bar and click on Data Analysis.

Step 3: Choose Correlation from the list and click OK

Step 4: The Correlation menu will appear.

Step 5: Provide the Input Range

Select the range of your data for both X and Y columns.

Step 6: Select the Output Range

Select the range of your data for both X and Y columns. By default, the output will appear in the new Excel sheet in case you don’t provide any Output Range.

Step 7: Click OK

Step 8: Preview Data Analysis Table

Here, you can see the correlation coefficient between X and Y1 in the analysis table.

Similarly, you can find correlation coefficients of XY2 and that of XY3. Finally, all the correlation coefficients are :

Method 3: Using PEARSON Function 

The PEARSON function is an alternative way to find the correlation coefficient in Excel.

It is exactly similar to the CORREL function which we have discussed in the above section. The syntax for the PEARSON function is :

=PEARSON(array1,array2) array1 : array of variable x array2: array of variable y To insert array1 and array2 just select the cell range for both.

Let’s find the correlation coefficient for X and Y1 in the data set of Example 2 using the PEARSON function.

The formula will return the correlation coefficient of X and Y1. Similarly, you can do for others.  The final correlation coefficients are :

Computing Correlation Coefficient in Excel

To find the correlation coefficient using Excel, you can use the built-in CORREL function:

Step 1: Enter your data in two columns.

Step 2: Use the formula =CORREL(range1, range2) to calculate the correlation coefficient.

For example, if your data is in columns A and B (from row 1 to 5), the formula would be:

=CORREL(A1:A5, B1:B5)

Example: Calculating Correlation Coefficient in Excel 2016

Consider a dataset of sales and advertising expenses for a company. You can use any of the methods above to find the correlation coefficient between the two variables, indicating how strongly related sales are to advertising.

What is Excel Correlation Matrix?

A correlation matrix shows the correlation coefficients between multiple variables in a dataset. Each cell in the matrix represents the correlation between two variables.

How to Create Correlation Matrix

Follow the Below steps to Create Correlation Matrix:

Step 1: Prepare Your Data

Ensure your data is organized in a table format, with each column representing a different variable and the first row containing the variable names.

Step 2: Enable Data Analysis ToolPak (if not already enabled)

• Go to the File tab, select Options, and choose Add-ins.
• In the Manage dropdown, select Excel Add-ins and click Go.
• Check Analysis ToolPak and click OK.

Step 3: Open the Data Analysis Tool

• Go to the Data tab in Excel.
• Click on Data Analysis, then select Correlation and click OK.

Step 4: Provide the Input Range

• Select the range of your dataset, including the headers (e.g., A1:C10 for three variables across 10 rows).
• Choose Columns as the Group By option if your data is arranged in columns.

Step 5: Set the Output Range

Choose where you want the correlation matrix to appear (e.g., select a cell or a new worksheet).

Step 6: Click OK

The correlation matrix will appear, showing correlation coefficients between each pair of variables.

Correlation Coefficient in Excel: How to Troubleshoot Common Issues

• Formatting Errors: Ensure your data is clean and formatted correctly. Text values or blank cells can cause errors.
• Incorrect Cell Ranges: Double-check the ranges used in your formulas.
• Missing ToolPak: If the Data Analysis ToolPak is not visible, make sure it’s installed and enabled.

Practical Uses of Correlation Coefficients

• Finance: Analyze the correlation between different stocks or economic indicators.
• Marketing: Understand the relationship between marketing campaigns and sales growth.
• Science: Study the relationship between different variables in research data.

Conclusion

Finding the correlation coefficient in Excel is straightforward with built-in functions like CORREL and PEARSON, or by using the Data Analysis ToolPak. Understanding the correlation between data sets can help you make better business decisions and gain insights into trends. Excel makes this process simple and efficient across different versions, including Excel 2010, 2013, 2016, 2019, and Office 365.

FAQs – Correlation Coefficient in Excel Graph

How to Compute Correlation Coefficient in Excel?

There are three ways to Compute Correlation in excel as follows:

• Method 1: Using CORREL Function
• Method 2: Using Data Analysis ToolPak
• Method 3: Using PEARSON Function

How can I calculate correlation coefficient?

Here is the way to calculate the correlation coefficient in Excel:

CORREL function: This is the simplest and recommended method. Enter =CORREL(array1, array2) in a cell, replacing array1 and array2 with the ranges of your data sets. Excel will return the correlation coefficient, a value between -1 and 1 that represents the strength and direction of the linear relationship between your variables.

How to find the r value in Excel?

The r value in Excel is directly calculated using the CORREL function. It represents the correlation coefficient, a value between -1 and 1 indicating the strength and direction of a linear relationship between two data sets.

How to calculate covariance and correlation coefficient in Excel?

In Excel, the CORREL function is your one-stop shop for both covariance and correlation coefficient:

• Enter =CORREL(array1, array2) in a cell. Replace array1 and array2 with the cell ranges of your data sets.
• Excel will return the correlation coefficient (r value), which reflects the strength and direction of the linear relationship.

Covariance is also calculated by CORREL, but it’s not directly displayed. It’s an intermediate step used internally to calculate the r value. If you specifically need the covariance value, you’ll need to use separate functions like COVARIANCE.P or COVARIANCE.S.