Move Relative Formulas Without Changing References





Move Relative Formulas Without Changing References

figs/beginner.giffigs/hack61.gif

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.


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows