MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

Worksheet functions are special tools that perform complex calculations quickly and easily. They work like the special keys on sophisticated calculators that compute square roots, logarithms, and statistical evaluations-except Microsoft Office Excel 2007 has hundreds of these special functions. Some functions, such as SIN and FACT, are the equivalent of lengthy mathematical formulas you would otherwise have to create by hand. Other functions, such as IF and VLOOKUP, can't be otherwise duplicated by formulas. When none of the built-in functions is quite what you need, you can create custom functions, as explained in Chapter 27, "Creating Custom Functions."

Using the Built-In Function Reference in Excel

While preparing this book, we had to make some tough choices. Fully describing each of the hundreds of worksheet functions would fill an entire book-or two, perhaps. To provide the greatest benefit, we had to decide which functions to focus on and which to mention only briefly. Admittedly, we tend to devote more ink to financial, information, and lookup functions than we do to engineering or trigonometric functions. We think this makes sense for the majority of our readers. If you need more information about functions that we do not cover in great detail, Excel offers several built-in resources:

• The online Help system The Excel Help system includes a detailed description of each worksheet function. Just press F1 to display the Excel Help window, and then type a function name in the Search text box to find all the relevant Help topics. You can also click Function Reference in the Table Of Contents, where the functions are grouped into categories to help you find the one you need. For example, clicking the Logical category and then the Logical Functions topic displays the information shown in Figure 13-1.

For more information, see "Using the Online Help System" on page 73.

• The Insert Function dialog box You can use this dialog box, shown in Figure 13-2, to browse through the entire list of functions if you're not sure which function you need. To display the Insert Function dialog box, click the Insert Function button on the formula bar.

• The Function Arguments dialog box This dialog box, shown in Figure 13-3, provides details about the function, and the required arguments appear as separate text boxes in the middle of the dialog box. Notice also the link to the relevant Help topic at the bottom of the dialog box. To display the Function Arguments dialog box, click the Insert Function button on the formula bar, select a function, and click OK. You can also click the Insert Function button while you are in the process of entering a formula after you type a valid function name and an open parenthesis to display the Function Arguments dialog box.

  Note

  Drag the Function Arguments dialog box around the screen if you need to see the cells behind it. For maximum viewing, make the dialog box smaller by clicking one of the collapse dialog buttons on the right side of the argument boxes.

• Function ScreenTips These little pop-up descriptions that appear below selected formulas are useful if you are unsure about the syntax of a function as you type a formula; you can get help without even leaving the cell. After you type the required open parenthesis following any valid function name, the appropriate ScreenTip appears, as shown in Figure 13-4. The ScreenTip shows you the correct function syntax and any available alternate versions of the function (also shown in Figure 13-4). You can also click the function name in the ScreenTip to display the relevant topic from the online Help system. If you click an argument name in the ScreenTip, the corresponding section of the formula is highlighted for you, making it easy to identify each argument, as shown in Figure 13-4.

  Note

  To turn off Function ScreenTips, click the Microsoft Office Button, click Excel Options, select the Advanced category, and then in the Display area, clear the Show Function ScreenTips check box.

• Formula AutoComplete As you type a formula, Excel provides pop-up lists that offer function names and defined names that match the letters you are typing in the formula. For example, if you type =S in a cell, Excel displays a scrolling list of all functions (and defined names, if any) that begin with the letter S that you can then double-click to insert into your formula.

Figure 13-1: The online Help system includes a comprehensive function reference.

Figure 13-2: The Insert Function dialog box provides assistance with using functions.

Figure 13-3: The Function Arguments dialog box provides assistance with entering function arguments.

Figure 13-4: Click an argument name in the Function ScreenTip, which appears when you click an existing function, to highlight the corresponding argument in the cell.

For details, see "Using Formula AutoComplete" on page 440 and "Naming Cells and Ranges" on page 441.