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

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.

Copy a formula in excel.xlsx

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.

Table of Contents

Copy a Formula in Excel without Changing Cell References Double-clicking on Fill Handle

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. Follow the steps below:

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.
Copy the formula (press CTRL+C).

Now double-click on an empty cell.
I selected cell H2.
Paste the formula (press CTRL+V).
Put the mouse cursor on the right bottom corner of cell H2, and the Fill Handle ‘+’ icon will come along immediately.

Now drag the Fill Handle down from H2 to H8.

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

Copy a Formula in Excel without Changing Cell References Using Find and Replace Command

I am going to replace the ‘=’ sign of the formulas with something else. To use the Find and Replace command in Excel, follow these steps to copy a formula without changing cell references:

Select the cell range that has formulas. I selected cells from E2 to E8.
Select the Home tab.
From the Editing group, click on the Find & Select drop-down list.

Click on the Replace command.
You will see the Find and Replace dialog box pop up on the screen.
Type ‘=’ inside the Find what box.
Type any other character inside the Replace with box.
I input ‘@’ inside the Replace with box.

Click on the Replace All button.
A small dialog box appears that shows “All Done. We made 7 replacements.”.
Press the OK button.
You will notice the formulas have a changed arrangement with ‘#’ instead of ‘=’ now.
Copy the formulas from column E and paste the formulas in column H.

Again open the Find and Replace dialog box (press CTRL + H) and type ‘#’ in the Find what box.
Type ‘=’ in the Replace with box and select the Replace All button.

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

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

Copy a Formula in Excel without Changing Cell References by Using Notepad

For this method, we are going to take help from Notepad. You can find the Notepad in your Windows. To do that, go through these steps below:

Select the Formulas tab from the ribbon.
From the Formula Auditing group, select the Show Formulas command.
The Show Formulas command will make all the existing formulas visible.
Open a Notepad.

Copy (CTRL+C) the formulas and paste (CTRL+V) them inside Notepad.
Now copy the formulas from the Notepad and paste them to your destination cells of the sheet.
Again click on the Show Formulas command.

This was another quick way to copy a formula in Excel without 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.

Frequently Asked Questions

How do I freeze formulas in Excel?

To freeze formulas in Excel and prevent them from updating when you scroll through a worksheet, you can use the “Paste Special” feature to convert formulas to their current values. Here’s how:

Highlight the range of cells containing the formulas you want to freeze.
Press CTRL+C on your keyboard.

Move to the same range where you copied the formulas.
Right-click on the selected range.
Choose “Paste Special” from the context menu.

In the Paste Special dialog box, select Values.
Click OK to paste the values and freeze the formulas.
Alternatively, after copying the data (CTRL+C), you can use the following keyboard shortcut to open the Paste Special dialog directly: ALT+E > S > V > ENTER

By pasting the formulas as values, you freeze them at their current state, preventing updates when the data in the referenced cells changes.

How do you copy formulas in Excel without incrementing?

To copy formulas in Excel without incrementing the cell references, you can use the $ symbol to create absolute references. Here’s how:

Click on the cell containing the formula you want to copy.
Press CTRL+C on your keyboard.

Go to the cell where you want to paste the formula.
Right-click on the destination cell.
Choose “Paste Special” from the context menu.

In the Paste Special dialog box, select Formulas.
Before clicking OK, manually edit the formula in the formula bar, replacing relative references with absolute references using the $ symbol.
Alternatively, after copying the data (CTRL+C), you can use the following keyboard shortcut to open the Paste Special dialog directly: ALT+E > S > V > ENTER

This method ensures that you copy the formula without incrementing the cell references when pasting it to a different location in Excel.

How do you copy and paste in Excel and keep formatting and formulas?

To copy and paste in Excel while keeping both formatting and formulas:

Select the cell or range you want to copy:
Click on the cell or drag to select the range.

Press CTRL+C on your keyboard.
Go to the cell where you want to paste the data.
Right-click on the destination cell.

Choose Paste Special from the context menu.
In the Paste Special dialog box, select both Formulas and Formats.
Click OK to complete the paste.
Alternatively, after copying the data (CTRL+C), you can use the following keyboard shortcut to open the Paste Special dialog directly: ALT + E > S > V >ENTER

This method ensures that you copy and paste both the formulas and formatting from the source to the destination in Excel.

Rate this post