Microsoft Office Tutorials and References
In Depth Information
Working with Formulas
Working with Formulas and Functions
arrow below the Paste button shown in Figure 9-5
and choose Paste Values. Excel then pastes in only
the results, not the formula.
an erroneous retail price of 0. The original formula
was =E2*A2, where A2 is the mark-up percentage
rate. When the formula is copied down to the next
cell, it becomes =E3*A3, and the cell in A3 is not the
mark-up percentage rate cell. Use the following steps
to create an absolute reference:
Not using an absolute reference creates an
1. Click the cell in which you want to place the
Pasting in values.
2. Type the formula. If any references are to be
an absolute reference, add dollar signs ($) in
front of both the column reference and the
Creating an Absolute Formula
Occasionally when you copy a formula, you do
not want one of the cell references to change.
That’s when you need to create an absolute
reference . To indicate an absolute reference, you use
the dollar sign ($).
When typing the cell reference, press F4 to
automatically create the absolute reference.
It’s called an absolute reference because when you
copy it, it absolutely and positively stays that cell
reference and never changes. An example of a
formula with an absolute reference might be
=B22*$B$24. The reference to cell B24 will not
change when copied.
3. Press the Enter key. Excel displays the answer
in the cell.
Compound formulas can also have
Figure 9-6 shows a formula that is supposed to take
the cost of an item and multiply it to the mark-up
rate. The result is the retail cost of the item. The first
formula is fine, but, as you can see, when the
formula is copied or filled down, the other products display
4. Copy the formula to other cells using one of
the methods you discovered earlier in this