How to Calculate Correlation Coefficient in Excel? 2 Useful Ways
(Note: This guide on how to calculate Correlation Coefficient in Excel is suitable for all Excel versions including Office 365)
You can use Microsoft Excel for a variety of purposes. It is an efficient tool for storing and retrieving data. In addition to data handling, you can perform a variety of statistical, mathematical, business, and engineering operations.
One such operation is calculating and establishing a relationship between two variables or data sets. This is called the “Correlation Coefficient”.
In this article, you will learn how to calculate correlation coefficient in Excel in 2 easy ways.
You’ll Learn:
Related Reads:
How to Remove Comma in Excel? 5 Easy Ways
How to Calculate Standard Deviation in Excel? 2 Simple Ways
How to Insert a Hyperlink in Excel? 3 Easy Ways
What is the Correlation Coefficient?
Before we see how to calculate correlation coefficient in Excel, let us see what it is and where we use them.
Correlation is a term in engineering and statistics which denotes the relationship between two or more data by a common value.
- When the correlation coefficient is positive (0 to 1), both the data are directly proportional to each other. When one of the data sets increases, the corresponding data set also increases.
- When the correlation coefficient is negative (-1 to 0), both the data series are inversely proportional. When one of the data sets increases, the corresponding data set decreases.
- Depending on the rate of change in the variables, the correlation coefficient can range between -1 to +1.
How to Calculate the Correlation Coefficient in Excel?
Consider an example where you have data on students from grade 1 to grade 10. The data is separated into three sections. One column represents the number of boys, another column the number of girls, and finally, the total number of students in the class. You can see that the data sometimes increase and decrease in proportion to each other.
Let us see the 2 methods to find the correlation coefficient in Excel.
Using the CORREL Function
One method to calculate the correlation coefficient between two data series is by using the CORREL function.
Syntax
The CORREL function takes two arguments.
=CORREL(array1,array2)
Where array1 and array2 represent the cell range that contains the data between which you want to establish a relationship.
Use the Function by Entering the Formula
- To find the correlation coefficient, first, select a destination cell.
- Enter the formula in the destination cell. In this case, enter =CORREL(B6:B15,C6:C15).
- Press Enter.
This gives you the correlation coefficient of the two arrays of data. You can see that the correlation value 0.86177 is between 0 and 1. This means the correlation is positive.
Use the Function by Selecting the Formula
Another way to use the CORREL function is by selecting it from the Formulas.
- First, select a destination cell.
- To select the formula, navigate to the Formulas option in the menu bar.
- Under the Function Library section, click on the dropdown from More Functions. From the Statistical section, click on the CORREL function.
- This in turn opens the Function Arguments dialog box.
- Select the data or enter the data range in the Array text box.
- You can already see the output as a preview in the dialog box.
- Click OK to see the output in the selected cell.
This gives you the correlation coefficient in the destination cell.
Suggested Reads:
How to Use CONVERT Function in Excel? A Step-by-Step Guide
How to Use the Excel TREND Function? A Step-by-Step Guide
How to Calculate SUBTOTAL in Excel? 2 Useful Methods
Using the Data Analysis Toolpak
The above method of using the CORREL formula is very effective when you have two data series and want to establish a relationship between them. But, when you have multiple variables or data sets and want to calculate the correlation coefficient, this method might be futile.
In such cases, you can use the Data Analysis Toolpak option to find the relationship between multiple data series.
Consider an example, where you have three data series instead of two. In addition to the number of boys and girls, let us take the total number of students into account to calculate the correlation coefficient.
Enable the Data Analysis Toolpak
The Data Analysis Toolpak in Excel is disabled by default and needs to be enabled as an additional add-in.
- To enable the Data Analysis Toolpak, click on File from the Menu bar.
- Now, click on Option from the categories pane.
- This opens the Excel Options dialog box.
- In the dialog box, click on the Add-ins section.
- From the Manage dropdown, select Excel Add-ins and click on Go.
- This in turn opens the Add-ins dialog box. Check the checkbox for Analysis ToolPak from the Add-ins available section.
- Click OK.
- This enables the Data Analysis Toolpak which can be found under the Data ribbon under the Analysis section.
Calculate Correlation Coefficient using Data Analysis Toolpak
Let us now see how to calculate the correlation coefficient of the above given three sets of data.
- First, navigate to Data. Under the Analysis section, click on the Data Analysis button.
- This opens the Data Analysis dialog box.
- Under the Analysis Tools section, select Correlation and click OK.
- This opens the Correlation dialog box.
- In the Input section, enter the Input Range. You can either enter the range manually or select the cells to populate the range.
Since we have grouped the data into columns, select the Columns radio button from the grouped by section
- We have also selected the data including headers, so check the checkbox for Labels in first row. This will give data cues when you calculate the correlation coefficient.
- Finally, choose the destination where you want to display the output. You can either choose a different cell, choose a new worksheet, or even a new workbook. In this case, let us choose cell A20 to display the output.
- Click OK.
- This instantly gives you the correlation coefficient for the selected data series in the destination cell.
Note: The output is static. That is, the table does not change when you change the data for any of the series. You will have to repeat the following steps to arrive at the correlation coefficient for the new data.
Also Read:
How to Count Cells with Text in Excel? 3 Different Use Cases
How to Calculate Percentile in Excel? 3 Useful Formulas
How to Fix the Excel NAME Error? 3 Simple Tips
Frequently Asked Questions
What is the formula to calculate the correlation coefficient in Excel?
Choose a destination cell and enter the formula =CORREL(array1,array2), where array1 and array2 represent the cells containing the data.
Where can I find the Data Analysis Toolpak?
The Data Analysis Toolpak in Excel is not enabled by default. To enable the Data Analysis Toolpak, go to File>Options and click on Add-ins. In the Manage section, click Excel Add-ins and click on Go. In the Add-ins dialog box, click on Analysis Toolpak and click OK. This enables the Data Analysis Toolpak which can be found in the Data tab.
What can be the value of the correlation coefficient?
The Correlation Coefficient can occur between -1 to 1. When one data increases in proportion to the other, the correlation coefficient will be positive (0 to 1). If one data increases whereas the other decreases, the correlation coefficient will be negative (-1 to 0).
Closing Thoughts
Calculating the correlation coefficient between different sets of data helps in determining the relationship between two or more data sets.
In this article, we saw how to calculate the correlation coefficient in Excel in 2 proven ways. You can use the CORREL formula to calculate the coefficient when there are two data variables. When there are more than two data variables, use the Data Analysis Toolpak method to calculate the correlation coefficient.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
