Ways to Enter a Function into a Formula

You can enter a function into a formula by typing it manually or by using the Insert Function dialog box.

Entering a Function Manually

If you're familiar with a particular function-you know its correct spelling and the types of arguments that it takes-you may choose simply to type the function and its arguments into your formula. Often, this method is the most efficient.

A new feature in Excel 2007 is Formula AutoComplete. When you type an equal sign and the first letter of a function in a cell, Excel displays a drop-down list box of all the functions that begin with that letter and a ScreenTip with a brief description for the function (see Figure 4-1). You can continue typing the function to limit the list or use the arrow keys to select the function from the list. After the desired function is selected, pressing Tab inserts the function and its opening parenthesis into the cell.

Figure 4-1: When you begin to enter a function, Excel lists available functions that begin with the typed letters.

Note 

In addition to displaying function names, the Formula AutoComplete feature also lists names and table references.

At this point, Excel displays another ScreenTip that shows the arguments for the function. The bold argument is the argument you are currently entering. Arguments shown in brackets are optional. Notice that the text in the ScreenTip is hyperlinked. The hyperlinks appear when you move the mouse pointer over the function name or the arguments. The function name is hyperlinked to the help file that explains that function. Each argument that is entered or is the current argument is also hyperlinked. Clicking an argument hyperlink selects that argument in the cell.

If you omit the closing parenthesis for a function, Excel adds it for you automatically. For example, if you type =SUM(A1:C12 and press Enter, Excel corrects the formula by adding the right parenthesis.

Tip 

When you enter a function, Excel always converts the function's name to uppercase. Therefore, it's a good idea to use lowercase when you type functions. If Excel doesn't convert your text to uppercase when you press Enter, your entry isn't recognized as a function-which means that you spelled it incorrectly or the function isn't available. For example, it may be defined in an add-in not currently installed.

Using the Insert Function Dialog Box to Enter a Function

The Insert Function dialog box assists you by providing a way to enter a function and its arguments in a semi-automated manner. Using the Insert Function dialog ensures that you spell the function correctly and that it contains the proper number of arguments in the correct order.

To insert a function, select the function from the Insert Function dialog box, as shown in Figure 4-2. You access this dialog box by

• Choosing Formulas Function Library Insert Function

• Choosing Formulas Function Library AutoSum, and clicking More Functions in the drop-down list

• Clicking the fx icon to the left of the formula bar

• Pressing Shift+F3

Figure 4-2: The Insert Function dialog box.

When you select a category from the drop-down list, the list box displays the functions in the selected category. The Most Recently Used category lists the functions that you've used most recently. The All category lists all the functions available across all categories. Access this category if you know a function's name but not its category.

If you're not sure which function to use, you can search for a function. Use the field at the top of the Insert Function dialog box. Enter one or more keywords and click Go. Excel will display a list of functions that match your search criteria. For example, if you're looking for functions that are used for loan calculations, enter loan as the search term.

When you select a function in the Select a Function list box, notice that Excel displays the function (and its argument names) in the dialog box, along with a brief description of what the function does.

When you locate the function that you want to use, click OK. Excel's Function Arguments dialog box appears, as shown in Figure 4-3. Use the Function Arguments dialog box to specify the arguments for the function. You can easily specify a range argument by clicking the Collapse Dialog button (the icon at the right edge of each argument field). Excel temporarily collapses the Function Arguments dialog box to a thin box, so that you can select a range in the worksheet.

Figure 4-3: The Function Arguments dialog box.

The Function Library group on the Formulas tab contains several drop-downs for often- used function categories. If you select a function from one of these drop-downs, Excel skips the Insert Function dialog box and immediately displays the Function Arguments dialog box for the function selected.

Let Excel Insert Functions for You

Most of the time, you're on your own when it comes to inserting functions. However, at least three situations can arise in which Excel will enter functions for you automatically:

• When you choose Formulas Function Library AutoSum, Excel does a quick check of the surrounding cells. It then proposes a formula that uses the SUM function. If Excel guessed your intentions correctly, just press Enter to accept the proposed formula(s). If Excel guessed incorrectly, you can simply select the range with your mouse to override Excel's suggestion (or press Esc to cancel the AutoSum).

  You can preselect the cells to be included in an AutoSum rather than let Excel guess which cells you want. To insert a SUM function in cell A11 that sums A1:A10, select A1:A11 and click the AutoSum button.

  The AutoSum button displays an arrow that when clicked, displays additional functions. For example, you can use this button to insert a formula that uses the AVERAGE function.

• When you're working with a table (created by using Insert Tables Table), you can choose Table Tools Design Total Row, and Excel displays a new row at the bottom of the table that contains summary formulas for the columns. See Chapter 9 for more information about tables.

• When you choose the Data Data Tools Outline Subtotal command, Excel displays a dialog box that enables you to specify some options. Then it proceeds to insert rows and enter some formulas automatically. These formulas use the SUBTOTAL function.

More Tips for Entering Functions

The following list contains some additional tips to keep in mind when you use the Insert Function dialog box to enter functions:

• Click the Help on This Function hyperlink at any time to get help about the function that you selected (see Figure 4-4).

  
  Figure 4-4: Don't forget about Excel's Help system. It's the most comprehensive function reference source available.

• If the active cell already contains a formula that uses a function, clicking the Insert Function button displays the Function Arguments dialog box.

• You can use the Insert Function dialog box to insert a function into an existing formula. Just edit the formula and move the insertion point to the location where you want to insert the function. Then open the Insert Function dialog box and select the function.

• If you change your mind about entering a function, click Cancel.

• The number of arguments used by the function that you selected determines the number of boxes you see in the Function Arguments dialog box. If a function uses no arguments, you won't see any boxes. If the function uses a variable number of arguments (as with the AVERAGE function), Excel adds a new box every time you enter an optional argument.

• On the right side of each box in the Function Arguments dialog box, you'll see the current value for each argument that's entered or the type of argument (such as text or number) for arguments yet to be entered.

• A few functions, such as INDEX, have more than one form. If you choose such a function, Excel displays the Select Arguments dialog box that enables you to choose which form you want to use.

• If you're entering a function manually and you need help remembering the function's arguments, type an equal sign and the function's name, and then press Ctrl+Shift+A. Excel inserts the function with descriptive placeholders for the arguments, as shown in Figure 4-5. You need to replace these placeholders with actual arguments.

  
  Figure 4-5: Press Ctrl+Shift+A to instruct Excel to display descriptive placeholders for a function.

• To locate a function quickly in the Function Name list that appears in the Insert Function dialog box, open the list box, type the first letter of the function name, and then scroll to the desired function. For example, if you select the All category and want to insert the SIN function, click anywhere on the Select a Function list box and press S. Excel selects the first function that begins with S. Keep pressing S until you reach the SIN function.

• If the active cell contains a formula that uses one or more functions, the Function Arguments dialog box enables you to edit each function. In the formula bar, click the function that you want to edit and then click the Insert Function button.