Microsoft Excel for statistics > Correlation Coefficient Excel 2007

How to Compute the Pearson Correlation Coefficient Excel 2007

The correlation coefficient is a measure of how well the data in your sample is linearly related. Finding by hand will take you over thirty minutes (even if your math skills are strong!). Microsoft has a built in function to find the correlation coefficient formula, which will return a correlation between -1 and 1. The closer to 1 or -1 your value is, the higher the linear correlation: 1 represents perfect linear correlation while -1 represents perfect negative correlation.

Correlation Coefficient Excel 2007: Steps

Watch the video or read the steps below:

Step 1: Type your data into two columns in Excel. For example, type your “x” data into column A and your “y” data into column B.

Step 2: Select any empty cell.

Step 3: Click the function button on the ribbon.



Step 4: Type “correlation” into the ‘Search for a function’ box.

Step 5: Click “Go.” CORREL will be highlighted.

Step 6: Click “OK.”

Step 7: Type the location of your data into the “Array 1” and “Array 2” boxes. For this example, type “A2:A10” into the Array 1 box and then type “B2:B10” into the Array 2 box.



Step 8: Click “OK.” The result will appear in the cell you selected in Step 2. For this particular data set, the correlation coefficient(r) is -0.1316.

Correlation Coefficient Excel 2007: What does r mean

R is the strength of the linear relationship.

• A 0 means there is no relationship at all.
• -1 means there is a very strong negative linear relationship.
• +1 means there is a very strong positive linear relationship.

Caution: The results for this test can be misleading unless you have made a scatter plot first to ensure your data roughly fits a straight line. The correlation coefficient Excel 2007 will always return a value, even if your data is something other than linear (i.e. exponential).

That’s it!

Subscribe to our Youtube Channel for more Excel tips and stats help.