excel 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).

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. This command
scans your worksheet and identifies possibly erroneous formulas. In Figure 2-14, Excel identifies a
possibly inconsistent formula and provides some options.
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.