How to Copy a Formula in Excel Without Changing Cell References

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate how to copy a formula in Excel without changing cell references. We usually need to apply the same formulas while dealing with many worksheets. So, it is very important to learn how to copy formulas without changing cell references. It will not only make the work easy but also it will be easily edited if any mistake is made. In this tutorial, we will explain to you how to copy formulas in Excel without changing cell references.


How to Copy a Formula in Excel Without Changing Cell References: 7 Easy Methods

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Product in Column B, Buying Price in Column C, and Selling Price with Profit in Column C. If you follow the steps correctly, you should learn how to copy a formula in Excel without changing cell references. The methods are

Dataset to Copy a Formula in Excel Without Changing Cell References


1. Using Fill Handle Option

In this case, our goal is to copy a formula in Excel without changing cell references using the Fill Handle option. The steps of this method are.

Steps:

  • First, insert the following formula in cell D6.
=C6*(1+$D$5)

Using Formula to Copy a Formula in Excel Without Changing Cell References

  • Next, after pressing the Enter button, you will get the desired result for this cell.
  • Then, use the Fill Handle option to apply the same formula to all the desired cells.

Using Fill Handle to Copy a Formula in Excel Without Changing Cell References

  • Finally, you will get the desired result.

Final Result to Copy a Formula in Excel Without Changing Cell References

Read More: How to Copy Exact Formula in Excel


2. Use of Double-Click Plus Sign Option

Now, we want to copy a formula in Excel without changing cell references using the double-click plus sign option. The steps of this method are.

Steps:

  • At first, get the result for the first cell following the first method.

Dataset to Copy a Formula in Excel Without Changing Cell References

  • Select the formula containing the cell and double-click on the plus sign like in the below image.

Double-click to Copy a Formula in Excel Without Changing Cell References

  • Last, you will get the desired result.

Desired Result to Copy a Formula in Excel Without Changing Cell References

Read More: How to Copy SUM Formula in Excel


3. Copying a Formula to Non-Adjacent Cells

After that, our goal is to copy a formula in Excel without changing cell references by copying a formula to non-adjacent cells. The steps of this method are.

Steps:

  • To begin with, get the result for the first cell following the first method.
  • Then, use the Ctrl+C buttons to copy the cell.

Copying Cell to Copy a Formula in Excel Without Changing Cell References

  • In addition, select the cells you want to insert the desired formula.

Selecting Desired Cells to Copy a Formula in Excel Without Changing Cell References

  • Finally, press the Ctrl+V buttons to get the desired result.

Final Result to Copy a Formula in Excel Without Changing Cell References


Similar Readings


4. Entering Exact Formula Individually in New Cells

Again, our goal is to copy a formula in Excel without changing cell references by entering the exact formula individually in new cells. The steps of this method are.

Steps:

  • Firstly, get the result for the first cell following the first method and use the Ctrl+C buttons to copy the cell.

Copying Cell to Copy a Formula in Excel Without Changing Cell References

  • Go to select the cell > Home > Paste > Paste Formula options.

Using Paste Option to Copy a Formula in Excel Without Changing Cell References

  • You will get the result for the desired cell.

  • If you repeat the same steps then you will get the result for the next cell as well.

  • Lastly, you will get the desired result.

Read More: How to Copy Formula in Excel Without Dragging


5. Utilizing Find and Replace Option

Next, our goal is to copy a formula in Excel without changing cell references utilizing the find and replace option. The steps of this method are.

Steps:

  • To begin with, get the result for the first cell following the first method.

  • Press the Ctrl+H buttons to open the Find and Replace window.
  • In the window, go to the Replace option, change the Find what and Replace with option and then press Replace All to execute the changes.

  • Furthermore, you will get the changes like the below image.

  • Then, use the Fill Handle option to apply the same formula to all the desired cells.

  • Finally, you will get the desired result.


6. Using Keyboard Shortcuts

This time, our goal is to copy a formula in Excel without changing cell references using the keyboard shortcuts option. The steps of this method are.

Steps:

  • Firstly, get the result for the first cell following the first method.

  • Go to the desired cell and press the Ctrl+D option to directly copy and paste the formula.

  • You will get the desired result for the cell.

  • If you repeat the process, you will get a similar result for the next cell as well.

  • You will get the desired result.

Read More: How to Copy Formula Down Without Incrementing in Excel


7. Use of Notepad to Copy Formula

Next, our goal is to copy a formula in Excel without changing cell references using Notepad to copy the exact data. The steps of this method are.

Steps:

  • To begin with, get the result for the first column following the first method.

  • In addition, copy the data of the whole column in the notepad like the below image.

  • Furthermore, in the notepad, select all the data and copy them using the Ctrl+C buttons.

  • Finally, go to the desired column and use the Ctrl+V buttons to get the desired result.

Read More: How to Copy a Formula in Excel with Changing Cell References


How to Copy and Paste in Excel Without Changing Format

In this case, our goal is to copy and paste into Excel without changing the format. It is very important as it is the most useful method in every field of work. The steps of this method are.

Steps:

  • First, insert the following formula in the D6 cell.
=C6*(1+$D$5)

Inserting Formula to Copy a Formula in Excel Without Changing Cell References

  • After pressing the Enter button, you will get the desired result for this cell.
  • Then, use the Fill Handle option to apply the same formula to all the desired cells.

Use Fill Handle to Copy a Formula in Excel Without Changing Cell References

  • Last, you will get the desired result.

Read More: Creating and Copying Formula Using Relative References in Excel


How to Copy Formula in Excel to Another Sheet

In this case, our goal is to copy the formula in Excel to another sheet. When we copy a cell that does not have a formula but has value and paste it someplace, it is usually pasted as value. However, in order to use the formula in the pasted cells, we must copy the formula from another worksheet and alter the value to match the data set in the current sheet. In this tutorial, we will explain to you how to copy formulas to another sheet in Excel. The steps of this method are.

Steps:

  • To begin with, select the desired cells in column data and use the Ctrl+C option to copy them.

  • In addition, select the desired cells you want to paste the data.

  • Furthermore, use the Ctrl+V buttons to paste the data into the desired cells.
  • Finally, you will get the desired result.

Result to Copy a Formula in Excel Without Changing Cell References


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Henceforth, follow the above-described methods. These methods will help you to copy a formula in Excel without changing cell references. We will be glad to know if you can execute the task in any other way. Please, feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Related Articles

Zehad Rian Jim
Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo