Excel is a powerful statistical tool. It can be used to calculate various statistics problems. Finding a correlation between two data series is a common statistical calculation that many statistical analysts come across. To solve these calculations, various methods are used. Using Excel to calculate correlation problems has proven to be one of the simplest and shorter methods. However, in excel two methods can be used to calculate the correlation of two data series.
Using the CORREL Formula
CORREL is an in-built function that is used in calculating Correlation problems. When using this formula, you don't have to install an add-ins feature. Here are the steps to use this function.
1. Open your Excel application and enter your dataset on the empty cells.
2. Since you are calculating the Correlation coefficient, you should have at least two data series.
3. Then, click another empty cell within the same screen and name this cell as "Correlation results."
4. Click on the cell next to the Correlation results cell and enter the CORREL formula as shown below;
=CORREL(B2:B5, C2:C5)
Note: B2:B5 means all the data from cell 2 to cell 5 in column B.
C2:C5 means all data from cell 2 to cell 5 in column C.
5. Then, hit the Enter button and the result will be stored on the cell that had the formula.
Using the Data Analysis Toolpak
Data analysis toolpak is an add-ins feature that is used to solve statistical problems.
Steps to use Toolpak
1. To use this feature, you firstly need to activate it. follow these steps to activate your Data Analysis Toolpak;
- On the main screen, click on the File tab, if you are using the latest versions of Excel. If otherwise, click on the menu button found on the top left side of the screen.
- Then, click on "Excel Options."
- On the Excel Options dialogue box, click the "Add-ins" button.
- At the bottom there's a Manage section, click the "Go" button.
- Finally, check the "Analysis Toolpak" checkbox and click the "Ok" button.
- Now, you can comfortably use this feature.
Calculating Correlation Coefficient using Data Analysis ToolPak
Steps;
1. Enter your dataset on the empty cells.
2. Then, click on the Data Tab.
3. Within the Analysis section, click on the Data Analysis button.
4. On clicking, a Data Analysis dialogue box pops out. choose the correlation option.
5. Within the Input range section, Highlight all cells that hold the data you wish to calculate its Correlation Coefficient- including the headers.
6. In the Group By section, toggle on the "Columns" button.
7. Check the "Labels in First Row" to make sure the resulting data would have the same headers.
8. In the Output Options section, choose where you want the result of your calculation to appear. Then click the "OK" button.
9. By doing so, the Excel feature will calculate the Correlation Coefficient and display it on the selected screen.