You can save yourself a lot of manual editing, or even copying and pasting, in Excel by using the search and replace (Ctrl-h) feature. This will replace all occurrences of one value, or even a part of a formula, with a different value.
Say you defined a lookup range improperly, setting the rightmost column to H, when it should really have been column M, and your formula reads something like:
Hit Ctrl-h and supply :H for the Find text, and :M for the Replace text, then click Replace All.
Be careful about what you’re replacing though, or you could make a real mess. What would happen if you replaced H with M without specifying the leading colon character?
Look for opportunities to include unique character combinations when modifying formulas. That’s reason enough to put a little thought into how you name your worksheets, as the sheet names usually wind up as part of a formula somewhere along the line.
If you really must know which cells will change, expand the options and set the format to something that will stand out, like a red pattern color with a bold white font. You can often reset the formatting of those cells to their original formatting pretty easily with a few clicks.
