40 Part I: Basic Information

h Values or text strings: Examples include 7.5 (a value) and “Year-End Results” (a string,
enclosed in quotes).
h Worksheet functions and their arguments: These include functions such as SUM or
AVERAGE and their arguments. Function arguments appear in parentheses, and provide
input for the function’s calculations.
h 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 Lotus 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 cells that are used in the formula. I discuss each of these methods in the following sections.

Entering a Formula 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 typing
a formula. After you insert the formula, press Enter.

When you type an array formula, you must press Ctrl+Shift+Enter rather than just Enter.
An array formula is a special type of formula, which I discuss 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.

06_475362-ch02.indd 40 4/14/10 9:18 PM

 Chapter 2: Basic Facts about Formulas 41

Entering a formula 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 typing them 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 left side of the status bar.
3. Press ↑ 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.
4. 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).
5. Press ↑ one more time. A2 adds to the formula.
If you prefer, you can use your mouse and click cell A2.
6. Press Enter to end the formula.
Like with typing 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.

This method might sound a bit tedious, but it’s actually very efficient once you get the hang of it.
Pointing to cell addresses rather than entering them manually is almost always faster and 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.

06_475362-ch02.indd 41 4/14/10 9:18 PM

 42 Part I: Basic Information

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

h Press F3 to display the Paste Name dialog box. Select the name and click OK.
h Take advantage of the Formula AutoComplete feature. When you type a letter while con-
structing a formula, Excel displays a list of matching options. These options include func-
tions 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 for-
mula that contains spaces and line breaks.

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

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

Formula limits
A formula can consist of up to about 8,000 characters. In the unlikely event that you need to cre-
ate 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 Visual Basic for Applications (VBA).

Part VI focuses on creating custom functions.

06_475362-ch02.indd 42 4/14/10 9:18 PM

 Chapter 2: Basic Facts about Formulas 43

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.

h The following formula multiplies 150 × .01, returning 1.5. This formula uses only literal val-
ues, so it doesn’t seem very useful. However, it may be useful to show your work when
you review your spreadsheet later.
=150*.01

h This formula adds the values in cells A1 and A2:

=A1+A2

h The next formula subtracts the value in the cell named Expenses from the value in the cell
named Income:
=Income–Expenses

h The following formula uses the SUM function to add the values in the range A1:A12.
=SUM(A1:A12)

h 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

h This final formula subtracts the value in cell B3 from the value in cell B2 and then multi-
plies 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 might need to edit the formula to cor-
rect the error. You can edit your formulas just as you edit any other cell.
Here are several ways to get into cell edit mode:

h 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
selected on the Advanced tab in the Excel Options dialog box.

06_475362-ch02.indd 43 4/14/10 9:18 PM

 44 Part I: Basic Information

h 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 selected, the editing will occur in the
Formula bar.
h 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.

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.

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, type =SQRT(221), press F9, and then 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

06_475362-ch02.indd 44 4/14/10 9:18 PM