Microsoft Office Tutorials and References
In Depth Information
Working with Formulas
Chapter 9
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:
Figure 9-6
Not using an absolute reference creates an
incorrect answer.
1. Click the cell in which you want to place the
formula answer.
Figure 9-5
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
row reference.
Creating an Absolute Formula
Reference
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 ($).
Keyboard Shortcut
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.
Tip
Compound formulas can also have
absolute references.
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
chapter.
Search JabSto ::




Custom Search