Copy formulas without link shift

Problem

Suppose we have a simple table like this, in which the amounts are calculated for each month in two cities, and then the total is converted into euros at the rate from the yellow cell J2.

The problem is that if you copy the range D2:D8 with formulas somewhere else on the sheet, then Microsoft Excel will automatically correct the links in these formulas, moving them to a new place and stop counting:

Task: copy the range with formulas so that the formulas do not change and remain the same, keeping the calculation results.

Method 1. Absolute links

Method 2: Temporarily disable formulas

To prevent formulas from changing when copying, you need to (temporarily) make sure that Excel stops treating them as formulas. This can be done by replacing the equal sign (=) with any other character not normally found in formulas, such as a hash sign (#) or a pair of ampersands (&&) for copy time. For this:

1. Select the range with formulas (in our example D2:D8)
2. Click Ctrl + H on the keyboard or on a tab Home – Find and Select – Replace (Home — Find&Select — Replace)

   

3. In the dialog box that appears, enter what we are looking for and what we replace with, and in Parameters (Options) don’t forget to clarify Search scope – Formulas. We press Replace all (Replace all).
4. Copy the resulting range with deactivated formulas to the right place:

   

5. Replace # on = back using the same window, returning functionality to formulas.

Method 3: Copy via Notepad

This method is much faster and easier.

Press the keyboard shortcut Ctrl+Ё or button Show formulas tab formula (Formulas — Show formulas), to turn on the formula check mode – instead of the results, the cells will display the formulas by which they are calculated:

Copy our range D2:D8 and paste it into the standard Notebook:

Now select everything pasted (Ctrl + A), copy it to the clipboard again (Ctrl + C) and paste it on the sheet in the place you need:

It remains only to press the button Show formulas (Show Formulas)to return Excel to normal mode.

Note: this method sometimes fails on complex tables with merged cells, but in the vast majority of cases it works fine.

Method 4. Macro

If you often have to do such copying of formulas without shifting references, then it makes sense to use a macro for this. Press keyboard shortcut Alt + F11 or button Visual Basic tab developer (Developer), insert a new module through the menu Insert – Module  and copy the text of this macro there:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Select cells with formulas to copy.", _ "Copy formulas exactly", Default:=Selection.Address, Type :=8) If copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("Now select the paste range." & vbCrLf & vbCrLf & _ "The range must be equal in size to the original " & vbCrLf & _ " range of cells to copy." , "Copy formulas exactly", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Copy and paste ranges vary in size!", vbExclamation, "Copy error " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

You can use the button to run the macro. Macros tab developer (Developer — Macros) or keyboard shortcut Alt + F8. After running the macro, it will ask you to select the range with the original formulas and the insertion range and will copy the formulas automatically:

• Convenient viewing of formulas and results at the same time
• Why R1C1 reference style is needed in Excel formulas
• How to quickly find all cells with formulas
• Tool to copy exact formulas from the PLEX add-on