BASIC INSTRUCTIONS FOR USING EXCEL TO GENERATE
SCATTERGRAPHS AND REGRESSION DATA
The following instruc tions ar e tied t o Exercise 5 -2 in t he text, b ut ma y be us ed for any problem.
Set up your data in columnar format with the first row used as labels for the column. For exa mple, Column A
could be labeled “Month,” Column B could be labeled “Units Shipped,” and Column C could be labeled “S hipping
Expense.”
Then type in the actual data using rows 2 through 8.
To make a scatterplot,
Click on “insert” in the menu bar
Click on “chart”
Click on the icon representing the XY Scattergraph (XY scatter)
Click next
In the “data range” box, type in B2.C8 L [make sure that “ser ies in columns” is selected]
Click next (add a title if desired; value X could be labeled “units” and value Y could be labeled “dollars” if
desired)
Click next
Choose “new sheet” or “object in sheet 1" (depending on whether you want the char t on a new page or
on the page with the data)
Click on finish
To do a simple regression,
Click “Tools” on the menu bar
Click “Data Analysis” L if data analysis is not present, you will have to do an add in; clic k on “add in”
under the tools menu and check “analysis tool pak” and click okay
Under “data analysis,” click on “regression” and hit okay.
In the box that appear s, ent er the f ollowing in t he “input frame”
Input Y range (the dependent variable)–type in C1.C8
Input X range (the independent variable)–type in B1.B8
Make sure that the “labels check box” is checked since we have used row 1 for labels.
In the “output options frame,” cli ck either
Enter output range and type in A10 (or a cell of your choice) if you want the outp ut on the same pa ge or
New Worksheet Ply (type in a title name if desired) if you want the output on a new page
In the “residuals frame,” check “line fit plots” and then click okay. (You may also want to click residual
plots to see another picture of the “goodness of fit”)
The regression and chart will now appear
Click on the data plots on the chart to highlight them and then right click with the mouse
On the menu that appears, click “add trend line”
Click on the “linear” box and then click on okay
You may resize the chart by dragging on its drag icons. You may extend the trend line back to the Y axis if
you desire by clicking on the trend line to highlight it. Then right click with the mouse and click on “format
trend line.” Click on “options” and in t he “forecast backwards” box, type in 2 (to move ba ck two units t o get
to 0). You shou ld now hav e the compl ete regr ession l ine.
Practice and experiment on your own with other exercises and problems.