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

Hack 12 Outsmart Excel's Relative Reference Handler

In Excel, a formula reference can be either
relative or absolute, but sometimes you want to move cells that use
relative references without making the references absolute.
Here's how.

When a formula needs to be made
absolute, you use the 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, the formula will
reference the same cells. Sometimes, however, you already 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.

If you already set up your formulas using only relative references,
or perhaps a mix of relative and absolute references, you can
reproduce the same formulas in either another range on the same
worksheet, another sheet in the same workbook, or perhaps even
another sheet in another workbook.

To do this without changing any range
references inside the formulas, select the range of cells you want to
copy and then select Edit Replace.... In the Find What:
box, type an equals sign (=) and in the
Replace With: box, type an at sign
(@). (Of course, these
could be any symbols you are sure are not being used in any of the
formulas.) Click Replace All. The equals sign in all the formulas on
your worksheet will be replaced with the at sign.

You now can simply copy this range, paste it to its desired
destination, select the range you just pasted, and select Edit
Replace.... This time replace the at sign with an equals
sign. Your formulas now should be referencing the same cell
references as your originals.