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

Scatterplot

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.

The minimum and maximum values of the x- and y-axis can be changed by clicking on the scatterplot chart, selecting Axes from the Layout tab, selecting Primary Horizontal Axis à or Primary Vertical Axis à, and selecting More Primary Vertical (Horizontal) Options…

A minimum and maximum value for the axis can be selected from the dialog box that appears

Calculating Pearson’s r

The Excel spreadsheet can be used to calculate the actual strength of the relationship (Pearson’s correlation coefficient). With the data entered on an Excel spreadsheet, place the cursor in a blank cell where you wish to have the correlation coefficient (Pearson's r) appear and click the mouse button. Select the Formula tab and click on Insert Function on that tab. A dialog box will appear. Select Statistical from Or select a category (middle textbox) and CORREL from the bottom textbox

After you have made those two selections, select OK and a new dialog box will appear (see Figure 10). Enter the cell range for the first variable in the Array 1 textbox. For example, if the data for the first variable were in column A from row 2 to 22, you would enter A2:A22. 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 OK at the bottom of the dialog box. The correlation for the two variables will appear in the cell youi selected.

If You Do Not Have Excel, You Can Use Google Spreadsheet

Google Spreadsheet provides a variety of graphing options, including scatterplot graphs. Once Google Spreadsheet is open (http://docs.google.com), enter the correlation data in the same manner as was described above for the Excel spreadsheet. To create a scatterplot in Google, highlight the data and select Chart from the Insert pull-down menu.

Select the type of chart desired, in this case scatterplot, and select Save Chart.

The scatterplot will appear on the spreadsheet. Once the chart is created, you can easily save the chart as a png file and insert it into other documents.

Calculating the correlation coefficient with Google spreadsheet is similar to the steps followed in Excel. Place the cursor in a blank cell where you wish the correlation coefficient value to appear. Click on the Formula tab and select more> (located on the upper right section of the screen). A list of possible functions will appear. Select CORREL from the Statistical option.

Double click on the formula that Google Spreadsheet provides in the box and that formula will appear on the spreadsheet. Replace data_1 in the formula with the range for the first variable (in our example it is A2:A22). Replace data_2 in the formula with the range for the second variable (in our example it is B2:B22). The two ranges are separated by a comma (A2:A22, B2:B22). Like the Excel spreadsheet, these values can be typed or included by clicking and highlight the cells in the spreadsheet. Once the cell ranges are included, press Enter on the keyboard. The correlation coefficient value will appear in the cell.