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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a ...Discover More
When analyzing data, you may have a need to calculate a sum based on just part of a particular cell. This tip examines ...Discover More
Want to use a formula to check if there is an error in your formula? (Sounds confusing, but it's not that bad.) You'll ...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.