Changing Limited Relative References to Absolute

Anne Marie has a range of cells containing formulas that utilize relative references. She wants to change the second reference in each formula from relative to absolute, so she wonders if there is a way to do this without the need to edit each cell's formula individually.

The answer to this question depends, entirely, on the characteristics of the formulas you are working with. If your data is arranged like most data that I've seen, you'll typically have a column that contains formulas based upon values in other columns and/or cells. You normally enter the formula at the top of the column and then copy that formula down the column for however many rows you need. (This could, of course, be turned 90 degrees so the formulas are across columns instead of down columns.)

If this describes the way your data is laid out, then all you need to do is edit the formula in the top cell of the column so it has the absolute reference that you want, and then press Enter. Select the cell again and then double-click the fill handle at the bottom-right of the cell. You end up with the modified formula copied down the column. The bottom line is that you only really needed to edit a single formula, not all the formulas individually.

If your data is laid out a bit differently (not in a column in the manner described), then you can try using Find and Replace to do the editing for you. First, however, you need to figure out if there is a consistent pattern in the second reference in the formulas you want to change. For instance, if you want to change all references to column D so that they are absolute, then you could proceed in this manner:

1. Select the range of cells that you want to edit.
2. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
3. Click the Options button, if it is available. (See Figure 1.)



Figure 1. The Replace tab of the Find and Replace dialog box.

4. In the Find What box, enter a capital letter D.
5. In the Replace With box, enter "$D" (without the quote marks).
6. Click the Match Case check box.
7. Click Replace All.

That should replace all the references to column D with $D, which indicates that the reference is absolute. You should know, though, that it will also replace any instances of a capital D in text values, plus if any of your formulas have $D in them already, you'll end up with $$D. (You can get around this last potential problem by doing a second Find and Replace in which you find $$D and replace it with $D.) This approach will also change all references to column D, regardless of whether they are in the second reference or not.

If you absolutely only want to change the second reference in any formula, then you'll need to create a macro to do the work. The macro would need to examine cells containing formulas, pick out the second cell reference, and then make sure there were dollar signs in front of the row and column in the reference. This, however, is easier said than done; the tricky part is to pick out the second reference in the formula. To a macro, how could it figure out the second reference from a formula like the following?

=A23+SUM(B7:B19)/C15

Is the second reference B7, B19, B7:B19, or do you skip that reference entirely because it is technically a parameter for a function and instead use C15? Such determinations are usually best left to a case-by-case examination, which means that you are back to looking at and possibly editing each cell individually, without the help of a macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9897) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.