Excel

Organization and Analysis of Experimental Data

Using an Excel Spreadsheet

Introduction: MicroSoft Excel, like other spreadsheet programs, is a powerful software tool for doing extensive chemical calculations without having to know a programming language like C, Fortran, Basic, or Pascal. Results of experimentation and calculations can be organized and visualized in a readily understandable tabular format. In this computational exercise we will use Excel to perform calculations related to Experiment 1 in the Chem111/112 Lab Manual on page 8, entitled Principles of Physical Measurement. In reality this is a trivial example of the use of a spreadsheet to organize and process experimental data, to the extent that it is perhaps as easy to make a table and manually calculate the numbers by hand calculator. Still it will serve our purposes of introducing the use of spreadsheets to treat experimental data.

Part I: Format and Labeling: First, we will set up the format and labeling of the spreadsheet to look similar to the top portion of the DATA and NUMERICAL RESULTS section on p. 15 of your lab manual. Turn to that page right now, where your data from that experiment will be recorded.

1. First, log into your student account on the PC Lab network.

2. Click on the "Start" button in the lower left hand corner, then click on Programs, then MSOffice97, then Microsoft Excel. This launches the Excel program and brings up a blank spreadsheet on your screen.

3. Make Column A twice as wide to accommodate lengthy labels by (a) positioning the cursor on the dividing line between the cells labeled A and B in the top row, (b) hold the mouse button down and drag the cursor to the right, enlarging the width of Column A. This is a standard operation which can be performed at any time for any column on your sheet.

4. Begin to enter labels for your table of data as shown below, by moving the mouse to various cells, selecting the cell by clicking with the mouse, and typing the label into the cell.

When you finish with the labeling, your spreadsheet should look something like the following:

5. Now, go ahead and Save what you have done on the disk space available to your PC Net account so that it won’t be lost in a power outage. Go to the File Menu and choose Save. You will be asked to select a disk directory and give the file a name. Choose the disk directory U:, which stands for User directory. Enter any file name that makes sense to you. It is customary to put a file extension .xls for an Excel spreadsheet. e.g., the name might be exp1.xls. From this point on it is a good idea to Save the file again under the same name every so often in case you make a mistake that corrupts your file.

Part II: Data Entry - Density of Water Determined by Buret Measurements: Now we begin to enter numerical experimental data into the spreadsheet. If you have not performed this experiment yet, some sample data is given in the example table below. Enter it. If you HAVE performed the experiment, use the data that you actual took during your laboratory procedure. Do not enter the numbers that result from doing calculations, rather simply the RAW DATA.

Raw data when first entered looks something like this:

At this point this numbers entered appear on the spreadsheet with the incorrect number of significant figures. For example, buret readings should be recorded to the nearest 0.01 mL, and yet several of the buret readings happened to have trailing zeros behind the decimal point (e.g., 0.40), and Excel simply truncated the zeroes. This will not affect the final numerical results, but it does violate our rules about showing significant figures correctly. To allow the sheet to show the correct significant figures for the volumes, you need to reformat the data cells. The masses are also known to the nearest 0.01 g, so these need to be reformatted also.

To reformat cells, hold down the mouse button and drag across the data cells you wish to reformat. Here the drag should take place from Cell B3 to E9. Then go up under the Format menu and choose Cells. In the FormatCells dialog box, enter 00.00 in the Type: box and click OK. There are other ways here that will work also.

The upper portion of the spreadsheet should then look like the following:

Part III: Numerical Calculations: Now we input the formulas that allow Excel to calculate the net volumes of the buret and the net masses, and finally the densities, average density, and uncertainty values.

1. Select cell number B5 by clicking on that cell. This cell represents the Net Volume in mL of the buret in Run 1.

2. Input the formula that will allow this cell to be calculated as the difference between final and initial buret readings for Run 1. A mathematical formula is input by typing an "=" sign as the first character in the cell, followed by the formula based on cell locations:

in Cell B5 type = B3- B4 and then hit the Enter key.

This lets cell B5 take on the value B3-B4, which is the final - initial buret reading.

3. The formula needs to be repeated across row 5 for the other three Runs. The easiest way to do this is to Copy the formula from cell B5 to cells C5, D5, and E5.

Click on cell B5, pull down the Edit menu to Copy. With the mouse, drag across cells C5 through E5 and pull down the Edit menu to Paste. The formulas are based on relative cell locations, so you are not copying the number from B5 across the row, but the formula from B5 across the row.

4. Repeat the same type of operations to calculate the net mass in Row 9.

The upper portion of the spreadsheet should now look like this:

5. Now we are ready to calculate the density for each run. This is done by entering formulas in Row 11 which divide the masses by the volumes for the four runs. Start by clicking on cell B11 to select the density cell for Run 1. Then type in =B9/B5 and enter.

6. Select Cell B11 again and copy and paste the formula just entered over into cells C11, D11, and E11. Now you should have densities calculated for all four runs.

7. Now we are ready to calculate an average density in Cell B13. This can be done most easily by selecting Cell B13 and entering = AVERAGE(B11:E11). This performs an average of the numbers in the range B11 through E11. Round this answer off to the correct number of significant digits by reformatting Cell B13 using the Format/Cells dialog box.

8. Calculate the standard deviation of the density in Cell B14 by selecting B14 and entering the formula =STDEV(B11:E11). Round this answer off to two significant digits by reformatting Cell B14 using the Format/Cells dialog box.

9. The percent relative error is obtained in cell B15 by dividing the standard deviation (B14) by the average density (B13) and multiplying by 100.

10. The literature value of the density is obtained from the CRC Handbook of Chemistry and Physics for the temperature at which the experiment is run. Enter this value and the temperature in the appropriate cells. If you have not done this experiment yet and don’t have your own data, use a literature density of 0.9970 at 25°C.

11. Calculate the Percent Absolute Error in Cell B18 by entering a formula for the difference between the average density and the accepted literature value, divided by the literature value, and then times 100.

Percent Absolute Error = (Average Density - Lit. Value) x 100

Lit. Value

12. Save what you have. Enter your name in some unused cell to be identified as yours when printed. Now Print your work. Generate TWO printouts, (1) the regular spreadsheet, and (2) one showing the formulas. The latter can be done best as follows: Go to Page Setup, choose Landscape orientation, choose Show Row and Column Numbers. Leave Page Setup. Then choose Format/Columns/AutoFit. Finally, choose Tools/Options/View and check Formulas. Now Print. Submit this sheet to your TA before leaving the lab to get credit.

Part IV: Independent Exercise: If time permits, go ahead and add to your spreadsheet the portion of the page 15 data table corresponding to Density of Water Determined by Graduated Cylinder Measurements. Do this on your own without instructions based on what you learned above. This will save you time when you enter your actual laboratory data later in the semester.