How to Copy and Paste Formulas
There may be situations in which we might prefer to copy and paste a formula from one cell to another so that we don’t have to retype the entire formula all over again.
I mean, wouldn’t we prefer to be able to just make a few mouse clicks?
Rather than retyping something like this:
Without the ability to paste formulas, Excel’s innovative functions would reach the point of diminishing returns very quickly. With that in mind, let’s get started with how to copy and paste formulas!
We will use a very simplified scenario to make sure we get the concepts we will cover.
See the table below.
Here we have a table with two columns that contain numeric values and then a third column containing a simple formula.
Obviously, we want to focus on the on the ‘Formula’ column and how to copy and paste any of these cells while maintaining the integrity of the formulas.
So let’s say we add a new row of values in the ‘Value 1′ and ‘Value 2’ columns and we want to carry down the formula in the ‘Formula’ column.
Our formula is simply an ‘IF’ comparison of the values in each of the value columns that will return whatever values we have chosen for the TRUE or FALSE result.
Clearly, if we have added new data to subsequent rows we need the formula to refer to cell references according to the row and not the same row we are copying from.
Excel allows us to paste formulas and when we do so, the formula automatically will change row references according to the row we paste it to.
The exception to this would be if we had locked the row number of any of the cell references in our formula by preceding it with a ‘$’.
For example, ‘A$1’ would remain ‘A1’ rather than taking on the row number of the cell that we paste the formula into.
As for copying our cell, it is the same method no matter how we want to paste it.
However, there are actually a couple of methods to copy!
First, we can use the ‘Copy’ selection in the ‘Clipboard’ section of the ‘Home’ tab in the ribbon. This will work just fine.
But for the sake of more efficient workflow, the second option is to use the keyboard shortcut ‘Ctrl’ + ‘C’.
It’s simply done by pressing and holding the ‘Ctrl’ key and then pressing the ‘C’ key. This shortcut copies the selection to the clipboard and saves time by eliminating repetitive mouse clicks and movement.
There is actually a third option for copying that lies somewhere between the other two methods by right-clicking the mouse when the selection is highlighted.
A drop down will appear that includes the option to copy.
This same drop down includes the ‘Paste Options:’ menu that we will be referring to shortly.
Now we can highlight cell ‘D9’ as our selection and press ‘Ctrl’ + ‘C’ to copy to our clipboard.
Then we go to the cell we want to paste it to (‘D10’), and right click.
Again, we will see the same drop down in the previous figure but because our selection is currently copied to the clipboard, ‘Paste Options:’ contains several options.
These options are:
‘Paste’ – ‘Values’ – ‘Formulas’ – ‘Transpose’ – ‘Formatting’ – ‘Paste Link’
These are all shown in the list by their respective icons.
Let’s take a look at what each of these means:
- ‘Paste’: This simply pastes everything about the copied cell.
- ‘Values’: This pastes the value of the copied cell and nothing else.
- ‘Formulas’: This pastes the formula from the copied cell.
- ‘Transpose’: This pastes the selection in a transposed position which simply means that if the copied selection was a range of cells on a row, that range would be pasted as a column and likewise if the copied selection was a range of cells in a column.
- ‘Formatting’: This simply pastes the formatting of the copied cell and no other contents.
- ‘Paste Link’: This simply pastes the cell reference(s) of a copied selection and the absolute cell reference of a copied cell.
This is very convenient but we can also click on ‘Paste Special’ beneath these six icons to open the full gamut of possibilities available in the ‘Paste Special’ dialog box. The keyboard shortcut ‘Ctrl’ + ‘Alt’ + ‘V’.
Here we can see the same options in addition to several others.
But let’s get back to the task of pasting our formula.
When we select our destination cell (‘D10’), we simply select ‘Formulas’ for our paste method and the cell now contains the formula we copied from our source cell with the cell references in the formula adapted to the new row.
But what if we had several rows of new data we added in the values column and we wanted to add formulas to the ‘Formula’ column for all those new rows of data?
Even with the additional efficiency the copy and paste offers us, as the need to expand the formulas scales larger with more data, doing so one cell at a time becomes less efficient.
But not to worry!
The same paste operation works just the same if we select a range of more than one cell.
So let’s look at this scenario…
We have added five new rows of data to our value columns.
So now we want to make sure we add formulas to the ‘Formula’ column for each of these additional rows of data.
All we need to do is select cell ‘D9’ and press our keyboard shortcut to copy (remember what that is 😉 ?).
Then we need to select the range of cells from ‘D10’ to ‘D14’.
Now let’s pull up the ‘Paste Special’ dialog box by one of the methods we discussed previously and select the ‘Formulas’ radio button.
Now all of the cells in the selected range should have formulas just like the one in the copied cell BUT with all the cell references changed to match the row numbers.
We can validate this by viewing the formula in the Formula Bar above the worksheet while the cell is selected.
In Excel 2016, we can also use the ‘FORMULATEXT’ function to show the formula in the cell.
The syntax is simple.
The ‘FORMULATEXT’ function only takes a cell reference and in this case, it would be a cell in the ‘Formula’ column like ‘D9’.
So our formula in ‘E9’ would be ‘=FORMULATEXT(D9)’.