Chapter 2: Basic Facts about Formulas | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

This chapter serves as a basic introduction to using formulas in Excel. Although I direct its focus on newcomers to Excel, even veteran Excel users may find some new information here.

Entering and Editing Formulas

This section describes the basic elements of a formula. It also explains various ways of entering and editing your formulas.

Formula Elements

A formula entered into a cell can consist of five element types:

Operators: These include symbols such as + (for addition) and * (for multiplication).
Cell references: These include named cells and ranges and can refer to cells in the current worksheet, cells in another worksheet in the same workbook, or even cells in a worksheet in another workbook.
Values or strings: Examples include 7.5 or "Year-End Results."
Worksheet functions and their arguments: These include functions such as SUM or AVERAGE and their arguments.
Parentheses: These control the order in which expressions within a formula are evaluated.

Entering a Formula

When you type an equal sign into an empty cell, Excel assumes that you are entering a formula because a formula always begins with an equal sign. Excel's accommodating nature also permits you to begin your formula with a minus sign or a plus sign. However, Excel always inserts the leading equal sign after you enter the formula.

As a concession to former 1-2-3 users, Excel also allows you to use an "at" symbol (@) to begin a formula that starts with a function. For example, Excel accepts either of the following formulas:

 =SUM(A1:A200) @SUM(A1:A200)

However, after you enter the second formula, Excel replaces the @ symbol with an equal sign.

You can enter a formula into a cell in one of two ways: Enter it manually, or enter it by pointing to cell references. I discuss each of these methods in the following sections.

ENTERING FORMULAS MANUALLY

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

Note

When you enter an array formula, you must press Ctrl+Shift+Enter rather than just Enter. I discuss array formulas in Part IV.

After you press Enter, the cell displays the result of the formula. The formula itself appears in the formula bar when the cell is activated.

ENTERING FORMULAS BY POINTING

The other method of entering a formula still involves some manual typing, but you can simply point to the cell references instead of entering them manually. For example, to enter the formula =A1+A2 into cell A3, follow these steps:

Move the cell pointer to cell A3.
Type an equal sign (=) to begin the formula. Notice that Excel displays Enter in the left side of the status bar.
Press the up arrow twice. As you press this key, notice that Excel displays a moving border around the cell and that the cell reference (A1) appears in cell A3 and in the formula bar. Also notice that Excel displays Point in the status bar.

If you prefer, you can use your mouse and click cell A1.
Type a plus sign (+). The moving border becomes a solid blue border around A1, and Enter reappears in the status bar. The cell cursor also returns to the original cell (A3).
Press the up arrow one more time. A2 adds to the formula.

If you prefer, you can use your mouse and click cell A2.
Press Enter to end the formula. Like with entering the formula manually, the cell displays the result of the formula, and the formula appears in the formula bar when the cell is activated.

If you prefer, you can use your mouse and click the check mark icon next to the formula bar.

Pointing to cell addresses rather than entering them manually is usually less tedious and is almost always more accurate.

Pasting Names

As I discuss in Chapter 3, you can assign a name to a cell or range. If your formula uses named cells or ranges, you can type the name in place of the address or choose the name from a list and have Excel insert the name for you automatically.

To insert a name into a formula, position your cursor in the formula where you want the name entered and use one of these three methods:

Press F3 to display the Paste Name dialog box. Select the name and click OK.
Choose Formula Use in Formula to display a drop-down list of names. Choose the name from the list, and it is inserted into the formula.
Take advantage of the formula autocomplete feature. When you type a letter while constructing a formula, Excel displays a list of matching options. These options include functions and names. Use the down-arrow key to select the name and then press Tab to insert the name in your formula.

Spaces and Line Breaks

Normally, you enter a formula without using any spaces. However, you can use spaces (and even line breaks) within your formulas. Doing so has no effect on the formula's result but can make the formula easier to read. To enter a line break in a formula, press Alt+Enter. Figure 2-1 shows a formula that contains spaces and line breaks.

image from book
Figure 2-1: This formula contains spaces and line breaks.

Tip

To make the formula bar display more than one line, drag the border below the formula bar downward.

Formula Limits

A formula can consist of up to about 8,000 characters. In the unlikely event that you need to create a formula that exceeds this limit, you must break the formula up into multiple formulas. You also can opt to create a custom function by using VBA.

Cross Ref

Part VI focuses on creating custom functions.

Sample Formulas

If you follow the above instructions for entering formulas, you can create a variety of formulas. This section provides a look at some sample formulas.

The following formula multiplies 150 × .01, returning 1.5. This formula uses only literal values, so it doesn't seem very useful. However, it may be useful to "show your work" when you review your spreadsheet later.
```
 =150*.01 
```
This formula adds the values in cells A1 and A2:
```
 =A1+A2 
```
The next formula subtracts the value in the cell named Expenses from the value in the cell named Income:
```
 =Income--Expenses 
```
The following formula uses the SUM function to add the values in the range A1:A12.
```
 =SUM(A1:A12) 
```
The next formula compares cell A1 with cell C12 by using the = operator. If the values in the two cells are identical, the formula returns TRUE; otherwise, it returns FALSE.
```
 =A1=C12 
```
This final formula subtracts the value in cell B3 from the value in cell B2 and then multiplies the result by the value in cell B4:
```
 =(B2-B3)*B4 
```

Editing Formulas

If you make changes to your worksheet, you may need to edit formulas. Or if a formula returns one of the error values described later in this chapter, you need to edit the formula to correct the error. You can edit your formulas just as you edit any other cell.

Here are several ways to get into cell edit mode:

Double-click the cell. This enables you to edit the cell contents directly in the cell. This technique works only if the Double-click Allows Editing Directly in Cell check box is enabled on the Advanced tab of the Excel Options dialog box.
Press F2. This enables you to edit the cell contents directly in the cell. If the Double- click Allows Editing Directly in Cell check box is not enabled, the editing will occur in the formula bar.
Select the formula cell that you want to edit and then click in the formula bar. This enables you to edit the cell contents in the formula bar.

When you edit a formula, you can select multiple characters by dragging the mouse over them or by holding down Shift while you use the arrow keys. You can also press Home or End to select from the cursor position to the beginning or end of the current line of the formula.

Using the Formula Bar as a Calculator

If you simply need to perform a calculation, you can use the formula bar as a calculator. For example, enter the following formula into any cell:

 =(145*1.05)/12

Because this formula always returns the same result, you may prefer to store the formula's result rather than the formula. To do so, press F2 to edit the cell. Then press F9, followed by Enter. Excel stores the formula's result (12.6875), rather than the formula. This technique also works if the formula uses cell references.

This technique is most useful when you use worksheet functions. For example, to enter the square root of 221 into a cell, enter =SQRT(221), press F9, and press Enter. Excel enters the result: 14.8660687473185. You also can use this technique to evaluate just part of a formula. Consider this formula:

 =(145*1.05)/A1

If you want to convert just the expression within the parentheses to a value, get into cell edit mode and select the part that you want to evaluate. In this example, select 145*1.05. Then press F9 followed by Enter. Excel converts the formula to the following:

 =(152.25)/A1

Tip

Suppose you have a lengthy formula that contains an error, and Excel won't let you enter it because of the error. In this case, you can convert the formula to text and tackle it again later. To convert a formula to text, just remove the initial equal sign (=). When you're ready to return to editing the formula, insert the initial equal sign to convert the cell contents back to a formula.