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

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 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****How to Copy a Formula in Excel to Another Sheet (4+ Solutions)****Copy Excel Sheet to Another Sheet with Same Format and Formulas**

## 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)****9 ways to Copy Formula Down in Excel without Dragging****6+ Ways to Copy a Formula in Excel with Changing Cell References**

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