Changing Limited Relative References to Absolute

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:

  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.)
  4. Figure 1. The Replace tab of the Find and Replace dialog box.

  5. In the Find What box, enter a capital letter D.
  6. In the Replace With box, enter "$D" (without the quote marks).
  7. Click the Match Case check box.
  8. 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 Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating a Table of Contents from Heading Levels

If your document is any length at all, adding a table of contents is a nice touch. This tip demonstrates how easy it is ...

Discover More

Searching for Tabs

Tabs don't normally show up in your printed document, but Word allows you to still search for them. All you need to do is ...

Discover More

Incorrect Page Counts Shown in Status Bar

A common statistic to see on the status bar is what page number you are working on in your document. If the page numbers ...

Discover More

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!

More ExcelTips (ribbon)

Setting a Default for Shifting when Inserting

When you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you ...

Discover More

Deleting Rows before a Cutoff Date

If you are using Excel to work with data that is date-centered, you may want to delete some of the data before a specific ...

Discover More

Disabling Dragging and Dropping

Excel allows you to easily paste information into a worksheet, including through simply dragging and dropping the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.