Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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.

Pasting without Updating References

Written by Allen Wyatt (last updated October 8, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


4

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11804) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel 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

Keeping Paragraphs on the Same Page

Don't want your paragraphs to flow from one page to another? Word provides a formatting setting that forces individual ...

Discover More

Clean Up Your Macro List

Got a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.

Discover More

Highlighting Greater Than Average Dry Durations

If you need to find whether the duration between two dates is greater than the average of all durations, you'll find the ...

Discover More

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!

More ExcelTips (ribbon)

Moving and Copying Cells

At the very heart of editing is the ability to move and copy cells in a worksheet. Understanding the differences between ...

Discover More

Default Cell Movement when Deleting

Delete a cell or a range of cells, and Excel needs to figure out how to rearrange the void left by the deletion. You can ...

Discover More

Entering Dates in Excel

When you type information into a cell, Excel tries to figure out what type of information you are entering. If Excel can ...

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}] (all 7 characters, in the sequence shown) 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 two less than 6?

2022-10-14 15:58:07

Rick Leisner

If only want to copy one formula without updating it, just put the cursor on the source, copy the formula from the formula bar (instead of the cell), hit Esc, then put the cursor on the destination and paste back into the formula bar.


2022-10-10 12:31:31

Mike Pringle

I tried the suggestion to show the formulas and then copy them to Notepad. If I have the data and formula in cells A1, B1, and C1, then the copy and paste into Notepad works but Excel crashes and does an automatic restart.

If I have the data in multiple cells and and rows (this was basic stuff like addition, subtraction, division), then copying the formulas resulted in Excel crashing "Microsoft Excel has stopped working" with the Close Program button displayed. No message from Microsoft on a solution. 8-)

MS Office Professional Plus 2016 32-bit on Windows 10 Enterprise 64-bit.


2022-10-10 04:42:07

Gerhard Seljehammer

Hi,
I always try to solve problems without making a macro. In this case I would have replaced the = sign with another charchter that would not be found in any of the formulas, for instance ¤ or any other strange symbol.
Ctrl + H, replace what? = , replace with? ¤ .
Then copy and paste the wholde range to your destination, and replace back to = in the cells. Also remember to "repair" the source cells.

Quick and easy.

Alternative (also simple)
Select source cells. Formulas, show formulas. Ctrl C
Open Notepad. Paste
Select all in Notepad, Ctrl C, goto destination and Paste.

Even safer and quicker perhaps.

Kind regards
Gerhard Seljehammer

PS: ASAP Utility has a function for this. :-)


2022-10-10 00:40:11

Shadeburst

Almost forty years ago, Lotus 1-2-3 had this native capability. Will it take another forty before MS listens to users and implements it?


This Site

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.

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.