3 Ways to Copy a Formula in Excel without Changing Cell References

We all know when we copy down a formula, Excel changes the cell references itself. And for most cases, it’s really useful because without this feature the whole work could be difficult. But in some specific cases, we don’t need the cell references to change while copying a formula. To stop the cell references to change, I am going to show you 3 different ways to copy a formula in Excel without changing cell references.

Introduction to the Dataset

So here is our sample dataset. I have used a pretty basic formula in column E. I subtracted the difference between the dates of due payment to current dates in column E.

Quickest Way to Copy a Formula in Excel without Changing Cell References

If your dataset has a small number of cells, this method will be best for you. It’s easy to remember and takes less time.

Now Follow The Guide 

Step_1: Double-click on the cell that has the formula (or press F2).

I double-clicked on cell E2. We do this because the formula becomes visible when we double click on it.

Step_2: Copy the formula (press CTRL+C).

Step_3: Now double click on an empty cell.

I selected cell H2.

Step_4: Paste the formula (press CTRL+V).

Step_5: Put the mouse cursor on the right bottom corner of cell H2, the Fill Handle ‘+’ icon will come along immediately.

Step_6: Now drag the Fill Handle down from H2 to H8.

Final Result >

This way you can copy a small number of formulas without changing their cell references in Excel.

5+ Proven Methods to Copy a Formula to Entire Column in Excel

How to Copy a Formula in Excel to Another Sheet (4+ Solutions)

Copy Excel Sheet to Another Sheet with Same Format and Formulas

Alternative Way #1: Use Find and Replace Command to Copy a Formula in Excel without Changing Cell References

I am going to replace the ‘=’ sign of the formulas with something else.

Now Follow The Guide 

Step_1: Select the cell range that has formulas. I selected cells from E2 to E8.

Step_2: Select the Home tab.

Step_3: From the Editing group, click on the Find & Select drop-down list.

Step_4: Click on the Replace command.

You will see the Find and Replace dialog box pop up on the screen.

Step_5: Type ‘=’ inside the Find what box.

Step_6: Type any other character inside the Replace with box.

I input ‘@’ inside the Replace with box.

Step_7: Click on Replace All button.

A small dialog box appears that shows “All Done. We made 7 replacements.”.

Step_8: Press the OK button.

You will notice the formulas has a changed arrangement with ‘#’ instead of ‘=’ now.

Step_9: Copy the formulas from column E.

Step_10: Paste the formulas in column H.

Step_11: Again open the Find and Replace dialog box (press CTRL + H).

Step_12: Type ‘#’ in the Find what box.

Step_13: Type ‘=’ in the Replace with box.

Step_14: Select the Replace All button.

Final Result >

So now the formulas are in their appropriate form without changing the cell references.

Apply Same Formula to Multiple Cells in Excel (5+ Solutions)

9 ways to Copy Formula Down in Excel without Dragging

6+ Ways to Copy a Formula in Excel with Changing Cell References

Alternative Way #2: Use Notepad to Copy a Formula in Excel without Changing Cell References

For this method, we are going to take help from Notepad. You can find the Notepad in your Windows.

Now Follow The Guide 

Step_1: Select the Formulas tab from the ribbon.

Step_2: From the Formula Auditing group, select the Show Formulas command.

The Show Formulas command will make all the existing formulas visible.

Step_3: Open a Notepad.

Step_4: Copy (CTRL+C) the formulas and paste (CTRL+V) them inside Notepad.

Step_5: Now copy the formulas from the Notepad and paste them to your destination cells of the sheet.

Step_6: Again click on the Show Formulas command.

Final Result >

This was another quick way to copy a formula in Excel without changing cell references.

4 Ways to Fill Formula Down to Specific Row in Excel

4+ Ways to Repeat Formula Pattern in Excel

Conclusion

I have discussed 3 solutions for copying formulas in Excel without changing cell references. I think these methods will be enough to make your work easier. Let us know if you still face any problems copying formulas in your worksheet.