Weekly Computer Tip # 73
24 July 2004

Switching between relative and absolute cell references (Microsoft Excel)

Look up the word "time" (using Google us described last week ;-) and it will tell you "A nonspatial continuum in which events occur in apparently irreversible succession from the past through the present to the future". As the definition suggests time is an irreversible succession. Time moves on and we have no control of time itself. Time is something we never seem to have enough of, which leads me on to this week's time-saving tip (written in spare time I don't have ;-) ...

When you create a formula and want to change relative references to absolute (or vice versa) you probably do that by typing a dollar sign ($) before each coordinate, for example $A$1. However,

Did you know ...

You can switch between relative and absolute cell references while building or editing the formula by using a shortcut key.

Here's how:

1. Select the cell that contains the formula.
2. In the formula bar, click on the cell reference you want to change.
3. Press the F4 key.

Each time you press F4, Excel toggles through the various combinations: absolute column and absolute row (for example, $A$1), relative column and absolute row (A$1), absolute column and relative row ($A1) and relative column and relative row (A1).

You can also switch between relative and absolute cell references while building the formula. Press F4 as soon as you have selected or typed the cell containing the value you want to change.

Save time -- and your sanity.

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer