Abstract
Transcript
When you copy and paste formulas, Excel will change all relative 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 spreadsheet 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 to 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 in a formula 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 put the cell in 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 of a formula is to use the keyboard shortcut Control-'. Just select the cell below the formula, and press the Control key with the Single Quote or Apostrophe key. Note that this only works when you are copying a formula to the cell directly below.
Sometimes you don't want to copy a formula, but you 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 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 will also bring formatting from the source cell to the destination. When you want to keep formatting at the new location, copying the formula as text, as we saw in the first example, is a better approach.
