Paste an Exact Match

Do you often use the same formula in multiple places in your MS Excel worksheets? I’m talking about the exact same formula. You know, no cell reference changes on the paste.

If so, have you ever tried to copy that formula from one cell to another?

Yeah, I know, all the cell references change in the pasted version relative to its new location. Not what you were hoping to accomplish, huh?

Looking for a method you can use to copy and paste the formula, without the cell references changing?

While I’m positive most people have different solutions to this dilemma, I have one to suggest that may not be on your list of tricks.

  • First, instead of simply selecting the cell, you need to actually select the contents of the cell. You can do that by either using the F2 key to put the cursor into the cell and then highlight the formula or you can go directly to the formula bar and highlight it there.

  • Once you have the formula highlighted, you can copy it as usual.

  • Now, hit the Enter or Esc key. (This step is important, because it tells Excel you’re done with the contents of the cell. Simply clicking elsewhere will make Excel think you’re still editing the contents of the original cell).

  • To finish, move to the new location and complete the paste.

Voila! The formula is pasted without any changes.

Don’t you just love it when things go your way?!

~ April