Keep References: How to Copy & Paste Exact Same Formulas!

This problem occurs quite often: Excel always adapts the cell ranges in formulas when copying cells. An example: Cell A1 links to A2. When you copy A1 to B1, the link will change to B2. This example is comparatively simple but there are more complex situations. In this article, we’ll take a look at four methods for copying and pasting the exact same formulas in Excel with the goal to keep all references: Two manual ways by using the $-sign or copying the cell contents, the third method by using a simple replace trick and the fourth: the automatic method.

Method 1: Preserve the cell references with the ‘$’-sign

Method description: Using the dollar sign. The dollar sign fixates the following row or column character. This is called an “absolute” reference (whereas cell references that adapt when copying are called “relative” references).

Let’s say cell A1 has this content: „=A2“. If you insert the dollar sign before A, the column letter will be fixed: „=$A2“. When you copy this cell to B1, the formula will still say „=$A2“.

The row number in this example is not fixed. So if you copy the formula to cell A4, the cell link will change to „=$A5“. Of course you can also add the ‘$’-sign in front oft he row number: „=$A$2“. Now – no matter where you paste this cell – the reference will persist. A quick tip: When typing the formula just press F4 on the keyboard. Excel will add the dollar sign to the current cell link.

Advantages: This method is widely spread so that most people know how to deal with ‘$’-signs within the formulas. Furthermore, you can define in detail which part of the formula should persist and which links should adapt to the new range.

Disadvantages: Especially with large formulas, setting the ‘$’-signs can be annoying. Also, with different formulas within the copied range you have to define each ‘$’-sign separately.

In this context: How to bulk-insert dollar signs to existing Excel functions and formulas.

Method 2: Copy the cell contents instead of the cell

Method description: There is a second manual method for copying and pasting exact formulas in Excel. The method is quite simple and basically only works for single cells: Copy the cell contents and not the cell itself. Therefore, enter the cell (press F2) and copy the contents. Paste these contents to the new cell (no need to enter the new cell again).

Advantages: This method is fast and simple.

Disadvantages: It only works for single cells. As soon as you got more cells to copy, this method becomes very troublesome.

Method 3: Replace the ‘=’-sign to freeze all references in a range of cell

Method description: As there is no official way of copying and pasting cells with the same references, the best work around for large ranges is this: Make Excel believe, there is not a formula but just some text in your cells. That way, Excel won’t adapt anything. The following numbers are corresponding to the picture on the right hand side:

1. Replace all ‘=’- signs with a character which you don’t use in other ways in your workbook by pressing Ctrl + h (or click on “Find & Select” on the Home ribbon and then on “Replace”). You could try it with ‘§’. ‘#’ is usually not a good choice as it also occurs in error messages.
2. Copy the cells.
3. Paste them to a new area.
4. Replace the new sign (‘§’) with ‘=’ in the pasted cell range as well as the original cells.

Advantages: The method works on large cell ranges, especially with different formulas within the ranges.

Disadvantages: It usually takes some trial and error before you get the desired result.

Method 4: Copy and paste the exact formulas with Professor Excel Tools

Method description: When all the other ways have major disadvantages, the Excel add-in Professor Excel Tools steps in. It provides a function for copying and pasting the exact formulas without changing the links within the formulas. The application is quite easy and can be done with two steps:

1. Select the cells which you want to copy and press the Copy button on the left hand side of the Professor Excel Tools ribbon.
2. Go to the cell in which you can to paste the exact formula and click on the ‘Paste Exact Formula’ button.

Advantages: It’s simple, fast and works on large ranges.

Image by Free-Photos from Pixabay