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.