Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Pasting without Updating References.

Pasting without Updating References

by Allen Wyatt
(last updated July 15, 2017)

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, of course, 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, when this macro is done, they will all be relative.

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 (2033) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Pasting without Updating References.

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

Swapping Two Strings

Strings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so by ...

Discover More

Specifying the Exact Pages to Print

If you have multiple sections in your document, it can be frustrating to print out a limited selection of pages from that ...

Discover More

Pulling AutoShape Text from a Worksheet Cell

AutoShapes can easily contain text—just click on the shape and start typing away. You may want the text in the ...

Discover More

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!

More ExcelTips (menu)

Quickly Transposing Cells

If you want to turn a range of cells by 90 degrees within a worksheet, you need to understand how Excel can handle the ...

Discover More

Moving Cells Using the Mouse

Want to easily move information from one cell to another? A quick way to do it is to simply drag and drop using the mouse.

Discover More

Flipping Data

Have you ever spent a lot of time putting information into a worksheet, only to realize that you should have put it in the ...

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 7 - 0?

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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.