Entering Formulas

As you learned in Chapter 1, "Understanding Excel Worksheets (Spreadsheets)," every formula begins with an equal sign (=) and can include cell references, mathematical symbols (such as + and), and actual numbers . You enter a formula in the cells in which you want the formula's results displayed. Excel gives you two options for entering formulas: You can use the point-and-click technique or type formulas. These techniques are described in the following sections.

Using Point-and-Click Entry

The easiest and most foolproof way to enter a formula is to use the point-and-click approach. You simply start your formula with an equal sign, click cells to add their addresses to the equation, and type the required mathematical symbols. The following instructions lead you step-by-step through the process:

1. Select the cell in which you want the formula's result to appear.

2. Type the equal sign ( = ).

3. Click the cell whose address you want to appear first in the formula. The cell address appears in the formula bar.

4. Type a mathematical operator after the value to indicate the next operation you want to perform. For example, type + to add the next entry, to subtract, * to multiply, or / to divide by.

5. Continue clicking cells and typing operators until you finish entering the formula, as shown in Figure 6.1. (Remember to group operations using parentheses, if necessary, to control the order of operations.)

   Figure 6.1. The easiest way to compose formulas is to point and click.

   

6. When you finish, press Enter to accept the formula.

Caution

If an error message appears in the cell in which you typed a formula, make sure you did not enter a formula that told Excel to do one of the following: divide by 0 or a blank cell, use a value from a blank cell, delete a cell being used in a formula, or use a range name when a single cell address was expected. For more details about formula error messages, see "Troubleshooting Errors in Formulas," later in this chapter.

Typing Formulas Manually

Though point-and-click formula entry is the best error-free method of constructing formulas, typing is typically faster. To type a formula, click the cell in which you want the formula's result to appear, and then type the formula, starting with an equal sign, and press Enter . To use a value from another cell in your formula, type the cell's address. For example, if cell H3 contains your monthly income and you want to insert your annual income in cell H4, you would type =H3*12 . This formula tells Excel to multiply the value in cell H3 by 12 and display the result.

Displaying Formulas

When you enter a formula, Excel displays the result of that formula in the cell. When you're checking your worksheet for errors, however, you may want to view the formulas rather than their results. In such cases, you can switch to formula-display mode by pressing Ctrl+` (hold down the Ctrl key while pressing the grave accent key). Excel not only displays the formula, but also highlights the cells referenced in the formula, as shown in Figure 6.2. Press Ctrl+` to switch back to displaying the results. (For more information on viewing and troubleshooting formulas, see "Troubleshooting Errors in Formulas," later in this chapter.)

Figure 6.2. In formula-display mode, Excel displays formulas, rather than their results, in cells.

Tip

Name the cells you want to refer to in your formula. You can then use the cell names in your formulas rather than use cryptic cell references. For example, if your worksheet has a cell named Income that displays your total income and another cell named Expenses that contains your total expenses, you can enter the formula =IncomeExpenses to determine your net profit. See "Naming Cell Ranges" in Chapter 4, "Entering and Editing Labels and Values."

Note

To view the formula in a cell when formula-display mode is off, click the cell whose formula you want to view. Excel displays the formula in the formula bar.