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.

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.

1. Copy Formula without Changing Cell References Using 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.

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.

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.

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.

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.

2. Copy Formula without Changing Cell References Using Notepad

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.

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.

3. Video: Copy Formula without Changing Cell References

Welcome to this video tutorial, we’ll uncover the art of copying formulas without changing cell references. Join us as we delve into two efficient methods that will enhance your Excel proficiency.

4. Related Posts

• Apply a formula in entire column or row without/with dragging
  Assuming that you want to apply a formula to large number of column cells or entire row. You can directly drag the AutoFill handle to all other cells. So you can use apply formula without dragging by using short cut or Fill Command…