Spreadsheets
So many spreadsheet programs are in widespread use that it is impossible to describe the use of each one in detail. Instead, we provide here detailed instructions for using Microsoft Excel®, which is one of the more popular spreadsheets in a Windows® operating system. Since most spreadsheets operate similarly, these instructions should be generically useful for other commercial spreadsheets as well. The instructions given here are tailored to the workshop, Graphing, in Explorations in Chemistry. By applying the instructions to the vapor pressure-temperature data (Data Set 1, Experiment 1) from the graphing workshop, students will become familiar with the basic operations of the spreadsheet. They should then be equipped to apply the spreadsheet to any desired data set. The instructions for using Excel® are given here so that the instructor may see the type of detail required to initiate students in spreadsheet use.
You need a (blank, formatted) disk for storing worksheets created with the spreadsheet.
From the Windows 95 desktop, click on the Excel icon in the microsoft office icon group.
This opens Excel and brings up Sheet 1 of a blank workbook.
Creating a Worksheet Containing Data to be Plotted
In cell A1, type a title for the worksheet. For example, "Graphical Analysis of Data, Experiment #1" <Enter> or Down arrow key.
(Using the down arrow key simultaneously enters what you typed and moves the pointer to cell A2).
Move the pointer to cell A3 (mouse or arrow key).
Type a label for the first column of numbers in the set of data points. For example, "X, units", Right arrow key.
Type a label for the second column of numbers. For example, "Y, units".
Move the pointer to cell A5 (mouse or arrow keys). Skipping a row sets off the column titles from the data.
Enter the first data point in columns A and B, independent variable in A cell, dependent variable in B cell.
Enter the second data point in the next row. Continue until all data points entered. If you enter a number incorrectly, move to that cell, type the correct value, and press <Enter>.
Performing Calculations on the Data in Order to Make Plots
Suppose that using the X,Y data referred to above, you wish to plot lnY vs. 1/X. Before doing this, you must calculate lnY and 1/X for each data point. The spreadsheet will do these calculations for you. Place 1/X in column C, and lnY in column D.
Move pointer to C3 and enter a label; e.g., "lnY".
Do the same in D3; e.g., "1/X"
Move the pointer to cell C5.
Enter the formula for calculating the natural log of the vapor pressure in cell A5.
1. Type =ln(a5) <Enter>. This tells the spreadsheet to compute the natural log of the number in cell A5 and put the result at the current pointer position, cell C5.
Copy the contents of C5 to cells C6 and below.
1. Select C5 by clicking on it with the mouse.
2. From the Edit menu, click Copy.
3. Place the pointer in C6, hold down the left mouse button, and drag the pointer down to the row where the last data point occurs. For discussion assume this to be row 15. This operation hilites cells C6-C15 in black.
4. From the Edit menu, click Paste. The formula from C5 is copied to C6-C15.
Move pointer to cell D5.
Enter the formula for calculating the reciprocal of the temperature in B5.
1. Type =1/b5 <Enter>. The = is necessary to tell the spreadsheet to do a calculation, not enter a number.
Copy the contents of cell D5 to cells D6:D15.
Plotting the Data
Your goal here is to plot lnY vs. 1/X, and to draw the best straight line through the points in each plot.
Ln Y vs. 1/X Plot
The $ prefixes prevent the spreadsheet from changing the letter and number in copying cell E5 to cells E6:E15.
Dragging with the mouse, select the "x" range of data (1/X in C5:C15).
Hold down <Ctrl>, then use the mouse to select the "y" range (ln Y values in D5:D15)
Click on Insert in the menu bar.
Click Chart
Click XY Scatter, then Next
Your selected data range should be shown. Click Next
Click on the Titles tab.
Click on space under Chart Title, type title.
Repeat for X and Y axis labels. Click Next
Click As New Sheet
Click Finish. The chart will be displayed.
Click the mouse once to deselect the chart title.
To set axis scaling:
1. Select axis by clicking on it with the mouse. First, select the x axis.
2. Select Format from the menu bar.
3. Click Selected Axis
4. Click the Scale tab
5. Enter desired minimum value, maximum value, and unit values
6. Click OK
Click outside the chart window to deselect the axis.
Select the y axis and proceed in the same fashion.
When done, click outside the chart window to deselect the axis.
At this point the chart should have a title, axis labels, and should contain the plotted data points.
Adding a Best-Fit Straight Line
From the Chart menu, select Add Trendline
Click the Options tab.
Select Display Equation on Chart
Select OK. The chart will be shown with a trendline through the data, and the least squares slope and intercept.
Saving the Workbook and Plots
Insert your data disk into a disk drive.
Select File from the menu bar.
Select Save As
Select drive and directory, then type in filename. Click Save.
Now you can access the data and plots at any future time using the Open command in the File menu.
Printing the Workbook and Graphs
Turn on the printer and wait for it to be ready.
Select File from the menu bar
Select Print
Select Entire Workbook
Select OK.
Wait for the print to come out.
When finished, close your workbook by selecting Close from the File menu.
Select Exit from the File menu