Page 146 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 131
24 Efficient Formula
Copying

Technique

opying formulas is undoubtedly one of the most basic and impor-
Save Time By tant functions that you perform when creating a new worksheet.
CGiven that, knowing how to make efficient copies of the formulas
Reviewing Excel’s differ-
ent cell references for you construct goes a long way towards making you an efficient spread-
formulas sheet designer.
Making efficient one- Whether you use the AutoFill feature or the cut-and-paste method to copy
dimensional copies your formulas, you don’t save any time if your basic formula doesn’t use
the correct type of cell references for the kind of copy you’re making.
Making two-dimensional
copies when possible Therefore, before revealing some tricks of the trade in making efficient
copies of your formulas, I think a quick review of the basic functions of
the types of cell references and when they’re used may be in order.

Going from Relative to Absolute

One reason why electronic spreadsheet programs such as Excel are so
popular is that they’re able to handle the cell references in formulas when
you make copies. The ability to populate the cells of your worksheet with
the appropriate calculations based on a single model formula copied in a
few seconds time is a tremendous boon to businesspeople everywhere.

As I’m sure you’re well aware, when you reference cells when building a
formula, Excel automatically makes those cell references relative, meaning
that both the row number and column letter of each cell address can be
adjusted in formula copies. Although the relative cell reference is the pro-
gram default, you can override this setting by making the cell reference
absolute so that neither the row number nor the column letter is adjusted
in formula copies. You can also convert to a mixed form of cell address
where the row number can’t be adjusted but the column letter can or one
in which the row number can be adjusted but the column letter can’t.

Excel indicates absolute elements in a cell reference by prefacing it
with a dollar sign so that $C$79 designates absolute column and row,
C$79 relative column and absolute row, and $C79 absolute column
and relative row. Use F4 to cycle a cell reference from relative to
absolute and through the two mixed modes.