by Allen Wyatt
(last updated September 12, 2015)
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:
Figure 1. The Replace tab of the Find and Replace dialog box.
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 you figure out the second reference from a formula like the following?
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, and 2013.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When you import information originating in a different program, Excel may not do the best job at figuring out what ...Discover More
Importing data into Excel that was generated in other programs can have some interesting side effects. For instance, you ...Discover More
The easy way to get rid of spaces at the beginning or end of a cell's contents is to use the TRIM function. ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.