In simplest terms, a function is a predefined formula. Many Excel functions are shorthand versions of frequently used formulas. For example, compare =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 with =SUM(A1:A10). The SUM function makes the formula shorter, easier to read, and easier to create. Most Excel functions perform much more complex calculations, like the PMT function, which lets you calculate a loan payment 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.)
Note
For more information about functions, see Chapter 13. For more about the SUM function, see Chapter 14.
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 AutoSum button.)
To see how this works, do the following:
Enter a column of numbers, like we did in Figure 12-7.
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.
Check the selection. If the suggested range is incorrect, simply drag through the correct range.
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, offering popular alternative functions you can choose instead. 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 a SUM function 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 41.
Note
For more information, see Using the SUM Function on page 537.
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, the Insert Function dialog box shown in Figure 12-8 appears. For all the details about using the Insert Function dialog box, see Inserting Functions on page 533.
Excel makes creating formulas a little easier with a 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. These lists appear automatically in the appropriate places as you enter a formula.
Note
For more about defined names, see Using Names in Formulas on page 483; for more about structured references, see Using Structured References on page 498.
Figure 12-9. When you start to type a function, Excel lists all the functions that begin with that letter or letters.
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. As you type additional characters, the list narrows 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 start typing a formula such as =SUM(SIN(A4),S into a cell, the AutoComplete list appears and readjusts its contents for each letter you type in the formula.