If you make a mistake when you’re typing in a formula, Excel’s error-checking buttons attempt to return you to the straight and narrow. For example, if you type =suum E3:E6, Excel displays “#NAME?” in the cell. Click the cell to display the error-checking button, as shown in Figure 12-18. Clicking the tiny arrow on the right of the button displays several options and bits of information, like the following:

To enter a simple formula that you know well, just double-click the cell and start typing (or click the Edit box in the Formula bar, shown in Figure 12-18, and typed there). The cursor appears simultaneously in the cell and in the Edit box, signaling that Excel awaits your next move.

Your next move is to type an equal sign (=), since every formula starts that way. Then type the rest of the formula using values and operators. When you want to incorporate a reference to a particular cell in your formula, you don’t actually have to type out B12 or whatever—just click the cell in question. Similarly, to insert a range of cells, just drag through them.

To complete a formula press Enter, Return, Tab, or an arrow key—your choice.

Screen tips (Figure 12-19) for function help are a real boon to spread-sheeting neophytes and dataheads alike. As you start to type a function into a cell, AutoComplete pops up a list of function names matching what you’ve typed so far. Click one to add it to the cell. Then a screen tip displays the syntax of the function in a pale yellow box just below where you’re typing. Not only does the screen tip show you how to correctly type the function it believes you have in mind, but you can also use the screen tip in other ways. For example, you can drag the screen tip to reposition it (to get a better look at your worksheet), click a piece of the tip to select it, or click the function to open up its Help topic in a separate window.

Figure 12-19. Excel’s Function screen tips make an educated guess at what you’re trying to do—usually a pretty darn good one—and provide the correct syntax for doing it. Here the SUM screen tip explains how to add a series of numbers.

You don’t need access to Microsoft’s reams of focus-group studies to realize that the most commonly used spreadsheet function is adding things up. That’s why Excel comes equipped with a toolbar button that does nothing but add up the values in the column directly above, or the row to the left of, the active cell, as Figure 12-20 shows. (The tutorial that resumes on “Tutorial 2: Yearly Totals” also shows why AutoSum is one of the most important buttons in Excel.)

Figure 12-20. The powerful AutoSum button on the Standard toolbar (upper right) is the key to quickly adding a row or column. A click of the button puts a SUM function in the selected cell, which assumes that you want to add up the cells above it. Note that it doesn’t write out C3+C4+C5+C6+C7+C8+C9, and so on; it sets up a range of numbers using the shorthand notation C3:C13. When you press Return, you see only the result, not the formula.

The flippy triangle to the right of the AutoSum button reveals a menu with a few other extremely common options, such as the following:

Whipping up the sum or average of some cells is only the beginning. Excel is also capable of performing the kinds of advanced number crunching that can calculate interest rates, find the cosine of an arc, find the inverse of the one-tailed probability of the chi-squared distribution, and so on. It’s safe to say that no one has all of these functions memorized.

Fortunately, you don’t have to remember how to write each function; save that brainpower for the Sunday Times crossword. Instead, you can use Excel’s new Formula Builder to look up the exact function that you need. To call up the Formula Builder, click the fx button on the formula bar, choose View → Formula Builder, or click the Toolbox button on the Standard toolbar and then click the fx button .

The new Formula Builder takes the place of Excel 2004’s Paste Function dialog box and greatly simplifies finding and inserting functions. The top of the Formula Builder displays a search field (see Figure 12-21) where you can type any bit of information relating to the function you’re looking for. You can type cosine, for example, and the Formula Builder displays in its main panel the six functions, which somehow relate to cosine. Click any of the functions to read a brief description and the syntax for that function. Double-clicking the function does two things: it inserts the function in the active cell of your spreadsheet and opens the Arguments pane of the Format Builder where it displays the arguments (if any) it’s extracted from your spreadsheet, and at the bottom of the window displays the result.

Just like when you use the AutoSum button, if Excel guesses wrong and highlights the wrong cells in your spreadsheet, either readjust the selection rectangle or click the appropriate cells. Press Return to enter the function into your spreadsheet.

Figure 12-21. The Formula Builder (left) is Excel 2008’s welcome new addition to the Toolbox. You can focus your hunt for the correct one from among the hundreds offered by using the search field at the top of the window. Double-click a function to open the arguments pane, where you can fill in the arguments by clicking cells and making entries directly in this window. When complete, the Formula Builder displays the result at the bottom. To learn more about any specific function, for more assistance with the arguments, or to view sample examples, click “More help on this function” to summon the Excel Help window for that function (right).

What with all the operators, parentheses, cell addresses, functions and such—all of which have to be entered in exactly the correct order—assembling a formula can be a painstaking business. The new formula builder eases much of that pain, but you can choose another approach to formula creation: Excel’s built-in calculator.

Choose Tools → Calculator to summon this virtual pocket pal which features a lot of the standard buttons that you might find on a pocket calculator, plus parentheses buttons, an IF button (to insert an IF statement), and a SUM button (to insert a SUM function). As shown in Figure 12-22, the Calculator window also has three fields: a large one up top that displays the current formula (and also lets you type your own formula, if you’re so inclined) and two smaller ones below it, which show the answer to the formula and the formula’s destination cell.

Figure 12-22. The Calculator lets you build a formula with just a few button clicks—in this case, an IF statement that presents different messages depending on the value of B16. Once you’ve perfected the formula, click OK to insert it into the active cell.

To create a formula, click the calculator’s buttons as you would on a real calculator. If you prefer clacking on your keyboard, you can also enter the numbers that way. As you build your formula with the various calculator buttons, the formula shows up in the top window, and the result of the calculation shows up in the Answer field.

Once you’ve built your formula, click OK to paste it into the spreadsheet. (And show it off to your friends who use Excel for Windows, as they don’t yet have a calculator.)

If you want to access other functions (besides the IF and SUM functions), click More to bid the calculator adieu and return to the more knowledgeable Formula Builder.

Once your spreadsheet grows beyond the confines of your screen, you may find it difficult to find your way back to areas within it that you work on the most frequently. By designating a cell or group of cells as a named range, you can quickly jump to a certain spot without having to scroll around for it. You can use named cells as a quick way to navigate a large spreadsheet. Once you’ve created a named range, click the Name pop-up menu in the Formula bar and choose it from the list. Excel instantly transports you to the correct corner of your spreadsheet, where you’ll find the named range selected and waiting for you.

As you create formulas, you may find yourself referring over and over to the same cell or range of cells. For example, in the profit and loss spreadsheet (Figure 12-23), you may need to refer to the 2008 Total in several other formulas. So that you don’t have to repeatedly type the cell address or click to select the cell, Excel lets you give a cell, or range of cells, a name. After doing so, you can write a formula in the form of, for example, =Total2008-Taxes (instead of =B17-F27). Or, you may find yourself doing the same operation on the same range of cells over and over, for example, totaling or averaging your monthly expenses. By designating a monthly expense totals as a named range, you can create surprisingly readable formulas that look like =SUM(Expenses) or =AVERAGE(Expenses).

To create a named cell or range, simply select the cell or range, enter a name for it in the Formula bar’s Name box, and press Return (or choose Insert → Name → Define) as shown in Figure 12-24.

Figure 12-24. To name a cell or range of cells, select it, enter a name for it in the Formula bar’s Name box, and press Return. From now on, you can use the Name box pop-up menu to jump directly to that point in your spreadsheet.

From now on, the cell’s or range’s name appears in the Formula bar’s Name pop-up menu. The next time you want to go to that cell or range or use it in a formula, you need only click that pop-up menu and select it from the list. In addition, Excel displays the name instead of the cell address whenever you create a formula that refers to a named cell or range.

If you need to change or remove a name, choose Insert → Names → Define to display the Define Names dialog box. From there, you’ll find it easy to delete, create, or edit names.

When you create a formula by typing the addresses of cells or by clicking a cell, you’ve created a cell reference. However, rather than always meaning B12, for example, Excel generally considers cell references in a relative way—it thinks of another cell in the spreadsheet as “three rows above and two columns to the left of this cell,” for example (see Figure 12-25). In other words, it remembers those cell coordinates by their position relative to the selected cell.

Figure 12-25. The formula in cell C5 calculates the sales tax for the item priced in B5. The sales tax rate is stored in B1. Thus, the formula in C5 multiplies the price (B5) by an absolute reference to the sales tax rate (expressed $B$1). When you copy this formula, it always refers to the fixed cell B1.

Relative cell references also make formulas portable: When you paste a formula that adds up the two cells above it into a different spot, the pasted cell adds up the two cells above it (in its new location).

The yearly totals in Figure 12-23 show how this works. When you “filled” the Total formula across to the other cells, Excel pasted relative cell references into all those cells that say, in effect, “Display the total of the numbers in the cells above this cell.” This way, each column’s subtotal applies to the figures in that column. (If Excel instead pasted absolute references, then all the cells in the subtotal row would show the sum of the first-year column.)

Absolute references, on the other hand, refer to a specific cell, no matter where the formula appears in the spreadsheet. They can be useful when you need to refer to a particular cell in the spreadsheet—the one containing the sales tax rate, for example—for a formula that repeats over several columns. Figure 12-25 gives an example.

You designate an absolute cell reference by including a $ in front of the column and/or row reference. (For the first time in its life, the $ symbol has nothing to do with money.) For example, $A$7 is an absolute reference for cell A7.

You can also create a mixed reference in order to lock the reference to either the row or column—for example, G$8, in which the column reference is relative and the row is absolute. You might use this unusual arrangement when, for example, your column A contains discount rates for the customers whose names appear in column B. In writing the formula for a customer’s final price (in column D, for example), you’d use a relative reference to a row number (different for every customer), but an absolute reference to the column (always A).