Toggle Absolute/Relative Address

A useful extension to using the F4 key inside the formula box at the top of Excel to change a formula from a relative address (without dollar signs) to an absolute address (with dollar signs). Unlike the F4 key, this little utility allows you to select a whole range that contains formulas and to quickly convert them from absolute to relative or from relative to absolute. This can be useful if you want to move or copy a set of relative addresses without Excel adjusted their references. Block the area you wish to copy that contains the relative formulas, use this utility to convert them to absolute addresses, and then copy and paste the range into a new place on the worksheet. Likewise, if you have a range of formulas with absolute addresses, but you want to copy them so Excel adjusts the references to take account of the range’s positional shift in the worksheet, then first convert the addresses to relative using this same toggle utility before copying the range of formulas.