Using Excel®in Chemistry
XY Plotting, Linear Regression, Statistics
Calvin College Chemistry Department
Fall 1995
Microsoft Excel®is one of the leading spreadsheet programs currently
available. In this paper, it is assumed that you are familiar with basic
spreadsheet operations (entering data, mathematically manipulating data,
etc.) If you have used other spreadsheet programs such as Quattro Pro or
Wingz, then you should have no trouble adjusting to Excel. If you do not
have spreadsheet experience, either talk to your professor or another student
who can help you. (Note also, that the Macintosh version of Excel is nearly
identical to the IBM PC version.)
However, the techniques for plotting and analyzing data are not always obvious.
Thus, the following information is given in order to help you quickly plot
and analyze your data. However, these instructions are not exhaustive (they
do not cover all statistical techniques, etc.) If you want to perform other
types of plots/analyses there is a book in the P-Chem Lab entitled: Running
Microsoft Excel 5 which has more information. Note: The on-line help
for Excel is currently not available.
A. Generating an XY Plot:
1. Select the two columns of data you wish to plot. Select your X data first,
and then your Y data. (If the data are not in consecutive columns, hold
down the Apple command key while selecting.)
2. Click on the ChartWizard icon 
on the toolbar
above the spreadsheet. The mouse pointer will now look like: 
. You now can
drag out a box of the approximate size and in the approximate area that
you wish your plot to appear. (You can move and resize your graph later.)
3. You will now be led through a series of dialog boxes. If you wish to
cancel at any time, just click on cancel. The first box asks you to confirm
the range that you wish to graph. Then you will be asked to pick the type
of graph -- we want type "XY" (scatter). Next you specify the
appearance of the graph. In general, type #1 (dots only) is adequate. A
sample graph will now appear. If you are not satisfied with it, you can
go back and change previous options. Next, you can give your graph a title,
label the axes, and specify whether you want a legend for your graph. If
you are satisfied, click on finish and the graph will appear in the area
you previously selected.
4. Making changes to an existing graph: Clicking once on a graph will allow
you to move/resize the graph. Double clicking on a graph brings Excel from
spreadsheet mode to graph mode. This will allow you to make
various changes to the graph's style. After Double clicking, you may either
select various parts of the graph by single clicking on them, or make format
changes to various parts by double clicking on them (for example, double
clicking on an axis, will allow you to format the axis in various ways:
scaling, ticks, etc.).
5. Printing a graph: To print a graph on a single page, without your spreadsheet
data, choose Print while in graph mode.
6. Inserting a best-fit line: In graph mode, select the data series
by single clicking on the dots. You will see something like this under your
toolbar:
This shows that you have selected Series #1 (S1). Next, on the menu,
go to Insert and then Trendline. Under Type, specify a linear regression,
and under Options specify if you want the equation of your best-fit line
and its r^2 value to appear on the graph.
B. Getting Linear Regression data from Excel:
First Select a 2 column x 5 row grid where you wish your statistical data
to appear. Then type: "=LINEST(E2:E7,B2:B7,1,1)" in the cell function
area -- where E2:E7 specifies your y range and B2:B7 specifies your x range.
Next, enter the formula into each selected cell simultaneously by pressing
'command-return'. You should get something like the following:
A B
Where A1 is the slope and B1 is the intercept of your best-fit line.
A2 and B2 are the standard errors in A1 and B1. A3 is the r^2 value, A4
the F statistic, and A5 the regression sum of squares. B3 is the standard
error value for y, B4 is the degrees of freedom, and B5 is the residual
sum of squares. You may find it convenient to label these numbers on your
spreadsheet.
C. Getting Descriptive statistics for a range of numbers:
Select 'Data Analysis' from the 'Tools' menu. Then select descriptive statistics
from the dialog box. Specify an input range, and give one cell as the output
range to specify where you want your output data to appear. You will then
get something like the following:
You may need to resize the first column in order to see all the text.
You can relabel "Column 1" as well.
Written by Jon Rienstra
September 1995