Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 61 Move Relative Formulas Without Changing References

In Excel, a formula reference can be either
relative or absolute. Sometimes, however, you might want to reproduce
the same formulas somewhere else in your worksheet or workbook, or on
another sheet.

When a formula needs to be made absolute,
type $ (a dollar sign) in front of the column
letter and/or row number of the cell reference, as in $A$1. Once you
do this, no matter where you copy your formula, it will reference the
same cells.

Sometimes, however, you might set up a lot of formulas that contain
not absolute references, but relative references. You would usually
do this so that when you copy the original cell formula down or
across, the row and column references change accordingly.

Yet other times you might set up your formulas using a mix of
relative and absolute references, and you want to reproduce the same
formulas in another range on the same worksheet, another sheet in the
same workbook, or perhaps another sheet in another workbook. You can
do all these things without changing any range references inside the
formulas.

Select the range of cells you want
to copy. Select Edit Replace..., and in the Find What:
box, type = (an equals sign). In the Replace
With: box, type @ (an at sign), or any other
symbol you are sure is not being used in any of the formulas. Click
Replace All.

All the formulas
will appear on your worksheet with an @ in place
of an =.

Now you can simply copy this range, paste it to the destination you
desire, select the range, and select Edit Replace.... This
time, replace the @ with an =.
Your formulas will reference the same cell references as your
originals.