Using Functions: A Preview

In simplest terms, a Junction is a predefined formula. Many Excel functions are shorthand versions of frequently used formulas. For example, compare the formula =A1+A2+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 13, "Using Functions." For more about the SUM function, see Chapter 14, "Everyday 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.)

Note 

You can quickly enter a SUM function in the selected cell by pressing Alt+=.

To see how this works, do the following:

1. Enter a column of numbers, like we did in Figure 12-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 12-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 12-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.

Note 

Get a quick sum by selecting the cells you want to sum and then looking at the status bar, where Excel automatically displays the sum, the average, and the count (the total number of cells containing entries) of the selected range. Right-click the status bar to add more readouts for minimum, maximum, and numerical count. For more information, see "Quick Totals on the Status Bar" on page 45.

For more information, see "Using the SUM Function" on page 497.

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 12-8 appears. For details about using the Insert Function dialog box, see "Inserting Functions" on page 492.

Figure 12-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 12-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 12-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 442; for more about structured references, see "Using Structured References" on page 454.

You can just keep typing your formula, or you can click any of the items in the Auto-Complete 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 12-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.