Using Excel to Calculate and Graph Correlation Data

Calculating Pearson’s r Correlation Coefficient with Excel

Screen capture of an Excel spreadsheet. 1. Enter the paired scores for each subject on an Excel spreadsheet. {An arrow pointing to column A} 2. After the data have been entered, place the curser in an empty cell where you wish to have the correlation coefficient (Pearson’s r) appear and click the mouse button. {An arrow pointing to cell B13 which has an = in it} 3. Select Insert Function (in red text) {An arrow pointing to selection below the top menu} (fx) from the FORMULAS (in red text) {An arrow pointing to FORMULAS tab on the top menu} tab. 4. A dialog box will appear. Select Statistical, (in red text) {An arrow pointing to the Statistical selection on the Or select a category drop down menu in the Insert Function dialog box} select CORREL, (in red text) {An arrow pointing to the CORREL selection on the Select a function drop down menu in the Insert Function dialog box} and click OK. (in red text) {An arrow pointing to the OK button in the Insert Function dialog box}

Screen capture of an Excel spreadsheet. 5. Enter the cell range for your first variable in the ARRAY 1 (Bold) {An arrow pointing to the Array 1 in the Function Arguments dialog box} box. For example, if the data for your first variable were in column A from row 1 to 12, you would enter A1:A12. 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 it. 6. Click in the Array 2 (Bold) {An arrow pointing to the Array 2 in the Function Arguments dialog box} box and do the same for Array 2. 7. Once you have entered the range for both variables, click OK (Bold) {An arrow pointing to the OK button in the Function Arguments dialog box} at the bottom of the dialog box. The correlation coefficient (r) will appear in the cell you selected.

Creating a Scatterplot of Correlation Data with Excel

Screen capture of an Excel spreadsheet. 1. Highlight your data. 2. Fron the INSERT (in red text) {An arrow pointing to the INSERT tab at the top menu} tab, select the scatterplot icon (in red text) {An arrow pointing to Scatter drop down menu} and select the top, left icon. (in red text) {An arrow pointing to scatter plot selection icon under Scatter drop down menu} A scatterplot (in red text) {An arrow pointing to scatter plot chart} should appear on your spreadsheet. You can copy the scatterplot chart and paste it into other documents.

Screen capture of an Excel spreadsheet. 4. You can change the minimum and maximum values of the x-axis and the y-axis by right clicking on the axis {An arrow pointing to the x-axis of the chart} you wish to change. 5. Select Format Axis…. (in red text) {An arrow pointing to the Format Axis… in the drop down menu} 6. Enter the Minimum (in red text) {An arrow pointing to the Minimum box in the Bounds selection of the Format Axis drop down list} and Maximum (in red text) {An arrow pointing to the Maximum box in the Bounds selection of the Format Axis drop down list} values you wish to display in the dialog box that appears.