Most people are familiar with what copy and paste does and would have used it everywhere from Facebook to eMail. We are used to using it on files and on text but when we use it in Excel with formulas containing cell references, we can get unexpected results. Depending on what you do, Excel will either maintain or adjust any cell references in formulas. This can be handy to quickly populate formulas but if not aware how to use it, can cause some extra work.
If you copy and paste text, date or number the value will always stay exactly the same. For example, if you copy the word Tuesday, it wont suddenly change to Wednesday when you paste it elsewhere (though there is a special way to do that if you wished), however with formulas, if you do a copy/paste, Excel will actually change the formula to move the references according to where you pasted. Let’s start with a simple example. Column A has the values, column B has the formula that just points back to column A:
If we copy the formula in B2 and paste it into B3 through B11, instead of copying the exact formula text (=A2) it will change the row number in the formula accordingly:
Note that this is independent of the row number of the cell relative to the formula. For example if our formula in B2 would have been =A3, then when we would have done the paste, the formula in B11 would have been =A12. The formula will change according to how many cells away from the original you are. And it does not require us to paste the rows in between. If we would have just pasted the formula from B2 into B11 the result would have been the same. Excell would count how many rows lower you are pasting and adjust the formula accordingly.
This does not apply to just rows, columns get adjusted too. Lets use the transposed version of our example:
So when we copy the formula over the column will be adjusted similarly:
Unlike when you copy a formula, moving formulas will retain the references in the original cell. To move a formula we use the cut option instead of copy. Going back to the original example:
So instead if we cut from cell B2 and paste it into cell B11, the formula will stay “=A2”:
You will also notice that the original formula is gone i.e. moved from B2 to B11.
It is important to keep the difference in mind as it may cause formulas to get misaligned. For example let’s say we have columns customers and the rows are charges on specific dates. At the bottom we have a total for the customer.
In this scenario we have one customer who wishes to transfer his balance to another a new account. So we set up a a new column, and we correctly copy paste the sum formula in rows 12 to make sure it will now sum the new column:
But the when we move the balance over since we wish to move we cut and paste the charges. What happens is the whole reference moves and the original customer (Customer 1) still has a balance and the new sum is an error:
So even though we wish to ‘move’ the balance, we want to copy paste over the balance and then delete the original.
Relative vs Absolute Reference
To understand what those terms mean, think of it like this, you are walking with your friends downtown, as you walk, what is in front of you changes depending on where you are and where you face, however a specific building will remain in the same location regardless of where you are. Similarly a specific road is always in the same place wherever you are, however you can be on different locations on the road. The first case is relative, i.e. you reference of in front of you is relative to you. However the latter two examples are absolute references, i.e. independent of your location.
Whenever your formula references a cell you can reference it relatively (to the active cell) or absolutely – i.e. a specific cell. By default all references are relative, this is why when you copy and paste Excel will shift over any cell references. But we can also tell Excel to stick to a specific cell, or a row, or a column. We can achieve this by using the $ operator. When placed in front of a cell reference you freeze that cell reference. Note however that the rows and columns are frozen independently. For example:
Example 1 is the default reference, in this case both the column and the row reference are relative and will change if you copy and paste. In the second example we have a complete absolute reference, both the row and the column are frozen, so if you copy and paste this formula anywhere it will still point to E2 (=$E$2). In the third example we freeze only the row, not the column. And Example 4 is with the column frozen. One of the main uses for Exmaple 3 and 4 comes in really useful when you are trying to set up a table for a presentation, you can keep certain constants in rows and columns and then use formulas across your entire table to automatically populate the information you need.
You can cycle between the scenarios by moving the focus onto the reference you want and pressing the F4 button
Example: Multiplication Table
An example of how you can quickly populate a table for presentation I’ll use a simple example of a multiplication table.
Let’s start wit this set up:
What we want to do is type in a formula into cell B2 which we can then copy and paste across to produce the multiplicative result of the values in row 1 and column A.
We’ll start with the simple reference:
If we copy and paste this across our table we will quickly realize there is a problem. With each column and row we move across, the reference also moves. So by the time we get to cell K11 we will be multiplying cell J11 by K10 (=J11*K10). Here is where freezing the rows and the columns separately comes in handy. If we freeze column A and row 1 we can make sure it will stay there:
Now when we paste it across when we get to K11 it will multiple A11 by K1 or 10 by 10: