Written by Allen Wyatt (last updated May 16, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 it 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, 2013, 2016, 2019, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...Discover More
When you are working with data created by other systems or other people, you often need to convert the data into ...Discover More
Excel makes it easy to select a group of contiguous cells. However, it also makes it easy to select non-contiguous groups ...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.