How to Copy Formula without Changing Cell References

This post will guide you how to copy formula without changing its cell references to make an exact copy in excel. How to copy and paste formulas or links without changing Cell References. How to copy exact formula from one cell to another cell.

Copy Formula without Changing Cell References

You copy formulas from a range of cells to other cells in the same column or row to perform the same action. If your formulas contains relative cell references, then excel will change the reference address to its own row or column. And most of time, this is what you want. But if you just only want to copy these formulas from cells to others cells in the same row or column without changing its cell references. How to achieve it?

You can use one of the following methods to copy formula without changing cell references.

Method 1: Use Find & Replace feature

If you want to copy formulas from a range of cells without changing its cell references, you can use the Excel Find and Replace feature as following steps:

1# Select cells with formulas that you want to copy.

copy formula without changing reference1

2# go to Home tab, click Find & Select command under the Editing group, or you can directly press Ctrl + H shortcuts to open the Find & Replace dialog.

copy formula without changing reference2

3# switch to Replace tab in the Find and Replace windows, then type equal sign “#” in the Find what text box; and input hash sign “#” or any other symbol character in the Replace with text box. Then click “Replace All” button.

copy formula without changing reference3

copy formula without changing reference4

After all equal sign are changed to hash sign in selected cells, and you can copy formulas in selected cells to anywhere without changing its cell references. Since, the formulas has been converted into text string in excel.

#4 you can select cells that you want to copy, press shortcuts Ctrl + C, then select one cell that you want to paste the formulas, and press Ctrl + V.

copy formula without changing reference5

You will see that the formulas were copied to another cells without changing those Cell references. As Excel does not treat the formulas with hash sign as formulas. They are just text strings.

#5 select both changed cells, then you need to reverse the changes from hash sign (#) to equal sign (#). Press Ctrl + H to open the Find and Replace dialog, input hash sign in the Find what box, and input equal sign (#) in the Replace with box. Then click “Replace All” button.

copy formula without changing reference6

Method 2: Use Notepad to copy formulas without changing cell references

If you have a range of cells with the formulas that you want to copy, you can also use a notepad to copy and paste the formulas without changing those Cell references. You can refer to the following steps:

1# go to Formulas tab, then click Show Formulas command under Formula Auditing group. Or you can press the Ctrl + ` shortcut to enter into the formula view mode.

copy formula without changing reference7

2# select the cells with the formulas that you want to copy, and press Ctrl +C to copy it.

3# open notepad and press Ctrl + V to paste the formulas there. Then copy all formulas from notepad.

4# select the top cell where you want to paste the formulas and then press Ctrl + V to paste it.

5# go to Formulas tab, click Show formulas command again to toggle out the formula view mode. Or press Ctrl + ` shortcut again.

Last, if you just only need to copy a few formulas without changing cell references, you can manually copy and pasted without changing the cell references.


Related Posts

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar