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
 
Search JabSto ::




Custom Search