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.
🔗 Copy Excel Sheet to Another Sheet with Same Format and Formulas
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)
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
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.
🔗 6+ Ways to Copy a Formula in Excel with Changing Cell References
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.
