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: Pasting without Updating References.
by Allen Wyatt
(last updated April 2, 2016)
As you are working on a worksheet, copying and moving information from one place to another, you may wonder if there is a way to copy or move a selection without Excel changing all the references within the selection. The answer, of course, is that it depends. (Don't you just love that about Excel?) Let's take a look at how you can both copy and move selections in Excel.
If you are copying a selection, then Excel will update all relative references within the selection when you paste it. The solution is to make sure that all the references within the selection are absolute before doing the copy and paste. Making the changes to the formulas by hand is tedious. You can use the following macro to convert all the formulas in the selection to their absolute equivalent:
Sub ConvertToAbsolute() Dim c As Variant Application.ScreenUpdating = False For Each c In Selection c.Value = Application.ConvertFormula(c.Formula, _ xlA1, , xlAbsolute) Next c Application.ScreenUpdating = True End Sub
Once this macro is run, you can copy and paste the selection without Excel doing any updating to references. Once the pasting is done, you can change the references in the selection (and in the original range, if it still exists) by selecting the range and applying this macro:
Sub ConvertToRelative() Dim c As Variant Application.ScreenUpdating = False For Each c In Selection c.Value = Application.ConvertFormula(c.Formula, _ xlA1, , xlRelative, c) Next c Application.ScreenUpdating = True End Sub
This macro will change all formulas in the selected range to their relative equivalent. Remember that this will affect all formulas—which means that if the formulas in the range contained both relative and absolute references, then they will all be relative when this macro is done.
If you are moving a selection, then Excel does not update cell references in the move. You can move either by selecting the range and using the keyboard (pressing Ctrl+X to cut and then Ctrl+V to paste the selection) or the mouse (dragging the selection to a new location). In either case, Excel leaves the references in the selection exactly the same—relative or not—without updating.
So far I have discussed what Excel does with the references in the selection being copied or moved. What about references to the information in the selection? If you are copying, then Excel leaves references pointing to the original range. If you are moving a selection, then Excel updates references to that selection, regardless of whether they are relative or absolute. If you don't want the information updated during a move, then the solution is to make a copy of the range and then delete the original.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11804) 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: Pasting without Updating References.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
The fill handle can save a huge amount of time when you are editing a worksheet. If you are really good at using the ...Discover More
Need to get rid of everything in a worksheet except for your formulas? You can do it rather easily by applying the ideas ...Discover More
When entering data in a worksheet, you may only want to add information to the cells in a particular range. You can ...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.