When you copy a formula cell and paste it to another cell in excel, it will always change the cell references value in the original formula automatically. For example, suppose cell A1 contains the formula “=B1*C1“. You copy cell A1 and paste the value to cell D1, and you will find the content of cell D1 is “=E1*F1“, excel will change the reference cell automatically. This article will show you how to keep the cell references in the destination cell formula unchanged.
1. Method: Change The Original Formula To Keep Cell Reference Unchanged.
- To avoid changing formula references while copying, we can make minor modifications to the formulas before copying.
- First, choose the cell range where you want to copy the formulas from, then navigate to the Home tab and select Find & Select drop-down menu in the Editing group.
- Then click the menu item Replace… to open the Find and Replace dialog window.
- In the Find and Replace dialog box that appears, input the symbol “=” into the “Find what” field.
- Then, type a blank space into the “Replace with” field.
- Lastly, click the “Replace All” button to complete the process.
- After the replacements have been made, a Microsoft Excel dialog box will appear displaying the number of replacements that were made.
- To close the dialog box, click on the OK button. Finally, close the Find and Replace dialog box.
- Continue selecting the range, copy it, and then paste it into the target cell.
- To proceed, select the pasted formulas, and then open the Find and Replace dialog box in the Home tab, Editing group, Find & Select —> Replace…
- To replace the blank spaces with the equal sign symbol, open the Find and Replace dialog box and enter a blank space in the “Find what” field.
- In the “Replace with” field, type the symbol “=” and select the “Replace All” button.
- Click the Replace All button to replace all the blank spaces with the = sign.
- It will pop up a dialog window to tell you how many replacements occurred.
- Close the dialog window and the Find and Replace window.
- Then you will notice that all of the formulas in the original cells have been accurately copied to the destination cells without changing the cell references.
