Entering Formulas into Your Worksheets

As I mentioned earlier, a formula must begin with an equal sign to inform Excel that the cell contains a formula rather than text. Excel provides two ways to enter a formula into a cell: manually or by pointing to cell references. The following sections discuss each way in detail.

Entering formulas manually

Entering a formula manually involves, well, entering a formula manually. In a selected cell, you simply type an equal sign (=) followed by the formula. As you type, the characters appear in the cell and in the Formula bar. You can, of course, use all the normal editing keys when entering a formula.

Using Formula AutoComplete The Formula AutoComplete feature in Excel 2007 makes entering formulas easier than ever. Here’s a quick walk-through that demonstrates how it works. The goal is to create a formula that uses the SUBTOTAL function to calculate the average value in a range named TestScores. 1. Activate an empty cell and type an equal sign (=) to signal the start of a formula. 2. Type the letter S, and you’ll get a list of functions and names that begin with S. This feature is not case-sensitive, so you can use either uppercase or lowercase characters. 3. Type the second letter, U. The list is filtered to show only functions and names that begin with SU. 4. SUBTOTAL is second on the list, so use the Down Arrow to highlight the function and press Tab. Excel adds the opening parenthesis and displays another list that contains options for the first argument for SUBTOTAL. 5. Use the Down Arrow to select AVERAGE and press Tab. Excel inserts 101, the code for calculating the average. 6. Type a comma to separate the next argument. 7. Type a T, and you get a list of functions and names that begin with T. You’re looking for TestScores, so narrow it down a bit by typing the second character (e). 8. Highlight TestScores and press Tab. 9. Finally, type a closing parenthesis and press Enter. Formula AutoComplete includes the following items (and each type is identified by a separate icon): Excel built-in functions User defined functions (Functions defined by the user through VBA or other methods) Defined Names (named using the Formulas Defined Names Define Name command). Enumerated Arguments (only a few functions use such arguments, and SUBTOTAL is one of them) Table structure references (used to identify portions of a table)

Entering formulas by pointing

Even though you can enter formulas by typing in the entire formula, Excel provides another method of entering formulas that is generally easier, faster, and less error-prone. This method still involves some manual typing, but you can simply point to the cell references instead of typing their values manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

1.	Move the cell pointer to cell A3.
2.	Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in the status bar (bottom left of your screen).
3.	Press the up arrow twice. As you press this key, Excel displays a faint moving border around cell A1, and the cell reference appears in cell A3 and in the Formula bar. In addition, Excel displays Point in the status bar.
4.	Type a plus sign (+). A solid-color border replaces the faint border, and Enter reappears in the status bar.
5.	Press the up arrow again, which puts the moving border around cell A2, and adds that cell address to the formula.
6.	Press Enter to end the formula.

Pasting range names into formulas

If your formula uses named cells or ranges, you can either type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically. Two ways to insert a name into a formula are available:

• Select the name from the drop-down list: To use this method, you must know at least the first character of the name. When you’re entering the formula, type the first character and then select the name from the drop-down list that appears.

• Press F3: This key displays the Paste Name dialog box. Select the name from the list and click OK (or just double-click the name). Excel will enter the name into your formula. If no names are defined, pressing F3 has no effect.

Figure 15-3 shows an example. The worksheet contains two defined names: Expenses and Sales. The Paste Name dialog box is being used to insert a name (Sales) into the formula being entered in cell B11.

Inserting functions into formulas

The easiest way to enter a function into a formula is to use the drop-down list that Excel displays while you type a formula. In order to use this method, however, you must know at least the first character of the function’s name.

Another way to insert a function is to use the Function Library group on the Formulas tab (see Figure 15-4). This is especially useful if you can’t remember which function you need. Click the function category (Financial, Logical, Text, etc.) and you’ll get a list of the functions in that category. Click the function you want, and Excel displays its Function Arguments dialog box. This is where you enter the function’s arguments. In addition, you can click the Help On This Function link to learn more about the selected function.

Yet another way to insert a function into a formula is to use Excel’s Insert Function dialog box (see Figure 15-5). You can access this dialog box in several ways:

• By using the Formulas Function Library Insert Function command.

• By clicking the Insert Function icon, which is directly to the left of the Formula bar. This button displays fx.

• By pressing Shift+F3.

The Insert Function dialog box shows a drop-down list of function categories. Select a category, and the functions in that category are displayed in the list box. To access a function that you’ve used recently, select Most Recently Used from the drop-down list.

If you’re not sure which function you need, you can search for the appropriate function by using the Search For A Function box at the top of the dialog box. Enter your search terms, click Go, and you’ll get a list of relevant functions. When you select a function in the Select A Function list box, 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 you want to use, highlight it and click OK. Excel then displays its Function Arguments dialog box, as shown in Figure 15-6. Use this dialog box to specify the arguments for the function. The dialog box will vary, depending on the function you’re inserting, and it will show one text box for each of the function’s arguments. To use a cell or range reference as an argument, you can enter the address manually or click inside the argument box and then select (that is, point to) the cell or range in the sheet). After you’ve specified all the function arguments, click OK.

Function entry tips

Following are some additional tips to keep in mind when you use the Insert Function dialog box to enter functions:

• 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 at which you want to insert the function. Then open the Insert Function dialog box (using any of the methods described above) and select the function.

• You can also use the Function Arguments dialog box to modify the arguments for a function in an existing formula. Click the function in the Formula bar and then click the Insert Function button (the fx button, to the left of the Formula bar).

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

• How many boxes you see in the Function Arguments dialog box depends on the number of arguments used in the function you selected. If a function uses no arguments, you won’t see any boxes. If the function uses a variable number of arguments (such as the AVERAGE function), Excel adds a new box every time you enter an optional argument.

• As you provide arguments in the Function Argument dialog box, the value of each argument is displayed to the right of each box.

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

• As you become familiar with the functions, you can bypass the Insert Function dialog box and enter the function directly. Excel prompts you with argument names as you enter the function.