Using Spreadsheets (ML/CB)
Ready to try your hand at creating a spreadsheet? It's not difficult, but there are a few tricks to learn. Read this section and you'll soon be using spreadsheets with style and verve, at least to the point where you can impress your friends and pets.
Spreadsheet Basics
Let's start with a few basics you'll use whenever you create a spreadsheet.
Moving around.
You can't put anything into a cell until you activate it. You can tell a cell is activated by looking at its border: An activated cell's border is thick or colored. You can activate a cell in two ways:
Position the mouse pointer (which will look like a fat plus sign [+]) over a cell and click once. You have to click—just moving the mouse pointer over a cell does not activate it.
Use a keyboard key to move to a cell. Here are the most commonly used keystrokes:
This key… Selects this cell… 
or 
The cell to the right of the current cell 
or 
The cell to the left of the current cell 
or
The cell below the current cell 
or
The cell above the current cell
Cell references.
When you select or activate a cell, the spreadsheet program reports the active cell's reference in the upper-left corner of the window. As mentioned earlier, the cell reference consists of the letter (or letters) of the cell's column and the number of the cell's row. (There are alternative ways to describe cell locations, but nobody uses them because they're confusing.) Although you can select more than one cell simultaneously by dragging through several cells at once, only one cell is active at a time. Remember, the active cell is the one with the border, the one where whatever you type will appear.
Cell ranges (SA/ML).
A group of cells is a range. You refer to a range by the addresses of the cells at its beginning and end, separated by a colon (Excel) or a pair of periods (AppleWorks). For example, A1:A5 describes the first five cells in the first column in Excel and A1..E5 is a five-by-five-cell grid in the upper-left corner of an AppleWorks spreadsheet.
Kinds of entries.
There are two kinds of entries: those you type in (values), and those that compute values (formulas).
Entering values.
Activate the cell into which you want to put a value, type what you want to appear in the cell, and then do one of the following:
Press
to enter the value. Depending on what spreadsheet program you are using, this may also activate the next cell down.Press
to enter the value and leave the same cell active.Press
to enter the value and activate the next cell to the right.Click the checkmark button near the top of the window. That's the Enter button and it works the same way as pressing
, entering the value and leaving the cell active.
 |
If you make a mistake while entering a value (or a formula, for that matter) you can start over by clicking the X button near the top of the window. That's the Cancel button. If you prefer keyboard shortcuts,
|
When You Enter Too Much … (CB/ML/DC)For long entries, the cell width determines what appears in the cell:
In most cases you can properly display lengthy numbers or text by making the column wider. Just drag the right boundary of the column heading at the top of the column. Another way you can make lengthy text fit in a cell is to turn on the word wrap feature for that cell. Both Excel and AppleWorks offer this feature. |
Editing entries.
You can change the contents of a cell in two ways:
Activate the cell and type something different into it.
Activate the cell, click in the formula bar (Excel) or entry bar (AppleWorks) at the top of the window where the cell's contents appear, and use standard word-processing techniques to edit what's there.
No matter how you edit an entry, don't forget to press 
or 
or click the Enter button to complete it.
Creating formulas.
Just putting numbers and words into neat rows and columns is fine, but spreadsheets are built to compute. You can tell your spreadsheet to add two cells, calculate sales tax, or figure out what day of the week it will be 100 days from now. In fact, you can do just about anything that involves math—and some things that don't. But to do these fancy things, you must know how to create formulas.
 | You must remember one thing to enter a formula successfully: All formulas start with an equal sign—no exceptions! |
To enter a formula, select the cell where you want the formula's results displayed, type an equal sign, type the formula, and complete the formula by pressing 
or
or clicking the Enter button.
Here's an example. Let's say you have a number in cell A1 and another number in cell A2. You want to add them and put the answer in cell A3. Select cell A3, type =A1+A2 (no spaces!), and press 
; the result appears in cell A3.
You do have other ways to enter the parts of formulas. For example, you can enter a cell reference in a formula by clicking the cell. To write the above formula by clicking, just click in cell A3, type an equal sign, click in cell A1, click in cell A2, and click the Enter button. The answer appears in cell A3.
 | This method is especially useful for preventing typing errors—the less you type, the less chance you have of making mistakes! |
As you may have noticed, the plus sign appears by default if you click a cell without first specifying an operator. What you may also notice is that if you forget to complete the formula by pressing
or 
or clicking the Enter button, any cell you click in is added to the formula in the formula bar or entry bar. That's why it's important to complete each entry properly before continuing to other cells.
Those Darned Error Messages (EC/ML)When you write a formula incorrectly, the spreadsheet program usually tells you by displaying an error message in a dialog box or within the cell. Here's a table of some of Excel's error messages—other spreadsheets offer similarly vague expressions.
|
Why Use Cell References? (ML)A well-constructed spreadsheet includes cell references in its formulas whenever possible to make modifications easier. The two samples here (Figures 10.8 and 10.9) illustrate how these references can simplify modifying spreadsheets. You can write formulas that include the percentage rate as a constant within the formula, like this: =B2*15%. Figure 10.8. To change the commission rate in this spreadsheet, you would have to edit the contents of the formulas in cells C2 through C8. That's seven changes! And if you forget to make a change, the spreadsheet will produce incorrect results.
But it's a lot more convenient to write formulas that reference a cell containing the percentage rate, like this: =B4*C1. Figure 10.9. In this spreadsheet, you'd only have to change the contents of cell C1 to recalculate all commissions in column C correctly. So you're making one change that's impossible to miss. Which method would you prefer?
|
2 + 3 + 5 * 10 = ? (EG/ML)The answer is 55. Why? Well, in spreadsheets, mathematical operations don't happen in their order of appearance—they occur in a specific order, which is shown in the table below. In the example above, the spreadsheet multiplies 5 by 10 first and then adds 2 and 3 to get 55.
To force an operation to occur first, put it in parentheses. In the example above, if you wanted to add 3 and 5 first, you would use the formula 2+(3+5)*10. The spreadsheet would first add 3 and 5 to get 8 then multiply that by 10, and then add 2, with the result of 82. See what a difference a couple of parentheses can make? |
Beyond the Basics
So far, we've given you enough information to get you started with just about any spreadsheet program. Here are a few additional techniques and concepts to consider as you hone your spreadsheet skills.
Functions (CB).
Remember high school math? No? Fortunately, the people who make spreadsheet software do remember, and they've loaded their programs with handy calculations called functions. Here are some of my favorites, the ones I use over and over again. You've seen some of them in action earlier in this chapter.
SUM sums (adds) a bunch of numbers. It's especially handy for totaling a column or a row.
AVERAGE calculates the average of a range of cells. As with most functions, it's a lot easier to use the function than to calculate averages yourself.
MAX looks at a range of cells and returns the largest value. You could do this by looking at the cells yourself, but the MAX function is faster, and it never makes mistakes.
MIN determines which cell in a range is the smallest.
IF—my favorite—gives you supreme power and flexibility. It evaluates a condition (such as “Is B10 greater than 5000?”) and performs a calculation (or returns a result) based on whether the condition is met (true) or not met (false). Creative use of this function can add intelligence to your spreadsheets.
PROPER changes the first character of text to a capital letter.
SIN—as in sine, not “Thou shalt not.” It's trigonometry, the high school math subject you understood either completely or not at all. Fortunately, spreadsheets excel (hey, a spreadsheet pun!) at trigonometry. If you have an angle, the sine is a function away. COS gives you cosines, and TAN produces tangents.
WEEKDAY returns the day of the week on which a certain date falls. The answer you'll get is a number from 1 to 7 representing the day of the week. (The actual result depends on the settings in the Date & Time control panel or System Preferences pane.)
SQRT—that's square root, not squirt—calculates square roots.
PMT figures out how much the periodic payments will be when you borrow a certain amount of money at a certain rate of interest for a certain length of time.
Copying formulas (ML).
You can create a spreadsheet like the expense sum-mary or loan amortization tables shown earlier without entering each formula down the columns. How? By copying similar formulas. The spreadsheet software changes cell references as necessary so the copied formulas are correct. Of course, this technique has its limitations (see the sidebar “Absolute References” below), so check the formulas you copy to make sure they're correct.
Formatting (ML).
Of course, all spreadsheet software offers the ability to format your spreadsheets and charts with fonts, colors, styles, borders—you name it. The spreadsheets shown throughout this chapter offer good examples. With a little creativity, you can make a spreadsheet look like a million bucks—even if it's reporting a $68 million loss.
Printing (ML).
When you print a spreadsheet, what emerges from your printer depends on several factors:
Did you specify a print area? A print area is the rectangular selection of cells that will print. If you don't specify a print area, most software will print the entire spreadsheet, inserting page breaks wherever necessary to get it all on paper.
Did you insert manual page breaks? You can specify where you want one page to end and the next to begin, to eliminate page-break surprises.
Did you set page orientation, margins, or scale? By fiddling around with these page-setup options, you can squeeze a relatively large spreadsheet onto standard-size paper—or magnify spreadsheet cells for use in a presentation.
Did you set print titles? If your spreadsheet is lengthy, you may want to print row or column headings as titles on each page. You must tell the spreadsheet software which columns or rows to use as titles.
These options vary from one spreadsheet package to another. Explore the Page Setup and Print dialog boxes of your spreadsheet software to see how their settings affect your printouts.
Absolute References (EG)You can use two kinds of references in your formulas: relative (as in D5) or absolute (as in $D$5). The only time the kind of reference matters is when you copy a formula. A relative reference changes relative to where you paste it. (For example, if a formula sums the four cells immediately above it, when the formula is copied to a new location, the pasted-in formula will sum the four cells immediately above its new location.) An absolute reference always refers to the same cell no matter where you paste it. This is probably the most complex concept you'll encounter in dealing with spreadsheets, but once you master it, it can help you quickly create error-free spreadsheets. |