How to disable Excel's automatic cell reference change after copy/paste?

Question

I've got a massive Excel 2003 spreadsheet I'm working on. There are a lot of very large formulas with a lot of cell references. Here's a simple example.

='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49

Most of them are more complicated than that, but this gives a good idea of what I'm working with. Few of these cell references are absolute ($s). I'd like to be able to copy these cells to a different location without the cell references changing. I know I can simply use f4 to make the references absolute, but there is a lot of data and I may need to use Fill later. Is there any way to temporarily disable the cell reference changing on copy-paste/fill without making the references absolute?

EDIT: I just found out that you can do this with VBA by copying the cell contents as text instead of a formula. I'd like to not have to do this though because I want to copy whole rows/columns at once. Is there a simple solution I am missing?

Macneil

Great question! Can a loop in VBA not solve your remaining problem? – Neogene

Answer 1

From http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Or, from http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/:

If you're a VBA programmer, you can simply execute the following code: 
With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Workwoman

It looks like you can paste into a sheet that isn't in Formula View Mode; it doesn't hurt to follow this to the letter though as everything else is strictly necessary. +1 as this was very helpful and easy to follow. I don't get why Excel assumes we want to reference a cell in another file, but at least there is a good work around. – Aglimmer

This answer is wonderful - I wish I could accept it for the original poster. I'm a programmer so VBA doesn't scare me, but I'm not going to use it just to copy and paste some cells if I can avoid it... There really should be a "Paste without updating formula" option build in to Excel. – Clayson

@Clayson Yes exactly. I don't know why they won't put Paste without updating formula. What bothers me the most is that there are more than spreadsheet providers, MS Office, Google Sheet, or WPS Office and they just repeat each other. I miss the time when they were innovated with the technology and functionality. They do a huge improvement with every release. But some basic stuff are just missing. – Noranorah

Answer 2

A very simple solution is to select the range you wish to copy, then Find and Replace (Ctrl + h), changing = to another symbol that is not used in your formula (e.g. #) - thus stopping it from being an active formula.

Then, copy and paste the selected range to it's new location.

Finally, Find and Replace to change # back to = in both the original and new range, thus restoring both ranges to being formulae again.

Emancipated

So far, sadly, this is the solution that I am most content with. It would have been convenient with an alternative shortcut that simply did this. – Seventeenth

This solution doesn't work if you are copying the data to a different sheet...and sadly, that's mostly the case where this functionality is needed. – Arsenite

Answer 3

I came to this site looking for an easy way to copy without changing cell references. But now I thnk my own workaround is simpler than most of these methods. My method relies on the fact that Copy changes references but Move doesn't. Here's a simple example.

Assume you have raw data in columns A and B, and a formula in C (e.g. C=A+B) and you want the same formula in column F but Copying from C to F leads to F=D+E.

1. Copy contents of C to any empty temporary column, say R. The relative references will change to R=P+Q but ignore this even if it's flagged as an error.
2. Go back to C and Move (not Copy) it to F. Formula should be unchanged so F=A+B.
3. Now go to R and copy it back to C. Relative reference will revert to C=A+B
4. Delete the temporary formula in R.
5. Bob's your uncle.

I've done this with a range of cells so I imagine it would work with virtually any level of complexity. You just need an empty area to park the coiped cells. And of course you have to remember where you left them.

Bathurst

Description is not simple :) If you want to copy row 1 (which references to row 2) to row 11, then: 1. Copy it to row 11, then it will reference row 12. 2. Cut and paste it to row 21, then it will still reference row 12. 3. Copy it from 21 to 11, then it will reference to row 2, just as needed. – Septempartite

It seems simple to me, copy to an offset, move the original, copy the offset back, delete offset. – Lapidify

For me this is the most simple since I can almost do it with my eyes closed hands on keyboard. Plus you can do whole areas or rows in one go: Ctrl+C -> Ctrl+V then Ctrl+X -> Ctrl+V, repeat as often as needed. E.g. to duplicate some rows in a table with references to outside the table this is the best: it updates the references inside the selection while preserving the references outside the selection. – Tire

Answer 4

This simple trick works: Copy and Paste. Do NOT cut and paste. After you paste, then reselect the part you copied and go to EDIT, slide down to CLEAR, and CLEAR CONTENTS.

Pickel

Answer 5

Simple workaround I used just now while in a similar situation:

1. Make a duplicate of the worksheet.
2. Cut + Paste the cells with formulas from the duplicate worksheet (e.g. Copy of Sheet1) into the original worksheet.
3. Remove all occurrences of the the duplicate sheet's name from the newly pasted formulas; e.g. find-and-replace all occurrences of the search term 'Copy of Sheet1'! with an empty string (i.e. blank).
4. Delete duplicate worksheet to clean up.

Note: if your sheet name lacks spaces you won't need to use the single quote/apostrophe (').

Your cell references are now copied without being altered.

Baillieu

Sounds like it could be a good solution but I am losing track of how this solution woks. Can you rephrase the answer while being more explicit please? – Arsenite

Answer 6

I think that you're stuck with the workaround you mentioned in your edit.

I would start by converting every formula on the sheet to text roughly like this:

Dim r As Range

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 1) = "=") Then
        r.Formula = "'ZZZ" & r.Formula
    End If
Next r

where the 'ZZZ uses the ' to signify a text value and the ZZZ as a value that we can look for when we want to convert the text back to being a formula. Obviously if any of your cells actually start with the text ZZZ then change the ZZZ value in the VBA macro to something else

When the re-arranging is complete, I would then convert the text back to a formula like this:

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r

One real downside to this method is that you can't see the results of any formula while you are re-arranging. You may find that when you convert back from text to formula that you have a slew of #REF errors for example.

It might be beneficial to work on this in stages and convert back to formulas every so often to check that no catastrophes have occurred

Noel

Answer 7

It's common to use find/ replace to disable formulas whilst performing manipulations. For example, copy with transpose. The formula is disabled by placing some placeholder (e.g. "$=") in front of it. Find replace can get rid of these once the manipulation is complete.

This vba just automates the find/ replace for the active sheet.

' toggle forumlas on active sheet as active/ inactive
' by use of "$=" prefix

Sub toggle_active_formulas()
    Dim current_calc_method As String
    initial_calc_method = Application.Calculation
    Application.Calculation = xlCalculationManual
    Dim predominant As Integer
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        If c.HasFormula Then
            predominant = predominant + 1
        ElseIf "$=" = Left(c.Value, 2) Then
            predominant = predominant - 1
        End If
    Next c
    If predominant > 0 Then
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If c.HasFormula Then
                c.Value = "$" & c.Formula
            End If
        Next c
    Else
        For Each c In ActiveSheet.UsedRange.Cells
            On Error Resume Next
            If "$=" = Left(c.Value, 2) Then
                c.Formula = Right(c.Value, Len(c.Value) - 1)
            End If
        Next c
    End If
    Application.Calculation = initial_calc_method
End Sub

Assiduity

Can you add more detail to this post please? – Rubenrubens

Answer 8

This macro does the whole job.

Sub Absolute_Reference_Copy_Paste()
'By changing "=" in formulas to "#" the content is no longer seen as a formula.
' C+S+e (my keyboard shortcut)

Dim Dummy As Range
Dim FirstSelection As Range
Dim SecondSelection As Range
Dim SheetFirst As Worksheet
Dim SheetSecond As Worksheet

On Error GoTo Whoa

Application.EnableEvents = False

' Set starting selection variable.
Set FirstSelection = Selection
Set SheetFirst = FirstSelection.Worksheet

' Reset the Find function so the scope of the search area is the current worksheet.
Set Dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

' Change "=" to "#" in selection.
Selection.Replace What:="=", Replacement:="#", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select the area you want to paste the formulas; must be same size as original
  selection and outside of the original selection.
Set SecondSelection = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
Set SheetSecond = SecondSelection.Worksheet

' Copy the original selection and paste it into the newly selected area. The active
  selection remains FirstSelection.
FirstSelection.Copy SecondSelection

' Restore "=" in FirstSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Select SecondSelection.
SheetSecond.Activate
SecondSelection.Select

' Restore "=" in SecondSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Return active selection to the original area: FirstSelection.
SheetFirst.Activate
FirstSelection.Select

Application.EnableEvents = True

Exit Sub

Whoa:
' If something goes wrong after "=" has been changed in FirstSelection, restore "=".
FirstSelection.Select
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Note that you must match the size and shape of the original selection when you make your new selection.

Ellaelladine

Answer 9

I found this solution which automates @Alistair Collins solution.

Basically you will change the = in any formula to * then do the paste after that you will change it back

        Dim cell As Range

msgResult = MsgBox("Yes to lock" & vbNewLine & "No unlock ", vbYesNoCancel + vbQuestion, "Forumula locker")

If msgResult = vbNo Then
    For Each cell In Range("A1:i155")
        If InStr(1, cell.Value, "*") > 0 Then
            cell.Formula = Replace(cell.Formula, "*", "=")
        End If
    Next cell
ElseIf msgResult = vbYes Then
    For Each cell In Range("A1:i155")
        If cell.HasFormula = True Then
            cell.Formula = Replace(cell.Formula, "=", "*")
        End If
    Next cell
End If

Cineraria

Answer 10

The above solutions won't work if the destination workbook where you want to paste is a protected worksheet and if you don't have the password. You can't apply replace command.

I may suggest another workaround. Assume you are copying from Workbook1, Sheet1 B5:B15 and want to paste it in another destination workbook (Workbook2 Sheet1) at D3:D13.

1. COPY the cells/range from original workbook and original sheet. (Workbook1, Sheet1 B5:B15 )

2. Open a new workbook & worksheet (Workbook3 Sheet1). Paste the copied cells/range into the same cell (B5) from where the copied cells/range starts.

3. Now, CUT the pasted cells/range from Workbook3 Sheet1. Open a new empty sheet (Workbook3 Sheet2). Paste the cut cells/range into Workbook3 Sheet2 into the cell (D3) where you want to finally paste in the destination workbook (Workbook2).

4. Now select the pasted range, and replace the sheet name ('Sheet1!') with Ctrl+H.

5. Now copy this range and go to your destination workbook (Workbook2), destination sheet (Sheet1) and paste the contents into the destination cell (D3).

These steps ensure that you are not doing any cut operation on original source file and hence, not disturbing it. No need to copy paste the original formula back. Cut operation is occuring in a new temporary workbook, and hence doesnot disturb the source or destination workbooks.

Hope it helps.

Equiponderance

Answer 11

Haven't check in Excel, but this works in Libreoffice4:

The whole thing of address rewriting comes during consecutive
(a1) cut
(a2) paste

You need to interrupt the consecutiveness by putting something in-between:
(b1) cut
(b2) select some empty cells (more than 1) and drag(move) them
(b3) paste

Step (b2) is where the cell that is about to update itself stops the tracking. Quick and simple.

Viral

I think that this answer is not appropriate as it has not even been tested. – Weight

Fjodr, I don't have excel. Could you please test it? I expect it to work and this sounds like the easiest of all the proposed solutions. The most voted answer consist of 9 steps! Mine is much easier. Please try it! – Octopus

Answer 12

Try this: Left-click the tab, making a copy of the whole sheet, then cut the cells you want to keep the same and paste them into your original worksheet.

Trulatrull

Answer 13

I found another workaround that is very simple: 1. Cut the contents 2. Paste them in the new location 3. Copy the contents that you just pasted into the new location you want. 4. Undo the Cut-Paste operation, putting the original contents back where you got them. 5. Paste the contents from the clipboard to the same location. These contents will have the original references.

It looks like a lot, but is super fast with keyboard shortcuts: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

Classieclassification

Answer 14

Click on the cell you want to copy. In the formula bar, highlight the formula.

Press Ctrl C.

Press escape (to take you out of actively editing that formula).

Choose new cell. Ctrl V.

Contrarily