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