When you copy and paste formulasin Excel, it automatically adjusts the cell references.

You are watching: The adjusted cell references in a copied and pasted formula

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 columnsnow refer to the adjusted rows and columns.

Note: This adjustment happens when you’re using relative references or mixed references. In thecase 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 methodworks 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.

See more: Telephone Operator On Andy Griffith Show, List Of The Andy Griffith Show Characters

Here are the steps to copy formulas without changing the cell references:

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).