Changing Limited Relative References to Absolute

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:

  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 you 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, and 2013.

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

A Picture Is Worth a Thousand Words

Nothing beats a screen shot when you are trying to convey information about using the computer. With just a couple of easy ...

Discover More

Creating Add-Ins

Want to create your own add-in? Excel makes it easy to do. Here are all the steps you need.

Discover More

Writing On Top of Locked Graphics

Getting graphics to appear right where you want them in relation to the text in your document can be a challenge. One such ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Copying to Very Large Ranges

Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of cells ...

Discover More

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Counting Words

Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you can ...

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}] 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 four less than 9?

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.