June 4, 2011, 2:42 p.m.

posted by donnie

### 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.

- Comment