By default, Excel automatically adjusts cell references of your formula when you copy and paste them to another location in the worksheet. However, the adjustment only happens when using relative references in your worksheet. You need to introduce a dollar sign in the formula to convert relative references to absolute references. Thankfully, Excel has workarounds that can prevent changing of formulas during the copy and paste process.
Let us discuss some methods that can be used to prevent changing cell references during the Copy and Paste process.
Using the Find and Replace Tool
Here are the steps to follow:
1. Open the Excel application and the worksheet that contains the formulas you wish to copy.
2. Highlight all the cells with the formula you wish to copy.
3. Click the Home tab on the Ribbon, and then go to the Editing section.
4. Click the Find & Select drop-down button. From the menu, choose the Replace button. Alternatively, use the keyboard shortcut to open the Replace dialogue box (CTRL + H).
5. In the Find What input box, type "=," while in the Replace with an input box, type the "#" or any other sign. Hit the Replace All button.
Note: All formulas begin with #, and thus changing it would prevent changing of cell references.
6. Click the OK button to close the dialogue box. Copy the cell with formulas containing the sign that replaces the Equal sign. Then, go ahead and paste the formulas (CTRL + V) to another location.
7. Click the Home> Find & Select >Replace.
8. In the Find What input box, type "#" while in the Replace with an input box, type the "=" and hit the Replace All button. That's all you need to do.
Using the Kutools Tool
Steps to follow:
1. Download and install Kutools Extension.
2. Open the Excel application and the worksheet that contains the formulas you wish to copy.
3. Highlight all the cells with the formula you wish to copy.
4. Click the Kutools tab on the Ribbon, and then go to the Formulas section.
5. Click the Exact Copy button. Check the Copy Formatting checkbox in the Exact Copy Formula dialogue box and hit the OK button.
6. In the Paste to section, highlight where you want to paste the formula. Hit the OK button, and that is all you need to do.
By converting formulas to Absolute references
Steps to follow:
1. Download and install Kutools Extension.
2. Open the Excel application and the worksheet that contains the formulas you wish to copy.
3. Highlight all the cells with the formula you wish to copy.
4. Click the Kutools tab on the Ribbon, and then go to the Formulas section.
5. Click the More drop-down button. From the menu, choose the Convert Refers button.
6. In the Convert Formula References dialogue box, toggle on the To absolute button and hit the Apply button. Then, hit the OK button to close the dialogue box.
7. The selected formulas will be converted to absolute formulas. Highlight and copy (CTRL + C), and then Paste (CTRL + V) to another location.