When you copy and paste formulas in Excel, it automatically adjusts the cell references. For example, suppose I have the formula =A1+A2 in cell B1. When I copy the cell B1 and paste it in B2, the formula automatically becomes =A2+A3.
This happens as Excel automatically adjusts the references to make sure the rows and columns now refer to the adjusted rows and columns. Note: This adjustment happens when you’re using relative references or mixed references. In the case of absolute references, the exact formula gets copied.
Copy and Paste Formulas in Excel without Changing Cell References
When using relative/mixed references in your formulas, you may – sometimes – want to copy and paste formulas in Excel without changing the cell references. Simply put, you want to copy the exact formula from one set of cells to another. In this tutorial, I will show you how you can do this using various ways:
Manually Copy Pasting formulas. Using ‘Find and Replace’ technique. Using the Notepad.
Manually Copy Paste the Exact Formula
If you only have a handful of formulas that you want to copy and paste without changing the cell references, doing it manually would be more efficient. To copy paste formulas manually:
Select the cell from which you want to copy the formula. Go to the formula bar and copy the formula (or press F2 to get into the edit mode and then copy the formula). Select the destination cell and paste the formula.
Note that this method works only when you have a few cells from which you want to copy formulas. If you have a lot, use the find and replace technique shown below.
Using Find and Replace
Here are the steps to copy formulas without changing the cell references:
Select the cells that have the formulas that you want to copy. Go to Home –> Find & Select –> Replace. In the Find and Replace dialog box: In the ‘Find what’ field, enter = In the ‘Replace with’ field, enter # Click OK. This will replace all the equal to (=) sign with the hash (#) sign. Copy these cells. Paste it in the destination cells. Go to Home –> Find & Replace –> Replace. In the Find and Replace dialog box: In the ‘Find what’ field, enter # In the ‘Replace with’ field, enter = Click OK.
This will convert the text back into the formula and you will get the result. Note: If you use the # character as a part of your formula, you can use any other character in Replace with (such as ‘ZZZ’ or ‘ABC’).
Using Notepad to Copy Paste Formulas
If you have a range of cells where you have the formulas that you want to copy, you can use a Notepad to quickly copy and paste the formulas. Here are the steps to copy formulas without changing the cell references:
Go to Formulas –> Show Formulas. This will show all the formulas in the worksheet. Copy the cells that have the formulas that you want to copy. Open a notepad and paste the cell contents in the notepad. Copy the content on the notepad and paste in the cells where you want the exact formulas copied. Again go to Formulas –> Show formulas.
Note: Instead of Formulas –> Show formulas, you can also use the keyboard shortcut Control + ` (this is the same key that has the tilde sign).
How to Convert Formulas to Values in Excel. Show Formulas in Excel Instead of the Values. How to Lock Formulas in Excel. Understanding Absolute, Relative, and Mixed Cell References in Excel. How to reference another sheet in Excel. How to Remove Cell Formatting in Excel How to Copy Excel Table to Word How to Copy and Paste Column in Excel? How to Multiply a Column by a Number in Excel
