Vertex42 The Excel Nexus

Formulas are what make a spreadsheet a spreadsheet. Excel has some important 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).

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, select Tools ^ Formula Auditing.

Beginning with Excel 2002, the auditing features have been enhanced significantly. For example, you can now use these features to scan your worksheet and identify possibly erroneous formulas. In Figure 2-4, Excel identified a possibly inconsistent formula, and a Smart Tag provides you with some options.

ÜBookl |
1- nlxl | ||||

A B 1 C |
D E |
F |
G ~~l | ||

1 |
J32.0 (64.0 £80. G |
(96.0 |
J_I_S | ||

2 |
J15.0 (30.0 137.5 |
(45.0 | |||

3 |
(21.0 (42.0 152.5 |
(63.0 | |||

4 |
(65.0 (130.0 190.0 |
(195.0 | |||

5 |
(69.0 (138.0 (172.5 |
(207.0 | |||

6 |
(75.0 (150.0 (187.5 |
(225.0 | |||

7 |
(102.0 (204.0 (255.0 |
(306.0 | |||

( |
<ft .}' (694.01 (875.0 |
11,137.0 | |||

9 |
1 |
1 | |||

10 |
Inconsistent 1 mi null.i | ||||

11 |
i~Y.nu 1, fmm 1 aft | ||||

12 | |||||

13 |
| Help on this error | ||||

14 |
ignore Error Edit in Formula Birr | ||||

15 | |||||

16 | |||||

17 |
Error Checking Options... | ||||

18 |
iliniAi F ai nui l.i AiiilitiiiiiTonlli.il | ||||

13 |
» — 1 | ||||

_in | |||||

20 |
■ | ||||

21 |
1 | ||||

22 | |||||

23 | |||||

24 | |||||

M i |
► ► |
\sheet2 / 5heetl / |
M |
_1 |

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

Figure 2-4: 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, and you can access even more functions (many of them quite esoteric) by attaching the Analysis ToolPak add-in.

The easiest way to locate the function that you need is to use the Insert Function dialog box, as shown in Figure 2-5. Access this dialog box by clicking the Insert Function button on the formula bar (or by choosing Insert ^ Function or pressing Shift+F3). If you're not familiar with this feature, I encourage you to check it out. It's very handy.

Beginning with Excel 2002, you can identify a function by searching for a keyword. This is useful if you can't remember the name of the function. (The name is an identifier that enables you to refer to a cell, range, value, formula, or graphic object.) For example, if you're looking for the function that converts text to its ASCII code, you can search for code and then click Go. Excel will propose five functions: CODE, CHAR, CELL, TODAY, and CLEAN.

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 using cell references, and it's much easier to create formulas that use named references.

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

Was this article helpful?

## Post a comment