McGraw-Hill, 1997, and even some remnants of my Spreadsheet Workbook for Quantitative
Chemical Analysis, McGraw-Hill, 1992. This is partially because I have retained some of the
didactic innovations introduced in these earlier texts, such as an emphasis on the progress
of a titration rather than on the traditional titration curve, the use of buﬀer strength rather
than buﬀer value, and the use of the abbreviations hand kin the description of electrochem-
ical equilibria. However, the present text exploits the power of Excel to go far beyond what
was possible in those earlier books.
For a few problems that would require the reader to write some rather complex macros,
these have been provided. They are fully documented and explained in chapter 10, and can
be downloaded from http://uk.cambridge.org/chemistry/resources/delevie Note that
their code is readily accessible, and that the reader is not only encouraged to modify them,
but is given the tools to do so. Again, the idea is to empower the reader to incorporate exist-
ing higher-language code into macros, in order to increase the reach and usefulness of Excel.
The ﬁrst chapter introduces the reader to the software; it can be speed-read or skipped by
those already familiar with Windows- or Mac-based spreadsheets. The last chapter dis-
cusses macros, which can convert a spreadsheet into a powerful computing tool.
Sandwiched between these are the four main parts of this book: statistics and related
methods, chemical equilibrium, instrumental methods, and mathematical analysis. These
parts can be used independently, although some aspects introduced in chapters 2 and 3 are
used in subsequent chapters, and the spreadsheet instructions tend to become somewhat
less detailed as the text progresses.
The treatment of statistics is focused on explicit applications of both linear and non-
linear least-squares methods, rather than on the alphabet soup (F, Q, R, T, etc.) of available
tests. However, within that rather narrow framework, many practical aspects of error analy-
sis and curve ﬁtting are considered. They are chosen to illustrate the now almost two centu-
ries old dictum of de Laplace that the theory of probability is merely common sense
conﬁrmed by calculation.
Since the spreadsheet is eminently capable of doing tedious numerical work, exact math-
ematical expressions are used as much as possible in the examples involving chemical equi-
libria. Similarly, the treatment of titrations emphasizes the use of exact mathematical
relations, which can then be ﬁtted to experimental data. In some of the exercises, the
student ﬁrst computes, say, a make-believe titration curve, complete with simulated noise,
and is then asked to extract from that curve the relevant parameters. The make-believe
curve is clearly a stand-in for using experimental data, which can be subjected to the very
For the more instrumental methods of quantitative chemical analysis, I have taken a
rather eclectic approach, merely illustrating some aspects that are especially suitable for
spreadsheet exploration, such as Beer’s law and its applications to the analysis of multi-
component mixtures, chromatographic plate theory, polarography, and cyclic voltammetry.
Because of its important place in modern chemical instrumentation, an entire chapter is
devoted to Fourier transformation and its applications, including convolution and decon-
volution. The chapter on mathematical analysis illustrates several aspects of signal handling
traditionally included in courses in instrumental analysis, such as signal averaging and
synchronous detection, that deal with the relation between signal and noise. Its main focus,
however, is on numerical analysis, and it covers such aspects as ﬁnding roots and ﬁtting
curves, integrating, diﬀerentiating, smoothing, and interpolating data. Numerical solution
of diﬀerential equations is the focus of chapter 9, where we discuss a number of kinetic
schemes, partially to counterbalance the earlier emphasis on equilibrium behavior.
The ﬁnal chapter describes the nitty-gritty of macros, and illustrates how they can be used
to make the spreadsheet do many amazing things in exchange for relatively little eﬀort on
the part of the user, who can simply incorporate pre-existing, well-documented, widely
The aim of this book, then, is to illustrate numerical applications rather than to explain
fundamental concepts. Theory is mentioned only insofar as it is needed to deﬁne the
nomenclature used, or to explain the approach taken. This book can therefore be used in
conjunction with a regular textbook in analytical chemistry, in courses on quantitative or
instrumental chemical analysis. It can also serve as a stand-alone introduction to modern
spreadsheet use for students of chemistry and related scientiﬁc disciplines, provided they
are already familiar with some of the underlying scientiﬁc concepts. Because of its emphasis
on exercises, this book is also suitable for individual, home use.
I am grateful to Drs. T. Moisio and M. Heikonen of Valio Ltd, Helsinki, for permission to use
their unpublished experimental data in chapter 4, to Professor Phillip Barak of the
University of Minnesota for permission to include his adaptive-degree least-squares algo-
rithm in chapter 10, and to Numerical Recipes Software of Cambridge Massachusetts for
permission to use some subroutines from the Numerical Recipes.
I am indebted to Professors Nancy Gordon and Gale Rhodes of the University of Southern
Maine, Professor Barry Lavine of Clarkson University, Professors Panos Nikitas and Nanna
Papa-Louisi of Aristotle University, as well as to Mr. William H. Craig and Professors Andrew
Vogt, George Benke, and Daniel E. Martire of Georgetown University, for their many helpful
and constructive comments and suggestions. I am especially indebted to Professor Joseph T.
Maloy of Seton Hall University for his extensive advice.
I am grateful to Georgetown University for a sabbatical leave of absence, which gave me
the unbroken time to work on this book, and to Professor Nancy Gordon of the University of
Southern Maine in Portland, Maine, and Professor Panos Nikitas of Aristotle University of
Thessaloniki, Greece, for their gracious hospitality during the writing of it. Finally I thank my
son, Mark, for his invaluable help in getting me started on this project, and my wife, Jolanda,
for letting me ﬁnish it.
User comments, including corrections of errors, and suggestions for additional topics
and/or exercises, are most welcome. I can be reached at
Corrections will be posted in the web site
From this web site you can also download the data set used in section 4.11, and the macros
of chapter 10.
part i: introduction to using the spreadsheet
how to use excel
First things ﬁrst: this introductory chapter is intended for readers who have
no prior experience with Excel, and only provides the minimum informa-
tion necessary to use the rest of this book. Emphatically, this chapter is not
meant to replace a spreadsheet manual; if it were, that part alone would
occupy more space than that of this entire workbook. Instead, during and
after using this workbook, you may be tempted to consult an Excel manual
(of which there will be several in your local library and bookstore) to learn
what else it can do for you – but that is up to you.
Second: this book is not intended to be read, but instead to be used while
you sit at the computer keyboard, trying out whatever is described in the
text. Learning to use a spreadsheet is somewhat like learning to swim, to ride
a bicycle, or to paint: you can only learn it by doing it. So set aside a block of
time (one or two hours should do for this chapter, unless you are really new
to computers, in which case you might want to reserve several such sessions
in order to get acquainted), make yourself comfortable, turn on the com-
puter, and try things out as they are described in, say, the ﬁrst three sections
of this chapter. (If it confuses you on your ﬁrst try, and there is nobody at
hand to help you along, stop, do something else, and come back to it later, or
the next day, but don’t give up.) Then try the next sections.
as the operating system on your personal computer, and that you have a
compatible version of Excel. Although there are relatively minor diﬀerences
between the various versions of Excel, they fall roughly into three categories.
Excel versions 1 through 4 did not use VBA as their macro language, and the
macros described and used in this book will therefore not run on them. The
second category includes Excel 5 and Excel 95 (also called Excel version 7;
there never was a version 6), which use VBA with readily accessible modules.
Excel 97, Excel 98 (for the Mac), and Excel 2000 make up the third category,
which has macro modules that are hidden from sight. The instructions given
in this book are speciﬁcally for the second and third categories, starting with
Excel 5. While they were mostly tested in Excel 97, all versions more recent
than Excel 4 will do ﬁne for most of the spreadsheet exercises in this book.
Because Excel is backward compatible, you can run older software in a more
recent version, but not necessarily the other way around.
When you have a Macintosh, your operating system will be diﬀerent, but
Excel will be very similar. After all, both IBM and Mac versions of Excel were
written by Microsoft. With relatively minor modiﬁcations, mostly reﬂecting
diﬀerences between the IBM and Mac keyboards, all exercises in this book
will run on the Mac, provided you have Excel version 5 or later.
In either case, whether you use an IBM-compatible PC or a Macintosh, use
at least Excel version 5, because earlier versions lacked some of the more
useful features of Excel that will be exploited in this book. If you have Excel 4
or earlier, it is time to upgrade.
When you are already familiar with earlier versions of Windows and Excel,
you may want to use this chapter as a refresher, or scan the text quickly and
then go directly to the next chapter. When you are already familiar with
Windows 95 or Windows 98, and with Excel 95 or 97, you may skip this
Windows is a so-called graphicaluser interface, in which many programs,
ﬁles, and instructions are shown pictorially, and in which many operations
can be performed by ‘pointing and clicking’, an approach pioneered in the
early 1970s by the Xerox Corporation, and long familiar to Macintosh users.
The pointing device is usually a mouse or atrackball; for many instructions,
equivalent typed commands can be used as well. We will use ‘mouse’ as the
generic term for whatever pointing device you may have. There are often
several ways to let the computer know what you want it to do. Here we will
usually emphasize how to do it with the mouse, because most users ﬁnd that
In what follows we will assume that Windows and Excel have been
installed in their complete, standard forms. For some applications we will
also use the Solver and the Analysis Toolpak. These come with Excel, but
(depending on the initial installation) may have to be loaded as an add-in.
When you start Windows, your monitor will show a screen (the desktop)
which typically displays, on its left side, a number of pictures (icons), each
with its own explanatory label. The bottom icon is labeled ‘Start’, and acts as
the onswitch of Windows. (There is no simple oﬀswitch, since Windows
requires a more elaborate turn-oﬀroutine, which rather illogically begins
with the Start button, and via the Shu
t Down command leads you to the Shut
Down Windows dialog box, where you can choose between several options.)
How to use Excel
Icons, such as the start label, are also called buttons, as if you could actu-
ally push them. Move the mouse so that the sharp point of the arrow on the
screen, the pointer, indeed ‘points to’ (i.e., is inside) the start button, and
press the left mouse button once. (Left and right depend, of course, on the
orientation of the mouse. By ‘left’ we mean the left button when the two or
three mouse buttons are pointing away from you, so that you can hold the
body of the mouse with your thumb and index ﬁnger, or with the palm of
your hand, while your index ﬁnger, middle ﬁnger, and ring ﬁnger can play
with the buttons.) To brieﬂy depress the left mouse button we will call to
clickthe mouse; when you need to do this twice in quick succession we will
call it double clicking, whereas brieﬂy depressing the right mouse button
we will call right clicking.
above it, showing you a a number of choices. . Manipulate e the e mouse so
that thearrow pointsto‘P
rograms’, whichwill nowbehighlighted,and
click.Asecond dialogbox will popupnextto theﬁrsttoshowyouthe
the spreadsheet. Alternatively, click on the Excel icon if the e desktop
A first look at the spreadsheet
After displaying the Excel logo, the monitor screen will show you a rather
busy screen, as illustrated in Fig. 1.2-1. The actual screen you will see may
have more bars, or fewer, depending on how the screen has been conﬁgured.
Please ignore such details for the moment; few if any of the instructions to
follow will depend on such local variations.
At the top of the screen is the title bar. In its right-hand corner are
three icon buttons, to minimizethe screen to near-zero size, to restoreit to
medium or full size, and to closeit. To the left on the same bar you will ﬁnd
the Excel logo and the name of the ﬁleyou use, where ‘ﬁle’ is the generic
name for any unit in which you may want to store your work. Below the title
bar is the menu bar(with such menu headings as F
etc.). This is usually followed by a standard barwith icons (pictograms
showing an empty sheet, an opening ﬁle folder, a diskette, a printer, etc.) and
a formula bar. At this point, the latter will show two windows, of which the
larger one will be empty.
Starting from the bottom of the screen and moving upwards, we usually
ﬁrst encounter the task bar, which has the Start button in its left corner.
Next to the start button you will ﬁnd the name of the Workbook you are
using. When you have not yet given it a name, Excel will just call it Book1,
Book2, etc. Above the task bar is the status bar, which may be largely empty
1.2 A first look at the spreadsheet
What we have described so far is the frame aroundthe actual spreadsheet.
Now we come to the spreadsheet itself, which is called a workbook, and is
organized in diﬀerent pages.
Above the status bar you will ﬁnd a tab, in Fig. 1.2-2 labeled Sheet1, which
identiﬁes which page of the work book is open. Here, then, you see the
general organization of individual spreadsheet pages into workbooks. You
can have as many pages in your workbook as you wish (by adding or remov-
ing sheets), and again as many diﬀerent workbooks as you desire. For the
exercises in the present text, you may want to use a new sheet for each exer-
cise, and a new workbook for each chapter, and label them accordingly.
In the region between the formula bar and the status bar you will ﬁnd the
actual working part of the spreadsheet page. It starts at the top with a
sequence of rectangles, each containing one letter of the alphabet on a gray
background. It ends, at the bottom, with a bar containing a series of tabs;
How to use Excel
Fig.1.2-1:The left top corner of the spreadsheet.
Fig.1.2-2:The left bottom corner of the spreadsheet.
one such tab, such as the one labeled Sheet1 in Fig. 1.2-2, will have a white
background, indicating the currently open (or ‘active’) sheet, while the
others will be gray. In between these is a rectangular array of blank cells.
Each such cellcan be identiﬁed by its (vertical) column and its (horizontal)
row. Columns are labeled by the letters shown just above row 1 of the
spreadsheet, while rows are labeled by the numbers shown to the left of
column A. The cell at the top left of the spreadsheet is labeled A1, the one
below it A2, the one next to A2 is B2, etc. One cell will be singled out by a
heavy black border; that is the highlighted, active cell in which the spread-
sheet anticipates your next action. The addressof the active cell is displayed
in the left-most window of the formula bar; in Fig. 1.2-1 it is cell A1.
To activateanother cell, move the mouse so that the pointer, which should
now have the shape of a hollow cross, is within that cell, then click. The corre-
sponding cell coordinates will show on the left-most window of the formula
bar. When you move the mouse pointer to another cell and click again, that
cell will now become the active one. Note that the left-most window in the
formula bar will track the coordinates of the active cell. Play with moving the
active cell around in order to get a feel for manipulating the mouse.
A cell can also be speciﬁed by typing its coordinates. The simplest way to
do so is by using the function keylabeled F5. (The function keys are usually
located above the regular alphabet and number keys, and labeled F1
through F10 or F12. On some keyboards they are found to the left of the
alphabet keys.) A dialog boxwill appear, and you just type the coordinates of
the cell, say, D11, and deposit this by depressing the large ‘enter’ key (to the
right of the regular alphabet keys). Another way, initially perhaps more con-
venient for those used to DOS-based spreadsheets, is to use the keystroke
sequence Alt+e Alt+g. Here Alt+e denotes that you depress Alt and then,
while keeping Alt down, also depress e; follow this by Alt+g. Alt speciﬁes that
you want to select an item from the menu bar, e selects the E
and g the G
o to command, where the underlining indicates the letter to be
used: e in E
dit, g in G
o, o in Fo
rmat, etc. As a gesture to prior users of Lotus 1-
2-3 or QuattroPro, you can even use the slant instead of the Alternate key: /+
e /+g . Any of the above methods will produce the dialog box in which to
type the cell coordinates.
Below we will usually indicate how to accomplish something by using the
mouse. For those more comfortable with using the keyboard rather than the
mouse, keystrokes to accomplish the same goals are often available. There is
no need to memorize these commands: just look for the underlined letters
to ﬁnd the corresponding letter code. Using keystrokes is often faster than
pointing-and-shooting with a mouse, especially when you use a track ball.
Note that, inside the cell area of the spreadsheet, the mouse pointer
usually shows as a cross. Select a cell, then move the pointer away from it
and back again. You will see that, near the border of the active cell, the
pointer changes its shape and becomes an arrow. When the pointer shows
1.2 A first look at the spreadsheet
as an arrow, you can depress the left mouse button and, while keeping it
down, move the pointer in the cell area. You will see that this will dragthe cell
by its border. By releasing the mouse button you can deposit the cell in a new
location; the formula bar will then show its new coordinates.
Practice activating a set of neighboring, contiguous cells; such cell blocks
or arraysare often needed in calculations. Move your mouse pointer to a
particular cell, say cell F8, and click to activate it. You can now move
thepointer away, the cell remains active as shown by its heavy border; also,
the formula bar shows it as the active cell regardless of where you move the
mouse pointer, as long as you don’t click. Return the pointer to cell F8, and
depress the left mouse button withoutreleasing it, then (while still keeping
the cell button down) move the mouse pointer away from cell F8 and slowly
move it in a small circle around cell F8. You are now outlining a cell block; its
size is clear from the reverse color used to highlight it (it will show as black
on a white background, except for the cell with which you started, in this
example F8, which will remain white, and which we will call the anchor cell).
The size of the block will show in the formula bar in terms of rows and
columns, e.g., 3R×2C will denote a block three rows high and two columns
wide. By releasing the mouse button you activate the entire block, while the
formula bar will return to showing the location of the anchor cell. You can
then move away from it; the active block will remain. After you have selected
the cell block, go back to it, grab its border (when the pointer is an arrow)
and move the entire block around! To deposit the block in a new location,
just release the mouse button. To abolish a block, release the mouse button
to deposit it, then move the pointer to another cell and click on it.
To activate a block of cells from the keyboard, use F5 (or Alt+e Alt+g),
then specify the block by the coordinates of its upper left cell and of its lower
right cell, separated by a colon, as in D4:E9, and deposit it with the enter key.
There is yet another way to activate a block, starting from a single active
cell. Again move the mouse pointer outside the active cell, but now
approach the small square in the right bottom corner of the border around
the active cell; this little square is the cell handle. The mouse pointer will
change into a plus sign when it points to the cell handle; you can then drag
the cell by its handle(rather than by its border) and make either columns or
rows. Again, ﬁx your choice by releasing the mouse button. You can drag it
again to make a block out of a row or column. Practice these maneuvers to
familiarize yourself with the mouse, and see how the pointer changes from a
hollow cross (when you point at the middle of the cell ) to an arrow (at its
border) to a plus sign (at its handle). Below we will speciﬁcally indicate when
to use the cell border or the cell handle; if nothing is speciﬁed, go to the
center of the cell and use its standard pointer, the hollow cross.
How to use Excel
Documents you may be interested
Documents you may be interested