How to calculate correlation coefficient
Correlation (Pearson's r ) with Excel
1. Enter the paired scores for each subject on the Excel spreadsheet (see the example below). After the data has been entered, place the cursor where you wish to have the correlation coefficient (Pearson's r) appear and click the mouse button. Now move the cursor to the Function Wizard (fx ) button and click on it.
2. A dialog box will appear. Click on Statistical from the left section of the box and CORREL on the right section. After you have made those two selections, click on Next> at the bottom of the dialog box.
3. Enter the cell range for your first variable in the Array 1 box. For example, if the data for your first variable were in column A from row 2 to 10, you would enter A2:A10. Instead of typing the range, you can also move the cursor to the beginning of the set of scores you wish to use and highlight them. Do the same for Array 2. Once you have entered the
range for both variables, click on Finish at the bottom of the dialog box.
4. The correlation for the two variables will appear in the cell you selected.
Excel can also be used to create a Scatterplot.
1. Highlight your data.
2. Move your cursor to the Chart Wizard on the menu bar (multicolor bar graph icon) and click on it.
3. Select XY (Scatter) from the chart type. Click on the traditional scatterplot chart-sub-type and click on Finish.
4. A scatterplot should appear on your spreadsheet. If you wish, you can adjust the x and y scales by double clicking on them.
Using the Latest Version Microsoft Excel
The data should be entered in two columns as pairs of scores. Once the data are entered (see background of the figure below), highlight the data, select Scatter from the Insert tab . In the popup box select the scatterplot icon. The scatterplot will appear on the spreadsheet.Source: www.gifted.uconn.edu