Relative formula not updating when copying

If we just copy the original formula, then paste, it stops working properly, because the cell references have been updated based on the new location.One way to prevent cell references from changing is to use absolute references.

But don't worry: you don't need to edit your 5,000 line spreadsheet cell by cell before you can copy-paste again.

Notice in steps 4 and 5 that what you are searching for and replacing it with is not the straight month names.

This is done because the month names alone (January, February, etc.) could easily occur in other places in the worksheet without being part of a formula.

Note: The point of this video is to show you how to stop Excel from changing cell references when you want to.

If you only want to display the same result of a formula elsewhere, just link back to the original formula (in the example, just use the formula '=C14' to link back to C14). Here's a simple worksheet that captures hours worked in a week and displays a total.

Leave a Reply