How to Copy and Paste Formulas in Excel without Changing Cell References

Watch Video – Copy and Paste Formulas in Excel without Changing Cell References

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.

Copy and Paste Formulas in Excel - Reference change demo

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 and Paste Formulas in Excel - show formulas
  • 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).

You May Also Like the Following Tutorials:

  • Jim - Omaha, NE USA says:

    I have a cell reference issue I hope someone can help me with. I have a cell outside of a range that I always want to refer to a specific cell inside of the range, even when cells are inserted or deleted from the range. For example, cell A10 refers to C10 in the range B1:D200. If someone inserts cells B7:D13, I still want A10 to refer to C10, not C17. I think I need a helper column that has the text “C10” in cell E10. What is the Function that gets A10 to use the static text in E10 to refer to cell C10?

    • Sumit Bansal says:

      You can use the INDIRECT function. This should work =INDIRECT(“C10”). If you have text C10 in cell E10, just use =INDIRECT(E10)

      • Jim - Omaha, NE USA says:

        Thanks. That is the Function I was looking for, but could not remember.

      • Jim - Omaha, NE USA says:

        Even though INDIRECT is less complicated, can you tell me why CELL(“contents”,ADDRESS(10,3)) didn’t work?

        • jim says:

          Jim,
          although these are functions I’ve never had cause to use, I think this might be because the $C$10 from the ADDRESS function is seen as text, not a cell reference
          CELL(“contents”,”$C$10″) certainly does not work
          regards,
          t’other jim

      • jim says:

        although INDIRECT is the way to go with this, you could also use OFFSET:
        =OFFSET(A10,,2) should work
        both are volatile formulae (will recalculate on every worksheet change), which you might be able to avoid by using =INDEX(C:C,10) which would only fail your requirements if a whole column were inserted or deleted somewhere between A:A and C:C

        • jim says:

          taking this a step further, =INDEX(1:1048576,10,3) will always refer to C10 – but it’s very clumsy-looking

    • Jim - Omaha, NE USA says:

      I’ve made a step in the right direction. ADDRESS(10,3) results in $C$10 and it does not change when cell C10 is moved. CELL(“contents”,$C$10) gives me the proper result. However, CELL(“contents”,ADDRESS(10,3)) is not even accepted. What is wrong with the nested formula?

      • AjayC says:

        you should use “=CELL(“contents”, INDIRECT(ADDRESS(10,3,1,1,”Sheet1″),1))” as there are certain arguments to ADDRESS function which ADDRESS(10,3) is not capturing and those arguments are not optional.

  • jim says:

    I think Bansal’s point was that sometimes you can have a range of dynamic formulae that you want to replicate elsewhere
    I’ve had this situation occur before but I never thought of using the Notepad method – thanks for that, another weapon in my arsenal

  • Ogundepo Ezekiel Adebayo says:

    Absolute cell reference is the best. i.e. =A$1$ + B$1$ this cell is locked in that way.

  • Gurpreet says:

    I use absolute/Dynamic references for doing this

  • >