The purpose of this tutorial is to teach you how to plot data on a simple X-Y scatter plot, conduct a simple linear regression analysis to determine how useful one variable (the independent variable "X") is at explaining the variation in another variable (the dependent variable "Y"), place the best-fit line (regression line) on the scatter plot, and prepare a publishable-quality graph. The model used in this analysis is the equation for a straight line:

Excel is a relatively large program with many features that are not automatically loaded when the program is installed. The statistical analyses procedures are some of those features that need to be manually loaded. Pull down the Tools menu (place the cursor over the menu and depress the mouse button) to determine if Excel has been configured to use the Data Analysis feature. If Data Analysis appears on the list of menu items, your computer is configured to use the statistical analysis tools. If it is not an option, you must load this feature. Pull down the Tools menu as described above and release the mouse button over Add-Ins to load the analysis tools. A splash screen appears with a list of options. Click on the Analysis ToolPak option (an x should appear in the box at the left of each item). Click OK and your done! Excel will automatically load the necessary statistical analyses tools each time it is opened.

1. Data Entry
   1. Type Height (cm) into cell A1
   2. Hit the return key
   3. Type the following data into the cells in the Height column (hit return after each number): 145, 132, 165, 187, 134, 147, 197, 201, 245, 234
   4. Type Weight (kg) into cell B1 and hit return
   5. Type the following data into the Weight column: 50, 43, 54, 58, 43, 50, 60, 61, 65, 62
   6. Sort data by Height
      1. Select columns containing all data (including column heading: Height and Weight)
      2. Select Sort from the Data menu
      3. Sort by Height in Ascending order
      4. Check the Header Row radio button if it is not already checked
      5. Click ok
   
   
2. REMEMBER TO SAVE YOUR WORK OFTEN!
   
   
3. Linear Regression Analysis of Data
   1. Pull down the Tools menu and select Data Analysis
   2. Scroll through the list of options on the splash screen, highlight Regression, and click OK
   3. You must now tell the computer where to find the data on the Regression splash screen
      1. Place the cursor over the open rectangular box adjacent to the Input Y Range: and click the mouse button (the cursor bar should now be flashing in the white box), then click the small square box-shaped button at the end of the white box to "roll-up" the Regression box
      2. Highlight the range of cells you wish to represent the y-axis (dependent variable): in this case choose the Weight column (including the cell with the Weight title), then click the box-shaped button to "unroll" the Regression box
      3. Notice that the range of cells appears in the white box adjacent to the Input Y Range:
      4. Repeat the steps above for the independent variable (Input X Range:)
      5. Click on the Labels box (notice that an X appears in the box)
      6. Click the radio button adjacent to Output Range: and then place the cursor in the white box
      7. Choose an ouput range (where you wish to have the results displayed) by clicking on a cell in the worksheet (choose D1 in this example), and notice that the cell address appears in the text box
      8. Click OK
      9. Your computer should flash a series of screens and finally return a Summary Output which includes a Regression Statistics table and an ANOVA table
      10. The R Square value in the Regression Statistics table is a measure of how closely the data fit the regression line
      11. The ANOVA table reports a number of statistics as well as two important coefficients:
          • The Intercept Coefficient (Y intercept) is the b in the equation for a straight line: y = mx + b
          • The Height (cm) Coefficient is the m in the same equation
   
   
4. REMEMBER TO SAVE YOUR WORK OFTEN!
   
   
5. Plotting data on an X-Y Scatter Plot
   1. Highlight the two columns of data (Height and Weight)
   2. Click on the Chart Wizard icon/button (this button looks like a horizontal bar chart or histogram) in the Tool Bar
   3. The Chart Wizard splash screen should appear with a list of Chart types. Highlight XY (Scatter).
   4. Choose the top Chart sub-type (the chart with only data points and no lines).
   5. Click the Next> button.
   6. The next screen (Chart Source Data) should show the preliminary graph. If you desire to change the independent and dependent variables, select the Series tab and make the changes. Click Next> when you are ready to proceed.
   7. The Chart Option screen allows you to modify the appearance of the graph.
      • It is best to leave the title blank because titles of figures should be below the figure.
      • Enter axes titles.
      • Axes should be left at the default setting.
      • Guidlines may be turned off or left on.
      • Legends are important when two or more dependent variables are plotted on the same figure. The legend should not be present for a single Y-variable.
      • Data labels should not be used for most graphs.
      • Click Next> when finished.
   8. Choose the Chart Location on the next screen and click Finish.
   9. You can move the chart around your desktop by placing the cursor over the chart, depressing the mouse button, and dragging the chart.
   10. You can resize the chart by depressing the mouse button when the cursor is positioned over one of the small black squares inside the box outlining the chart and dragging.
   
   
6. REMEMBER TO SAVE YOUR WORK OFTEN!
   
   
7. Place Finishing Touches on Your Plot
   1. Double-click on the chart to make it active (an active chart is enclosed by a box with small black squares positioned inside the box around its perimeter).
   2. Place a regression line on the scatter plot
      1. Select Trendline from the Chart menu
      2. Choose Linear from the splash screen
      3. Click OK
      4. A best-fit line should appear on your plot
   3. Change the shape of the area enclosed within the axes (allows changing orientation of X-axis label)
      1. Click the upper right corner of the grey box enclosed by the axes
      2. A box should form around the grey area
      3. Place the cursor in any of the small dark squares located on the corners and midpoints of the box enclosing the grey area
      4. Click on a box and drag to change the shape
      5. Click within the plot but outside the grey area to close the editing box
   4. Format the plot area
      1. Double-click on the plot area
      2. Check none for Border
      3. Check none for Area color
   5. Format the X-Axis Title
      1. Double-click on the X-axis Title (Height)
      2. Patterns
         • Check None for Border
         • Check None for Area color
      3. Font
         • Choose 12 pt
         • Choose a Type consistent throughout the document (Geneva is the default)
         • Choose bold
      4. Alignment
         • Orientation - TEXT
         • Text Alignment - your choice
   6. Format the Y-axis Title following the same procedure as was used for the X-axis Title
   7. Format the X-axis
      1. Patterns
         • Custom - choose a suitable Style, Color, and Weight
         • Tick Mark Type
           • Major - Inside
           • Minor - None
         • Tick Mark Labels - Next to Axis
      2. Scale - choose values to position the data in the center of the graph
      3. Font - be consistent
      4. Number - All
      5. Alignment - Automatic
   8. Format Y-axis following the same procedure as was used for the X-axis
   
   
8. REMEMBER TO SAVE YOUR WORK OFTEN!
   
   
9. Printing
   1. Double-click the graph to select it
   2. Choose Print Preview from the File menu
   3. Click the Setup button
   4. Select the radio button adjacent to Landscape
   5. Click the Margins tab
      1. Set left, right, top, and bottom to 1
      2. Set From Edge to 0.5
   6. Click Header/Footer tab
      1. Header: scroll to None
      2. Select Custom Footer
         • Delete entry in Center Section:
         • Place cursor in Left Section: text box
         • Press A button
           • Choose a consistent font
           • Font Style: Regular
           • Size: 12
         • Type a figure title in the text box. Figure titles should be constructed as a sentence, e.g., Figure 1. The relationship between the price of gold and the price of silver.
   7. Press the Print button
   
   
   

   ---

   Now that you are experienced in using MS Excel to calculate regression coefficients (R²) and plot data, why not try out you new skills on the questions below?

   Use the data in the table below to answer the following questions:

   Table 1. Measurements of height, weight, exam scores, and metabolic rates taken from a sample of Westfield State College students.
   Height (cm)	Weight (kg)	Exam Score %	Metabolic Rate mL O2/h
   145	50	92	110
   132	43	55	120
   165	54	85	98
   187	58	72	85
   134	43	98	115
   147	50	85	75
   197	60	68	73
   201	61	70	68
   245	65	75	58
   234	62	80	62

   
   
   1. What is the relationship between height and exam scores?
   2. What is the relationship between weight and exam scores?
   3. What is the relationship between height and metabolic rate?
   4. What is the relationship between weight and metabolic rate?