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

* *

The minimum and maximum values of the x- and y-axis can be changed
by clicking on the
scatterplot chart, selecting ** Axes** from the

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

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

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

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

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