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.

Table of Contents

## 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.

**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.

### 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.

**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

- 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…

## Leave a Reply

You must be logged in to post a comment.