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.
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!
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
If you have a long numeric value in a cell, you may have a need to remove the last digit of that value. You can do so ...Discover More
Sometimes you need to look backward, through the information above your formula, to find the data you need. This can be ...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.