Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Relative References to Cells in Other Workbooks.
by Allen Wyatt
(last updated October 29, 2016)
When Stephen is creating a formula and refers to a cell in another worksheet in the same workbook, the reference is always relative (A1). However, when he refers to a cell in another workbook, the reference is always absolute ($A$1). Stephen wonders if there is a way of getting this to be automatically relative (A1) without having to go into the cell and press F4 three times.
The typical way to deal with this situation is to simply edit the reference in the formula, as you are effectively doing. There are, however, two other ways that you can approach this problem, if you desire.
The first idea is to simply create all your formulas, but leave the absolute references in place. Then, as a "final" step, use Find and Replace to get rid of the dollar signs in the formulas. All you need to do is follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
That's it; all the dollar signs in the selected cells are deleted, leaving relative references, as desired. If you have some absolute references in those cells that you want to remain, then you should use the controls in the Find and Replace dialog box to step through each instance and do the replacement, as desired.
The second idea is quite novel, really. If your references are all to the same worksheet in the external workbook, then follow these general steps:
What happens with these steps is that when you move the worksheet back to its original workbook, Excel updates the first part of the formula references to include the information about the external workbook. It does not, however, change the cell references in those formulas from relative to absolute.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11226) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Relative References to Cells in Other Workbooks.
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!
Hate to take your hands off the keyboard while working on a worksheet? Here's one way to activate the Formula Bar without ...Discover More
Excel provides a variety of tools that allow you to perform operations on your data based upon the characteristics of ...Discover More
When you are working with sequenced values in a list, you’ll often want to take some action based on the top X or ...Discover More
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.