Calculating Correlation with the Excel Spreadsheet Program

The Excel Spreadsheet Program can be used to calculate the Pearson r (with data at the interval or ratio level) or the Spearman r (with data at the ordinal level).

Calculating the Pearson Product Momement Correlation Coefficient with the Excel Spreadsheet Program

To calculate the Pearson Product Moment Correlation Coefficient with the Excel Spreadsheet Program, open a new worksheet and create a table with the data that will be used to create the scatterplot. We will use the data from Table 3 - Reading and Spelling Scores for 10 Students - which may look like the following after being entered into an Excel worksheet.

Find the correlation between reading and spelling as follows:

Click on cell A14 and enter the following label - Pearson r =
Click on cell B14 and then click on the Paste Function icon
In the Paste Function window select Statistical under Function category:, then scroll down and select PEARSON under Function name:
Click OK.
Drag the Pearson window, which appears, so that it is not covering your Table 3 data.
Click and select cells B3 through B12. The Array 1 box should now show the range of cells for reading (B3:B12).
Select the Array 2 box by clicking in it, then click and select cells C3 through C12. The Array 2 box should now show the range of cells for spelling (C3:C12).
Click OK and note that -0.3611812 appears in cell B14.
Format the quantity in cell B14 by clicking on it and then selecting Cells... from the Format menu. Click on the Number tab and select Number under Category: Use the selection arrow to set Decimal places: at 2 and click OK.

Your completed worksheet should appear as below with - 0.36 as the correlation coefficient.

As an activity you may wish to use the spreadsheet to calculate the correlation coefficient for the data in tables 1 and 2.

Calculating the Spearman Rank Order Correlation Coefficient with the Excel Spreadsheet Program

To calculate the Spearman Rank Order Correlation Coefficient with the Excel Spreadsheet Program, open a new worksheet and create a table with the data that will be used to create the scatterplot. We will use the same data we used to explain the Spearman r, that is the ratings of two judges for seven subject's art projects - which may look like the following after being entered into an Excel worksheet.

Proceed as follows to calculate the Spearman Rank Order Correaltion Coefficient.

Click on cell D1 and enter the label D.
Click on cell E1 and enter the label D2 (for D squared).
Click on cell D2, and enter the formula =B2-C2 and press return.
Click on cell D2 and select down through cell D8. Select Fill from the Edit menu and slide to the right and select Down. The formula in cell D2 will be copied into cells D3 through D8.
Click in cell E2, enter the formula =D2*D2 and press return.
Click on cell E2 and select down through cell E8. Select Fill from the Edit menu and slide to the right and select Down. The formula in cell E2 will be copied into cells E3 through E8.
Enter the label Sum in cell A9.
Click on cell E9, enter the formula =SUM(E2:E8) and press return. The sum of the D squares (10) will appear in cell E9.
Enter the label Spearman r = in cell A10.

Since Excel does not have a built in function to calculate the Spearman r we will enter the formula ourselves. We will use the following formula which is an adaptation of the Spearman Rho formula for entry into a cell in the spreadsheet.

As we enter the formula we will substitute cell E9 for summation D squared and 7 for N.
Select cell B10 and enter the following formula: =1-(6*E9/(7*(7*7-1))) and press return. The Spearman r should appear in cell B10 (0.82142857).
Format the result by clicking on cell B10 and selecting Cells... from the Format menu. Click on the Number tab and select Number under Category:. Select the arrows to the right of the Decimal places: box until 2 appears in the box.
Click OK. Notice that the answer, 0.82, is formatted to 2 decimal places.

Your spreadsheet should now appear as follows: