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
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)
- 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.
- Finally, you will get the desired result.
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.
- Select the formula containing the cell and double-click on the plus sign like in the below image.
- Last, you will get the desired result.
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.
- In addition, select the cells you want to insert the desired formula.
- Finally, press the Ctrl+V buttons to get the desired result.
Read More: How to Copy Formula Down with Shortcut in Excel
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.
- Go to select the cell > Home > Paste > Paste Formula options.
- 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 a Formula in Excel with Changing Cell References
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.
Read More: Creating and Copying Formula Using Relative References in Excel
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 in Excel to Change One Cell Reference Only
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 Across Multiple Rows in Excel
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)
- 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.
- Last, you will get the desired result.
Read More: How to Copy Formula to Entire Column 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.
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.
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
