Entering Formulas into Your Worksheets –
Every formula must begin with an equal sign to inform Excel that the cell contains a for-
mula 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 method in detail.
Excel provides additional assistance when you create formulas by displaying a drop-down
list that contains function names and range names. The items displayed in the list are
determined by what you’ve already typed. For example, if you’re entering a formula and
then type the letters SU, you’ll see the drop-down list shown in Figure below. If you type an
additional letter, the list is shortened to show only the matching functions. To have Excel
autocomplete an entry in that list, use the navigation keys to highlight the entry and then
press Tab. Notice that highlighting a function in the list also displays a brief description of
the function.
Entering formulas manually –
Entering a formula manually involves, well, entering a formula manually. In a selected cell,
you 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 of the normal editing keys when
entering a formula.
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 refer-
ences instead of typing their values manually. For example, to enter the formula =A1+A2
into cell A3, follow these steps:
- Select cell A3.
- Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in
the status bar (lower left of your screen). - Press the up arrow twice. As you press this key, Excel displays a dashed 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. - Type a plus sign (+). A solid color border replaces the dashed border of A1, and
Enter reappears in the status bar. - Press the up arrow again. The dashed border encompasses cell A2 and adds that
cell address to the formula. - Press Enter to complete 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 automati-
cally. Three 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. - Press F3. The Paste Name dialog box appears. Select the name from the list and
then click OK (or just double-click the name). Excel enters the name into your for-
mula. If no names are defined, pressing F3 has no effect. - Click the Use in Formula drop-down on the Formulas tab (Defined Names group).
This command is available while you are in edit mode, and it allows you to select
from the available range names.
Inserting functions into formulas –
The easiest way to enter a function into a formula is to use Formula AutoComplete (the
drop-down list that Excel displays while you type a formula). 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 tools in the Function Library group on the
Formulas tab on the Ribbon (see Figure below). This method is especially useful if you can’t
remember which function you need. When entering a formula, click the function category
(Financial, Logical, Text, and so on) to get a list of the functions in that category. Click
the function that 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 the Insert Function dialog box
(see Figure below). You can access this dialog box in several ways:
- Choose Formulas ➪ Function Library ➪ Insert Function.
- Use the Insert Function command, which appears at the bottom of each drop-down
list in the Formulas ➪ Function Library group. - Click the Insert Function icon, which is directly to the left of the Formula bar. This
button displays fx. - Press Shift+F3
The Insert Function dialog box shows a drop-down list of function categories. Select a cat-
egory, and the functions in that category are displayed in the list box. To access a function
that you recently used, 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 field at the top of the dialog box.
- Enter your search terms and click Go. You get a list of relevant functions. When
you select a function from the Select a Function list, 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 below.
3. Specify the arguments for the function. The Function Arguments dialog box will
vary, depending on the function that you are 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.
4. After you specify all of the function arguments, click OK.