Creating a Scatter Plot in Excel 2007 and Excel 2003

Creating a Scatter Plot in Excel 2007 or later.

(1)  Highlight the data.  Note:  The first column will become the X-Axis and the second column will be the Y-Axis.

(2)  From the Excel menu, click on INSERT, click on SCATTER, then click on ALL CHART TYPES this will make the chart options box bigger.

(3)  Click on XY (Scatter) – it should be on the left.

(4) Click OK.  The Scatter Plot should be on the same page as your data.

(5)  If you wish, you can change the size of the chart window and drag it anywhere  you want on the worksheet.

(6)  To make changes to the chart, you can use DESIGN, LAYOUT, and/or FORMAT on the chart tools  menu.

Creating a Scatter Plot in Excel 2003

1. First, type your data in two columns. Age is in cells A3:A18 and Number of absences is in cells B3:B18.

 Age # absences 20 2 24 1 28 3 30 4 32 5 35 7 41 6 45 8 48 5 50 9 53 9 55 12 62 10 64 8 65 11 67 13

2. Select Insert > Chart from the tool bar to bring up the Chart Wizard.

3. Choose XY (Scatter) and select the unconnected points from the Chart sub-type

4.Click Next>

5. In the Data Range box, indicate where all of your data (the X and Y variables) are located. For example, you might indicate A3:B18. Since your data is in columns, check Columns under: "Series in."

6. Click Next>

7. You will now write labels for your chart:

Chart title: Give the chart a name, e.g., Employee Absences by Age

Value (X) Axis: variable name for the x-variable, e.g., Age

Value (Y) Axis: variable name for the y-variable, e.g., Number of Absences

8. Click Next>

9. Place chart: you will have a choice to place chart As new sheet: or As object in: by clicking on one of these options. Choose: As object in: sheet 1

10. Click Finish

Note: If you highlight the chart, you can "grab" one of the corner handles to change the size of the chart. You can also drag it to another part of the page.

To Create a Linear Regression Line

11. Highlight the entire chart window. You should see a "Chart" menu appear on the tool bar (next to the "Window" menu). Select Chart > Add trendline.

12. Trend/Regression Type: Select Linear

13. From the Options Tab, click on boxes in front of Display equation on chart and Display R-squared value on chart. A high R-squared value means that the X-variable does a good job in explaining the Y-variable.

14. Click OK