LABORATORY SCHEDULE
Professor Charles E. McKenna
Department of Chemistry
Last updated October 24, 2003
| Previous Lab |
Enzyme Kinetics using a Spreadsheet
GRAPHING AND LINEAR REGRESSION USING MICROSOFT EXCEL
This document is designed to teach you how to create a graph and perform a linear regression using Microsoft Excel. The version shown in the pictures will be Excel 97 for Windows: the Mac version and older Windows version are slightly different and we will point out the differences as they arise.
Excel is a spreadsheet program. This is a program that is designed to take data, usually numerical, and perform various operations on it and output those results as numbers or graphs. While designed primarily for businesses, Excel can also be used for scientific work.
To begin the process of creating the graph, simply click the mouse in a cell and type the data into that cell. The data can be numbers, words, mathematical formulas, etc. When done, click the mouse in another box and enter the data there. In this example, we've created two columns: temperature and volume.
When you've entered all of the data, you can create a graph from it. To select the data that you want to graph, click and hold the left mouse button on the upper left cell, then drag the mouse to the lower right cell while holding the button down. The data will highlight.
To add a graph to the spreadsheet, you can go to the Insert menu and
choose Chart, or click on the "Chart Wizard" button on the
toolbar- it looks like a bar chart.
On the Mac version, you will have to decide where to place the graph- simply click and drag where you want the chart to appear on the sheet. The Chart Wizard will then appear. In Excel97, the Chart Wizard will appear first- you can place the chart later.
On the first Page of the Chart Wizard when it asks you the type of plot, choose "XY Scatter" and the type without any lines joining the points. Click Next when done. The second page of the wizard should allow you to set the range of data that you want to graph. If you've already highlighted the data as we told you to earlier, the range will already be filled out. Click Next
The third page allows you to set things like the chart title, axes labels and so on. Fill out any data you need to enter here. You can now click on the Finish button. On the Mac version, the chart will appear where you wanted, the Windows version will place it automatically.
Once you chart appears on the screen, you may want to make changes in how it
is displayed. Here, since we want to find out where the gas goes to zero volume,
we want to change the X axis (temperature) to go to at least -300 C. Double
click on the x axis and you will get a dialog box that allows you to set a wide
variety of parameters for the axis, including max, min, log/standard, and so on.
Once this has been set, we want to perform a linear regression on the data. Click on one of the data points on the graph- the points will highlight. On the Windows version, go to the Chart menu and select Add Trendline. On the Mac version, go to the Insert menu and select Trendline.
Select the Linear type of Trendline from the first window. Click on the Options tab to bring up the second window where you can set options for the line.
We want the details of the linear fit, so select both the checkbox labeled Display Equation on Chart as well as Display R-squared value on Chart.
For this experiment, we want to find the point where the line will cross the x axis, which should occur around -273 C. Thus, we select Forecast Backwards 350 units., which should give us enough of a line to see the x-intercept.
Click Finish, and the graph appears with the line as well as the equation. To make the graph look a bit nicer, you can move the labels and equations around- simply click on one of them. The item in question will be surrounded by a box with a bunch of small black squares around it. Click and drag on one of the small squares to resize the box, click and drag within the item to move it.
Once you've finished, select the graph by clicking on it. (The graph will appear with the boxes around it.) Select print preview from the File menu to get a view of the whole graph. Once it looks ok, go ahead and print it and include it with your lab report. You're done!
(C) CE McKenna, Ph.D. USC, Chemistry Dept.,
2002