Formulas, Functions, and Names

Formulas are what make a spreadsheet a spreadsheet. Excel has some advanced formula- related features that are worth knowing. They enable you to write array formulas, use an intersection operator, include links, and create megaformulas (my term for a lengthy and incomprehensible - but very efficient - formula).

CROSS-REFERENCE

Chapter 3 covers formulas and presents lots of tricks and tips.

Excel also has some useful auditing capabilities that help you identify errors or track the logic in an unfamiliar spreadsheet. To access these features, use the commands in the Formulas Formula Auditing group .

You may find the Formulas Formula Auditing Error Checking command useful. It will scan your worksheet and identify possibly erroneous formulas. In Figure 2-14, Excel identifies a possibly inconsistent formula and provides some options.

Figure 2-14: Excel can monitor your formulas for possible errors.

Worksheet functions enable you to perform calculations or operations that would otherwise be impossible . Excel provides a huge number of built-in functions.

The easiest way to locate the function that you need is to use the Insert Function dialog box, as shown in Figure 2-15. Access this dialog box by clicking the Insert Function button on the formula bar (or by pressing Shift+F3). After you select a function, Excel displays its Function Arguments dialog box, which assists with specifying the function's arguments.

Figure 2-15: The Insert Function dialog box is the best way to insert a function into a formula.

NEW	In Excel 2007, the Analysis ToolPak functions are now built-in. In other words, you can use these function even if the Analysis ToolPak add-in is not installed.

CROSS-REFERENCE

Excel also lets you create your own worksheet functions by using VBA. For details about this powerful feature, see Chapter 10.

A name is an identifier that enables you to refer to a cell, range, value, formula, or graphic object. Formulas that use names are much easier to read than formulas that use cell references, and it's much easier to create formulas that use named references.

CROSS-REFERENCE

I discuss names in Chapter 3. As you can see there, Excel handles names in some unique ways.