Sometimes when we copy cells, we want to make an exact duplicate. This tutorial teaches you exactly how to do that.
Typically, when a cell is copied, references in the formula in the new cells shifts relative to their location to the original cell unless the cell reference is locked. A simple example below:
C2 links to A2 A2 is copied and pasted to A4
C3 links to A3 automatically
The Solution
To prevent the link from changing, there are 2 ways:
- You can either use the $ sign to lock in the reference, please refer to [cell locking].
- However, sometimes there are just too many cells to deal with, or it involves complex formulas. The easiest way is to convert the formulas into text, copy the text into the new destination cells, and turn the text back into formulas.
Step by step instructions:
- Highlight the cells you want to duplicate.
- Search for “##” and make sure that there are no matches. If there are, skip to the end.
- Replace the equal sign “=” with “##”. The formulas will turn to text, which is exactly what you want.
- Copy the highlighted cells and paste them to the destination cells. None of the cell’s contents will change. Perfect!
- Highlight both sets of formulas.
- Replace “##” with “=”. Your cells instantly turn into calculated formulas.
It is rare that there are matches in step 2, however, if you do, please read. If there are matches to step 2, you need to find a unique string of text with no matches, because in step 6, you will replace all the “##” with “=”. If the cells originally has “##” before the duplicating, they will be converted to “=” too. Sometimes you can get away with using a longer string of text “###”, or you can go creative with “#x#”, etc. As long as you get rid of the “=”, its fine.