One feature of Excel that is important to the chemistry laboratory is linear regression. We are going to use the Analysis Tools to find the slope, y-intercept, and predicted values of Y for data.

Your data for the density of water experiment should be similar to the one shown below.

We now want to analyze the data in columns A and B.
From the menu:

Click on Tools then Click on Data Analysis Note: If you do not see "Analysis" under Tools, then you will need to add in the Analysis Tool Pack. Go to Tools, then Add-ins, then click on the box for Analysis Tool Pack. After hitting "ok", you should be able to see "Analysis" under the "Tools" menu. If not, then you did not install all of the options when you installed Excel on to your computer. Thus, you will need to reinstall Excel and make sure that you select the full installation option.
Scroll down and click on Regression

(Repeatedly click on the down arrow to the right of the choices in the dialog box until regression appears)


Click OK

A new dialog box will appear:

Click inside the box next to Input Y range


Highlight cells B5-B9 (or type B5:B9) or wherever your mass data is located on the spreadsheet.


Click inside the box next to Input X range


Highlight Cells A5-A9 (or type A5:A9) or wherever your volume data is located on the spreadsheet.


Under the heading Residuals Click on Line Fit Plots
Click OK

Excel will open a new worksheet and input a information relevant to regression and include a plot.

We only want some of this information, so we need to copy it to sheet1:

The predicted Y values (in the red box) are needed to generate the best line through your data.  Copy this column and paste it into the sheet that has your mass and volume data.  Paste it into the column right next to your mass of water data.  The slope of the line is the "X Variable"; it is in the blue box.  The Y intercept is the value of your line when x=0.  So, on sheet 1, put this value in the Predicted Y value column and put a zero in the same row in the Volume column.

On Sheet1 you should now have

• the data you entered (Volume in one column, Mass in the next)
• the predicted Y values (in the third column)
• the intercept value in the Predicted Y value column and a zero in the the same row under Volume
• x variable value is the slope, put this under your table of data e.g: Slope =   1.004647

We now need to graph the information so we have data points and our best fit line (predicted y):

Highlight the cells that have your data (volume, mass, predicted Y)


• Go to the top bar on your Excel file, and click on Insert, select Chart.  This will open up a box.
  Click on XY(Scatter) The top box should be selected.  See the graphing section for pictures.
  Click on Next >
  Click on Box under Chart Title
  type a title for your plot
  Click on Box next to Category (X)
  type a X axis title (Volume (ml))
  Click on Box next to Value (Y)
  type a y axis title (Mass (g))
  Click on Finish

We now need to make our best fit line a solid line with no squares:

Double click on the marker that is at x=0


(A dialog box should appear - if you do not see a dialog box click on Edit, then Click on Undo - try to double click again)


In the dialog box Under


1. Line

Click on Custom
Select weight and color of line

2. Marker

Click on None
Click OK

Remove the gray background (see section 9)

You now have a plot with a best fit line.

Add your name and the date to your worksheet (to any cell on the sheet1)
You will print out both the sheet1 and the chart to put in your notebook. (see section 12).

Back to the Tutorial

This tutorial was written by Dr. Laura Maki, it was last updated for Excel2000 on March 2, 2004 by Koni Stone. koni@chem.csustan.edu