Microsoft Office Tutorials and References

In Depth Information

**How Excel Formats Cells That Contain Cell References**

Creating a Basic

Formula

Every cell reference points to another cell. For example, if you want a reference that

points to cell A1 (the cell in column A, row 1), then use this cell reference:

=A1

In Excel-speak, this reference translates to “get the value from cell A1, and insert it in

the current cell.” So if you put this formula in cell B1, then it displays whatever value’s

currently in cell A1. In other words, these two cells are now linked.

Cell references work within formulas just as regular numbers do. For example, the

following formula calculates the sum of two cells, A1 and A2:

=A1+A2

Note:
In Excel lingo, A1 and A2 are
precedents
, which means another cell needs them to perform a

calculation. Cell B1, which contains the formula, is called the
dependent
, because it depends on A1 and A2

to do its work. These terms become important when you need to hunt for errors in a complex calculation

using Excel’s error-checking tools (page 476).

Provided both cells contain numbers, you’ll see the total appear in the cell that

contains the formula. If one of the cells contains text, then you’ll see an error code

instead that starts with a # symbol. Errors are described in more detail on page 475.

Gem in the roUGh

Excel As a Pocket Calculator

Sometimes you need to calculate a value before you enter

it into your worksheet. Before you reach for your pocket

calculator, you may like to know that Excel lets you enter a

formula in a cell, and then use the result in that same cell.

This way, the formula disappears and you’re left with the

result of the calculated value.

the result if you change those other cells’ values. That’s the

difference between a cell that has a value, and a cell that

has a formula.

Excel has a similar trick that’s helpful if you want to take

a whole batch of formulas (in different cells), and replace

them all with values. It’s the Paste Values command. To

try it out, select the cells that have the formulas you want

to change, copy them (Home➝Clipboard➝Copy), and

then paste them somewhere in your worksheet using the

Home➝Clipboard➝Paste➝Paste Values command. The

pasted cells now have the numbers, not the formulas.

Start by typing your formula into the cell (for example

=65*88
). Next, press F9 to perform the calculation. Finally,

just hit Enter to insert this value into the cell.

Remember, when you use this technique, you replace your

formula with the calculated value. If your calculation is

based on the values of other cells, then Excel won’t update

How Excel Formats Cells That Contain Cell References

As you learned in Chapter 16, the way you format a cell affects how Excel displays

the cell’s value. When you create a formula that references other cells, Excel attempts

to simplify your life by applying automatic formatting. It reads the number format