Introduction to Spreadsheets
Spreadsheets are computational tools for doing calculations, plotting
data, and presenting numerical information. Spreadsheets originated in a
business and accounting environment, but their usefulness has extended to many
different disciplines including chemistry and other natural sciences.
You can use spreadsheets to record, manipulate, and plot experimental data.
Knowing how to use spreadsheets will be a very useful skill in your future
science courses. You can also use spreadsheets as a programmable
calculator in doing homework problems. For example, if you have several
problems that require the use of the quadratic formula, you might enter the
formula into your spreadsheet so that it gives the values of x when you enter
the values of a, b and c.
You can also use spreadsheets to model phenomena that can be described by a
mathematical equation. You can then plot the equation and study the behavior
under different conditions. You can change the values of the constants that
might be present in the equation in order to see the effect of that parameter.
This use of spreadsheets will be our primary focus in this course. Used
properly, such modeling enables the scientist to visualize better the
phenomena he or she is studying.
There are currently many different high powered spreadsheets that have good
graphical and presentation capabilities. These include programs such as
Lotus 1-2-3, Quattro Pro, Excel, Wingz and integrated packages such as
Microsoft Works and Claris Works. At Calvin College we use
Quattro Pro in the Windows and DOS environment and Wingz in the
Macintosh environment. Since Excel dominates the
Macintosh spreadsheet market, we will probably be switching to Excel
in the near future. For this course, however, we will be using Wingz.
The spreadsheet skills learned in one program are easily transferred to
Double-click on the Wingz icon. (Wingz is located on the
Limerick file server with the path: Limerick:/Mac Programs/General/Wingz
Folder/Wingz). In the Chemistry Department Macintosh Laboratory (S-346)
an alias or link to Wingz is in the Applications folder.
A new spreadsheet opens; it is a two-dimensional array of cells
with rows labeled with numbers and columns labeled with letters. Each cell is
uniquely identified by a combination of a letter and a number. For example,
the upper left cell is A1; the cell directly under it is A2.
The tool palette on the left side of the Wingz window gives you access
to the presentation capabilities of the program. These include the ability
to create text boxes and charts and line, arc, oval, rectangle and polygon
drawing tools. The top tool, the block plus sign, is the tool that must be
activated for normal spreadsheet functions. The second tool, the cross-hairs,
is the tool used to manipulate objects like text boxes or charts. The third
tool is the button tool that allows you to activate a script (a pre-programmed
series of commands) from the spreadsheet. Of these tools we will only be
using the chart tool (unless, of course, you want to get fancy on your own).
Text, Number and Formula Entries
There are three fundamental kinds of spreadsheet entries:
- Text Entry - Used mainly for labels. By default text is left
justified; the font is Geneva, the size is 10, and the style is Plain. These
defaults can be altered with commands in the Format menu.
- Number Entry - By default numbers are right justifed; the font,
size and style are the same as for text entries and can also be changed.
Numbers are given as decimals with two digits shown to the right of the
decimal point. The number of digits shown to the right of the decimal point
can also be altered with Precision... command in the Format menu.
Other number formats can be used: including scientific notation, various date
notations and various time notations.
- Formula Entry - In Wingz formula entries always begin with
an equal sign. For example, to multiple the value of cell A1 by 2 and
add 10 to it, you would write the formula =A1*2+10 into a cell (other than
A1). Wingz uses the "*" as the sign for multiplication and
"^" as the sign for taking the exponent. The order of operations in
Wingz is exponentiation, then sign changes, then multiplication and
division, and finally addition and subtraction. If the value of A1
was 15.00, the cell that you wrote the formula in would display 40.00.
Wingz also has a number of built-in functions that can be included
in formulas. These range from simple mathematical functions such as SQRT() or
EXP() to spreadsheet specific type functions such as SUM() or AVG(). The
functions can be accessed via the Go, Paste Function... command.
Relative and Absolute Cell References
Another feature of spreadsheets in general (and Wingz in particular)
that needs to be introduced at the outset is the distinction between relative
cell reference and an absolute cell reference. This distinction is relevant
only to formula
entries. It is often convenient to use the copy and paste features of
spreadsheet programs to copy a formula down a column or across a row. But
it is crucial to understand the way spreadsheets perform this copy command.
Suppose the value of cell A1 is 10.00. In cell A2 is the
formula =A1*2 (which would display 20.00). If you copy cell A2 with
the Edit, Copy command, and paste it into cell A3 with the
Edit, Paste command, what appears is not the formula =A1*2 (and the
value 20.00), but the formula =A2*2 (and the value 40.00). Try this if you
like. If you continue pasting down column A (by dragging through
several cells before using the Edit, Paste command) the reference that
was originally to cell A1 changes by one in each successive cell in the
This feature is especially useful with the kind of modeling that we
will be doing. For example, in column A we could place a column of numbers
that are the independent variable in our mathematical equation. In column B
we could write some function that depends on the independent variable. If
the formula refers to the first cell in column A, then when you copy and paste
the formula into the other cells of column B the reference to the independent
variable is automatically changed. The end result is two columns of numbers:
the first, the independent variables and the second, the value of the function
at each independent variable. The second column can be plotted as a function
of the first to represent the function graphically. (See the next introductory
document, Tips to XY Plotting with Wingz.
Sometimes it is necessary to override this default feature of spreadsheets, for
example, if there was a constant in your formula, but the value of the constant
was located in another cell. Suppose you were plotting a straight line with
with the formula y = mx + b, where m is the slope of the line and b is the
y-intercept. In column A write a series of numbers for x, the independent
variable. Cell B1 will contain the value for m, the slope and B2
the value for b, the y-intercept. In column C write the following formula:
=B1*A1+B2. If this formula is copied down in all the cells of column C, then
because of relative cell referencing, not only will A1 be changed to
A2 to A3,
etc. as desired, but B1 will be changed to B2 to B3, etc.
and B2 will be
changed to B3 to B4, etc. Of course, this is not what we want
since B1 and B2 are constants for the equation. One solution is
to put the value for
m and b explicitly into the formula. Sometimes this is the best solution.
Another solution is to use absolute cell references. An absolute cell
reference is not changed during a copy and paste action. In Wingz an
absolute cell reference is made by using the dollar sign ($) symbol. Thus, a
reference to B1 would be $B$1. (It is possible to keep only the
column of the cell reference absolute by placing the $ before the letter in
($B1) or to keep only the row of the cell reference absolute by placing the
$ before the number in the reference (B$1). The proper formula in the previous
example is =$B$1*A1+$B$2. Now if the formula is copied down column C only
the reference to cell A1 is changed.
Terry M. Gray
Last modified on November 11, 1995