Excel is a powerful computer program that can graph, do statistical analysis and automate simple calculations. You will use excel often when treating data in General Chemistry I and II labs. This worksheet is intended to introduce you to the basics you will need to know for Chemistry laboratories. Excel can do much much more, but we will not get into that here.

The best way to show you excel is by going through an example, so click on the "start" menu, go to "programs" and "Microsoft Excel". Follow along with this example and make your own excel worksheet.

Suppose you have a block of ice and you want to know the mass of the ice as a function of time. The ice melts so the mass decreases with time. You measure the mass of the block of ice every 2 minutes for 30 minutes and get data like this (I have no idea if this is reasonable, I’m making up this data because the important thing here is how to use excel not the actual values):

Input these numbers into your excel spreadsheet. Excel is divided into cells. Each cell is designated by the column (A, B, C….) and row (1, 2, 3,…). Put the time values from 0 to 30 in a single column and the corresponding Mass values in the next column over. In other words, if the time "0.0" is in cell A1, then the mass "10.0" is in cell B1. The time "2.0" goes in A2 and mass "9.0" goes in B2 etc.

In order to determine the rate at which the ice is melting, we need to plot the data.

1. click on the chart wizard over on the right side of the toolbar at the top of the page. The icon should look a little like this except it has colors and looks much nicer.

 

2. When the chart wizard comes up, click on "XY (Scatter)" from the "Standard Types" of graphs. Click "Next".
3. You should now be on the "Chart Source Data" pop-up. Select the "Series" tab.
4. Select "Add" under the Series box.
5. Click in the "X values" box and highlight your time data. This puts your time data on the x axis.
6. Put the mass data on the Y axis in a similar manner. At this point you should have a preliminary picture of your graph. If everything looks OK click "Next".
7. This takes you to the "Chart Options" pop-up window which allows you to add titles and labels. Give your chart a title (something like "mass of ice block"). Label the x (horizontal) axis and the y (vertical) axis. Make sure to include your units in your labels. Play around with some of the other options to see what they do. For example, you can click on "Legend" and then uncheck the "Show Legend" box in order to remove the legend (you don’t have to do this, but I find the legend that excel automatically puts in to be extremely annoying so I get rid of it.) Click "Next".
8. "Chart Location" should be fine so just click "Finish" (you can always change it later).

Now we have a graph that shows the mass of the ice block decreasing over time. But we need more information.

9. Click on your new chart. The toolbar at the top of the page will have a "chart" option. Click it and go to "add trendline".
10. Choose "Linear" for the chart type and then select the "options" tab.
11. Check "display equation on chart" and "display R-squared value on chart" and select OK.

What you have just done is to draw the best fit line through your data points. This line DOES NOT go through all points. It is the straight line which comes closest to ALL points. You have also found the mathematical equation of this best fit line. This equation is of the form y=mx+b where x and y are your axes, m is the slope of the line and b is the y intercept. For example, you should have gotten the equation y = -0.3189x + 9.6522. –0.3189 is the slope of the line, m. m=(change in y value)/(change in x value). In other words, the mass of the ice block changes by –0.3189 gram (the mass decreases) for every minute. The y intercept is 9.652. This is the point where your line crosses the y axis. In other words, the point where x=0.0. The R2 value gives you an idea of how close your points are to the best fit line. A value of 1.00 means that ALL point are EXACTLY on the line.

Another neat thing that excel can do is automate repetitive calculations. For example, suppose that you want to know the mass of the ice block in ounces rather than grams. You know that 1 g.= 0.0353 oz. (look it up in your book). Rather than multiply each mass in your table by 0.0353, let excel do it for you.

1. Go to the next column over from your mass column and click on the first cell (ie. if the first mass in g. is in cell B1, click on cell C1).
2. Type "=". This lets excel know that you are going to write an equation.
3. Click on the mass in g value (B1 in our example) and then write "*0.0353" (*is the symbol for multiplication in excel). Notice that the dialogue box at the top of the page says "=B1*0.0353" which means multiply the value in cell B1 by 0.0353. Hit "enter". The value 0.353 is in cell C1.
4. Click on cell C1. Click in the lower right hand corner of the cell and drag down. When you release the mouse button, the mass values in oz. appear in column C.

Now you try. Suppose that you use a spectrometer to measure the transmittance of solutions of varying concentrations.

Transmittance does not do us much good, so we first need to convert to absorbance, A, by the formula A=-log(T) (don’t worry about what the math means for now). Use Excel to convert transmittance to absorbance and then plot the absorbance (y-axis) vs. concentration (x-axis). Find the equation of this line and report the slope, y intercept and R2 value. You have just made a calibration curve. A handy skill for later in the course.