Note: The point of this video is to show you how to stop Excel from changing references when you need 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).
When you copy and paste formulas, Excel will change all relative cell references to reflect the new location of the formula. Usually, this is exactly what you want. However, there are times when you’ll want to copy a formula exactly so that cell references don’t change.
Let’s take a look at a few ways to do that.
First let’s take a look at the problem. Here’s a simple worksheet that captures hours worked in a week and displays a total. The total is calculated using the SUM function on all cells above.
Let’s say you want copy the formula that sums the hours to another location.
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. Then you can copy a formula and the references won’t change.
But making cell references absolute, just so you can copy a formula, doesn’t make a lot of sense. So let’s undo that.
The standard way to copy a formula exactly, is to copy the formula as text, and then paste.
To do this, first enter edit mode. Next, select the entire formula, and copy it to the clipboard.
Use escape to get out of edit mode.
Now, if we move to another location on the worksheet and paste, we’ll get an exact copy of the original formula. Note that the cell references have not changed.
Another way to make an exact copy a formula is to use the keyboard shortcut Control-‘. Just select the cell below a formula, and press the control key with the single quote or apostrophe key. Note that this only works when you are are copy to the cell directly below.
Sometimes you don’t want to copy a formula, but want to move it to a new location.
A simple way to do this is to use Cut and Paste instead of copy and paste. Cutting to the clipboard keeps all cell references intact and unchanged.
You can also use drag and drop. Just select cell and hover the the mouse over the cell boundary until the cursor changes. Then drag the formula to a new location.
Notice that both cut and paste, and drag and drop also bring formatting from the source cell to the destination. If want to keep the formatting at the new location, copying the formula as text, as we saw in the first example, is a better approach.