Using Excel 2007 to Create a Scatter Diagram and Obtain Regression Output
Entering Data
The data used for this example that looks at the relationship between sales and the number of ads
placed. This tutorial will use the data set to construct a scatter diagram and to obtain regression
output for simple linear regression.
The first step is to enter the data. In general, this can be done by typing in the values or by
opening the file on the disk that came with the book. Recall that these files are also on the R
drive on the NGCSU campus and in the Shared Class Files (in the BUSA 3110 folder inside the
NGCSU folder) on the GSC campus.
Typing variable names in the first row makes it easier to keep track of what is entered. This is
not required…just a convenience. Since we will be setting up a scatter diagram, it is easier if we
put the data in so that the X variable (the variable that will be on the horizontal axis) is in the
first column and the Y variable (the variable that will be on the vertical axis) is in the next
column. In this problem we are attempting to estimate sales based on the amount spent on
advertising. Therefore, Sales is Y and Advertising is X. Once the data are entered your screen
should look like the following.
To make a scatter diagram, highlight the data (no need to highlight the column headings) and
click on the Insert tab.
