Udostępnij za pośrednictwem

Formula Watch: Lock Excel formulas so they don't change when you paste

  • Artykuł

How many times has this happened to you? You're copying and pasting some Excel functions and they change to reflect the cells relative to where you pasted them, but you want the original values intact.

There's a neat trick that will make this problem quickly vanish. Just use the F4 key. Here's how it works:

Say we're adding two cells in C1:

=A1+B1

We want to be sure this doesn't change when we paste it elsewhere. Click on the cell with cell references you want to lock - in this case, C1. Now select the formula in the formula bar so that either the whole formula or just the part you want to lock is highlighted, and then press F4.

You'll get an absolute version:

=$A$1+$B$1

Freeze your formulas with dollar signs

Now when you cut and paste, the formula will stay the same - changing only if any of the original cells referenced in your formula are updated.

You're also free to lock just the row or the column. Press F4 a second time to lock just the rows:

=A$1+B$1

Press F4 a third time to lock just the columns:

=$A1+$B1

Press F4 one more time and you're back where you started, with a fully relative formula reference.

You could just enter the dollar signs manually, but F4 is a convenient shortcut. It's located right above the $ symbol on most keyboards, and you don't even have to press Shift to get to it.

Suzanne

Comments

  • Anonymous
    August 31, 2012
    Thanks, I was having the opposite problem - trying to get formulas to be relative, now I know what the $$ signs are for.

  • Anonymous
    December 12, 2012
    You just saved me HOURS of tedious work.  THANK YOU!

  • Anonymous
    April 15, 2013
    ditto....saved hrs of useless work...

  • Anonymous
    October 13, 2013
    THANK YOU , VERY CLEAR EXPLANATION

  • Anonymous
    November 25, 2013
    Thanks! it was really helpful! bless you :)