Occasionally you may want to override Excel’s natural temptation to adjust cell references when you copy and paste, and just copy the existing formula as they are, with no changes to where they refer to. In effect you want to copy cell or cells without changing the formula within the cell (so =A1 won’t change to =B1 when you paste it).
Depending on how many cells you want to copy, there are 3 possible ways to do this i.e.
- copy and paste the contents (not the cell) of a single cell
- replace the = sign with some nonsense characters
- use the cut & paste tool but leave a copy behind
Copy only one cell to another cell and keep the references the same
In this case, you mustn’t copy the cell but rather the contents of the cell. So click in the cell and highlight the formula including the = sign. Copy it and then push the ESC key (otherwise you will overwrite this formula with a reference). Go to the other cell and paste it. The cell will have been copied and Excel won’t have adjusted the references even if they were relative (no $ signs)
Copy excel cells without changing formula
There are two ways to do this.
Convert formula to text
Firstly you could replace the equal signs with some nonsense characters turning them into a word. Then copy and paste the cells and replace the nonsense cells with the = sign again.
In steps
- I would highlight the area and do a Find/ Replace (CTRL+H). Ask it to find the = sign and replace all with #$# (or another combination that won’t ever appear in your formula or text otherwise on the undo of this you could create chaos).
- Copy this area and paste it in the new area (all formula would become text and any numbers or text would stay the same)
- Now in this new area and the old area, go back to Find/ Replace and replace #$# with = and replace all.
- All the cells that were formula will revert to formula but with the exact same references as the original area.
Use Cut and Paste
A clever alternative where you want to replicate a section of a model so that all references stay the same is to use the Cut&Paste tool in such a way that it leaves behind the original cells but also ‘moves’ (and hence doesn’t change references) the new cells.
Below a video clip which shows the method but the basics of it are as follows:
- Cut (CTRL+X) the area you want to copy from the workbook (let’s call it Workbook A)
- Paste it into a new workbook (lets call is workbook B)
- Close down the original workbook (A) but DON’T save
- Immediately open workbook A again (the area you just cut should still be there)
- In workbook B cut (CTRL X again) the section and paste it into the old workbook A.
- You will see that all the references stay the same.
- Excel tutorial below explains it visually.