To Documents

Excel Correlation, Scatter Plots and Trendlines

Directions for Computing Correlation

• Suppose that one variable is found in cells A2 through A21 and the other variable is found in cells B2 through B21 on the Excel spreadsheet. The columns of the two variables should be adjacent.

Directions for Creating a Scatterplot

1. Load the Analysis ToolPak if it is not already loaded: Select main menu Tools >> AddIns >> Check Analysis ToolPak under AddIns Available. Click OK.

2. Select main menu item Tools >> Data Analysis.

3. In the Data Analysis Dialog, select Correlation and click OK.

4. In the Correlation Dialog
   1. In the Input Range box, enter A2:B21.

   2. Click the Output Range radio button and enter C2. You will get a warning if this choice will overwrite existing data. This produces a table that lists the correlations between Column 1 and Column 1, between Column 1 and Column2 and between Column 2 and Column 2. The correlation between any column with itself is always 1, so only the correlation between Column 1 and Column 2 is of interest.

5. Select main menu item Insert Chart. In the Chart Wizard:
   1. Step 1 of 4. Change the Chart Type to XY (Scatter). Leave the Chart Subtype on the first choice. Click Next >

   2. Step 2 of 4. Click Next >

   3. Step 3 of 4. Choose the chart options. Click Next >.

   4. Step 4 of 4. Select the sheet location as an object in the current sheet. Click Finish.

6. Move the chart to the desired location, or cut and paste it into a Word document.

Adding a Linear Trendline

• Select main menu Chart >> Add Trendline >> Select Linear.

• Select the Type tab >> Select Linear.

• Select the Options tab >> Check the checkbox Display R-sqrared value on chart. Check the checkbox Display equation on chart. Click OK.