Introduction to Spreadsheets

Introduction

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.

Different Spreadsheets

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 other programs.

Spreadsheet Basics

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:
  1. 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.
  2. 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.
  3. 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 column.

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 will contain 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 the reference ($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
grayt@calvin.edu

Last modified on November 11, 1995