# Fitting a Line in Microsoft® Excel

## Chapter 4, Printout 1

### Section 1: Making a Fitted Line Plot

1. Enter the data.

• In cell A1, type "Pressure"

• In cell B1, type "Density"

• In cells A2-A16, enter the pressure data from the x column in Table 4.1 (p. 124).

• In cells B2-B16, enter the density data from the y column in Table 4.1.

Screenshot:

2. Plot density vs. pressure.

• Select "Tools --> Data Analysis."

• In the dialog box that follows, select "Regression" from the list.

Screenshot:

• Click "OK."

• Fill out the resulting dialog box as follows:

Input Type Label Value
Input Y Range: \$B\$1:\$B1\$16
Input X Range: \$A\$1:\$A\$16
Labels checked
Constant is Zero unchecked
Confidence Level unchecked
New Worksheet Ply: selected
New Worksheet Ply:
Residuals unchecked
Residual Plots unchecked
Standardized Residuals unchecked
Line Fit Plots checked
Normal Probability Plots unchecked

Screenshot:

• Click "OK."

Excel places the regression summary and line-fit plot in a new sheet of the workbook.

Screenshot (summary output):

Screenshot (plot):

Note: you can resize all columns to fit their contents by selecting all the cells you want to resize, then selecting "Format --> Column --> Autofit Selection."

3. Edit the plot for a more informative display.

• Double-click on one of the "predicted" points on the plot. If you are using Excel's default settings, these should be pink.

• In the dialog box that opens, click the "Patterns" tab.

• Under "Line," if "None" is selected, change the selection to "Automatic."

• Under "Marker," select "None."

Screenshot:

• Click "OK."

Excel draws the fitted line and removes the predicted points from the plot.

Screenshot:

Note: you can resize the chart that contains the plot by clicking a blank area in the chart to select it and dragging one of the handles that appears on the chart's border.

### Section 2: Generating Additional Summary Information and Plots

1. Return to the worksheet with the pressure and density data.

• Click the tab labeled "Sheet 1" near the bottom of the screen.

2. Generate new summary information and plots.

• Select "Tools --> Data Analysis."

• Select "Regression" from the list as in Section 1.

• Fill out the resulting dialog box as in Section 1, but with the following changes:

Input Type Label Value
Residuals checked
Residual Plots checked
Standardized Results checked
Normal Probability Plots checked

Screenshot:

• Click "OK."

Excel creates a new sheet in the workbook with the summary statistics and three plots.

Screenshot (summary output):

Screenshot (plots):

Note: you can move the charts as necessary by clicking a blank area in the chart and dragging it to a new location. You can add the line to the fitted line plot and resize the charts as described in Section 1. Other changes can be made by double-clicking various elements in the charts.