2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)

In simplest terms, a function is a predefined formula. Many Excel functions are shorthand versions of frequently used formulas. For example, compare the formula =A1+A 2+A3+A4+A5+A6+A7+A8+A9+A10 with the formula =SUM(A1:A10). The SUM function makes the formula a lot shorter, easier to read, and easier to create. Some Excel functions perform complex calculations. For example, using the PMT function, you can calculate the payment on a loan at a given interest rate and principal amount.

All functions consist of a function name followed by a set of arguments enclosed in parentheses. (In the preceding example, A1:A10 is the argument in the SUM function.) If you omit a closing parenthesis when you enter a function, Excel adds the parenthesis after you press Enter, as long as it’s obvious where the parenthesis is supposed to go. (Relying on this feature can produce unpredictable results; for accuracy, always verify your parentheses.)

For more information about functions, see Chapter 14, “Using Functions.”

Using the Sum Button

No surprise-the SUM function is used more often than any other function. To make this function more accessible, Excel includes the Sum button on the Home tab on the Ribbon, which inserts the SUM function into a cell. (This button has an alter ego with identical functionality on the Formulas tab on the Ribbon, where it is called the Auto-Sum button.)

To see how this works, do the following:

1. Enter a column of numbers, like we did in Figure 13–7.

2. Select the cell below the column of numbers, and click the Sum button in the Editing group on the Home tab. The button inserts the entire formula for you and suggests a range to sum.

3. If the suggested range is incorrect, simply drag through the correct range, and press Enter.

Figure 13–7: Use the Sum button to add a summary formula in a cell adjacent to columns or rows of numbers.

The Sum button includes a menu that appears when you click the arrow next to the button, as shown in Figure 13–7. You can enter the AVERAGE, COUNT, MAX, or MIN function almost as easily as you can enter the SUM function-all it takes is an extra click to select the function you want from the Sum menu. The More Functions command opens the Insert Function dialog box, where you can access any Excel function. If you select a contiguous cell range that is adjacent to rows or columns of numbers before clicking the Sum button, Excel enters SUM functions in each cell.

For more information, see “Using the SUM Function” on page 461.

Inserting a Function

When you want to use a built-in function, click the Insert Function button on the Formulas tab on the Ribbon (or the little fx icon located on the formula bar). When you do so, the Insert Function dialog box shown in Figure 13–8 appears. For details about using the Insert Function dialog box, see “Inserting Functions” on page 458.

Figure 13–8: The Insert Function dialog box gives you access to all the built-in functions in Excel.

Using Formula AutoComplete

Excel 2007 makes it a little easier to create formulas with a new feature called Formula AutoComplete. Figure 13–9 illustrates what happens when you type an equal sign followed by the letter S-Excel lists all functions that begin with that letter. Formula AutoComplete also provides lists of defined names and function arguments, as well as special codes and names used in structured references and Cube functions.

Figure 13–9: When you start to type a function, Excel lists all the functions that begin with that letter or letters.

For more about defined names, see “Using Names in Formulas” on page 408; for more about structured references, see “Using Structured References” on page 420.

You can just keep typing your formula, or you can click any of the items in the AutoComplete list to see a pop-up description of what that function does. Scroll down the list to see more functions; to insert one of the functions into your formula, double-click it. You can type additional characters to narrow the list further. For example, typing =si in the example shown in Figure 13–9 would narrow the AutoComplete list to three functions: SIGN, SIN, and SINH. Formula AutoComplete also works within nested formulas. For example, if you started typing a formula such as =SUM(SIN(A4)_,S into a cell, the AutoComplete list would appear and readjust its contents for each letter you type in the formula.