Teach Yourself VISUALLY™ Microsoft® Office 2008 for Mac®

256

11

22

●

Excel switches the address to an

absolute cell reference.

4 Repeat Steps 2 and 3 to switch

any other cell addresses that you

require in the absolute reference

format.

5 Press .

1 Double-click the cell that contains

the formula you want to edit.

2 Select the cell reference you want

to change.

3 Press + .

Switch Between Relative and Absolute Cell References

You can make some formulas easier to copy by

switching to absolute cell references. When you

use a regular cell address — called a relative cell

reference — such as A1 in a formula, Excel

adjusts that reference when you copy the

formula to another location. To prevent that

reference from changing, you must change it to

the absolute cell reference format: $A$1.

See the tip on the following page to learn more about the

difference between relative and absolute cell references.

Switch to Absolute

Cell References

15_485033-ch13.indd 25615_485033-ch13.indd 256 4/23/09 12:25:07 AM4/23/09 12:25:07 AM

257

66

Manipulating Formulas and Functions

chapter

13

How do I return a cell address

back to a relative cell reference?

The + keyboard technique

actually runs the address through

four different reference formats. Press

+ once to switch to the absolute

cell reference format, such as $A$1. Press

+

again to switch to a mixed reference format that

uses a relative column and absolute row (A$1).

Press

+ a third time to switch to a mixed

reference format that uses an absolute column and

relative row ($A1). Finally, press

+ a fourth

time to return to the relative cell reference (A1).

What is the difference between

absolute cell references and

relative cell references?

When you use a cell reference in a

formula, Excel treats that reference

as being relative to the formula’s cell.

For example, if the formula is in B5 and it

references cell A1, Excel effectively treats A1 as the

cell four rows up and one column to the left. If

you copy the formula to cell D10, then the cell

four rows up and one column to the left now

refers to cell C6, so in the copied formula Excel

changes A1 to C6. If the original formula instead

refers to $A$1, then the copied formula in cell D10

also refers to $A$1.

●

Excel preserves the absolute cell

references in the copied formulas.

Excel adjusts the formula.

6 Copy the formula.

Note: See the section “Move or Copy a Formula”

to learn how to copy a formula.

15_485033-ch13.indd 25715_485033-ch13.indd 257 4/23/09 12:25:08 AM4/23/09 12:25:08 AM