 |
||
Navigation Topics Tutorials Others Contact |
Excel - Copy, Paste and referencesIntroduction IntroductionTo effectively use a spreadsheet, you must be able to create formulas to calculate automatically any change that you could bring to your model. Because some of these formulas are alike, it's very effectient to copy them than to rewrite them. This page demonstrates how to copy formulas and also looks at the very important point of relative and absolute references. You must master these notions to properly use a spreadsheet. CopyOne of the big advantages of a spreadsheet is to be able to copy a formula or a text as often as you need to create a model. It's easier and faster to copy a formula than to rewrite it every time you need it. The usual way to copy a cell or a range of cells is as follows.
|
|
Select
the cell or the range of cells to be copied.
button. Another technique to copy is to use the Autofill handle located in the lower right corner of the cell or a range of selected cells. You need to:
The contents of the cell will be copied into the selected cells. You can also copy the contents in any direction: left, right, up or down. You can also copy a range of cells at the same time. The result will vary as the contents of cells are numbers or formulas. There is an exercise on this option later on this page. Absolute and relative reference: IMPORTANTCoping formulas is the fastest way to create a model. There is however a trap that you must be aware. Let's place the following formula =A1+A2 in the A3 cell. If you copy this formula in the B3 and C3 cells, the result would be the following: B3: =B1+B2 , C3: =C1+C2. Why did Excel not copy exactly the formula =A1+A2? Actually, it did copy the formula. It's all in the way that Excel interprets the formula. It's not the cell's address that's important but its position with regards to the cells where the formula is located. If the formula is in the A3 cell, Excel will interpret the formula =A1+A2 this way: the A1 cell is two cells above the current position of the formula and A2 cell is just above. So, when Excel will copy the formula it's going to add both cells just above where the formula is placed. It's for that reason that the formulas copied in the B3 (=B1+B2) and C3 (=C1+C2) cells are this way. They also add the numbers of the two cells above the formula. This kind of reference in the formula is called relative reference. Everything is relative to the location of the formula. Excel always fetches the numbers in the same way. It's also more practical when you copy a formula. You usually use the same formula on a different set of numbers. For example, you can copy a formula that adds the monthly expenses. The same formula that was needed for January should also work for every other month of the year. Quiz: If you copy the formula =A1+A2 from the A3 cell to the Z20 cell, what will the formula look like? The answer is at the end of this page. But what can you do when you need a variable in the formula? A variable is information that can change at any time. For example, if you want to know what happens to the profits if the growth rate changes from 5 % to 50 %. Or what if the number of employees passes from 5 to 25? Or what if the mortgage rate to be renewed changes? A model consists of three elements: constants, variables and formulas. Constants are number needed to start your model. They will not change during the timeline of your model. Even these numbers change in the long run, but not in the time frame for this model. Examples of constants are tax and insurance rates, sales tax, telephone bill and mortgage payments. Variables are the data that you want to test the model by asking " what happens if... " questions. What will happen if the interest rates rise? What will happen if a customer doesn't pay? What will happen at such a growth rate? These variables can change at any time so that you can try to find the best solutions for your model. Never write a variable in a formula. For example, never write =100000*10%. Otherwise, you'll have to change every formula each time the variable changes. It's for that reason that you need to place a variable in a single cell. If the variable is written in several cells, chances are you will forget to change a value in one of these cells. This would invalidate your entire model because you forgot to change a single value. It also makes it very difficult to make sure that the model is correct because you must verify all the cells or where the variable is located. This would make the validation process, very long and very painful. So, it's better to avoid copying a variable in several places in a file. The best way is to write a variable in a single cell and have every formula refer to that cell. Change the content of a single cell and your entire model changes! Isn't it why you created a model in the first place? This makes the work of creating formulas a little more complicated. What must you do when you want to copy a formula? Cell addresses are composed of two components: a reference to the column and to row they're located. The first cell's address, A1, is composed of these two components. The letter represents the column and the number represents the row. The column's addresses are from A to Z and then from AA to IV (in Excel 2007 it goes up to XFD). The row's addresses are from 1 to 65 565 (compared to Excel 2007's 1 048 576). By copying a formula, the relative reference changes too. The column reference changes when you copy a formula horizontally (left to right). The row reference changes when you copy a formula verticaly (up and down). You can use the dollar sign ($) to "fix" or "freeze" a column or a row in a formula. When the formula will be copied, the parts that are frozen will not change. To freeze, you must place the dollar sign ($) in front of the column or row position.
Example: Write numbers and following formula in the appropriate cells. Copy the contents of the A3 cell in the B3 to E3 cells. There are many ways to copy the formula. Here are two of them
There is also the other way by using the fill handle.
The other ways would have been to use the CTRL+C and CTRL+V keys to copy and paste the cell. You could also have used the copy and paste buttons on the standard toolbar or use the copy and paste options from the context menu that's available by pressing the right mouse button. The formulas in the B3 to E3 cells are:
By "freezing" the A column in the formula, this reference will always look for the value in the first column (A). This is necessary when you want to look for the contents of a cell that contains a variable and you wish to copy the formula. A formula that has a relative position would not allow you to look for the contents of the A1 cell once it's copied elsewhere. By freezing the column or the row in the formula, you'll not be obliged to change the formula once you copied it to other cells. If you want to copy a formula horizontally, you may only need to freeze the column reference ($A1). If you wish to copy a formula vertically, you may need to freeze the row or row reference (A$1). If you a coping the formula to a different row and column, you may need to freeze both. It depends on where the data is located relative to the position of your formula. If they're always in the same cell, you need to freeze the column of row to get an absolute reference. Excel will always look for the data in the same column or row. Exercise with the exchange rateThis exercise is to demonstrate the enormous advantage of using relative and absolute references in formulas that you want to copy. It consists in changing the values of US currency into Canadian dollars.
For this exercise, we presume that 1 US dollar equals 1.4 Canadian dollars, which is a the rate in the A2 cell. The A3 cell contains the formula that will help us to find the value for the first column. The result of this cell should be 7 (5 * 1.4 = 7). You could write the formula again for the following cells, from B3 to D3, but it's even more effective to copy the formula to the other cells (B3 to D3).
The frame around the A3 cell will flash. This is normal. It's to remind you that it's the cell that you have just copied.
And here it's! The formula is now copied in the other cells that you have just specified. However, the result is not what you expected. There are only zeros instead of values! Let's find out why.
You can see from the formula bar the following contents:
You'll notice that the B1 cell contains a value ( 10 ) whereas the B2 cell is empty. It's for that reason that Excel shows the value 0 (10 * 0 = 0) in the B3 cell. To solve this small problem, you could copy the exchange rate of the A2 cell in the B2 to D2 cells. But that's not a practical solution for several reasons. In the first place, the same value would occupy several cells for nothing. You could put the other numbers or formulas in that place. Secondly, the chances to forget to change the contents of a single cell during modifications are far too high. It would be really pity to have a useless model because you forgot to change the contents of one or two cells! It's preferable to put the values of variables and constants in cells apart and adjust formulas. It's never a good idea to put the value of a constant or a variable in a formula. As mentioned in the previous paragraph, if you forget to change the value in a single cell, invalidates your model. In fact constants and variables have something common: they can change. A variable can change any time for the needs of your model: occupation rate, price of a product, price of the material etc. A constant can also change but on a mid to long-term basis: taxes, tax rates, interest rates... Because a variable or constant can change, it's much easier to manage it by placing the value in a single cell. You can adjust your formulas to refer to that cell using relative and absolute references. You must master theses references if you wish to copy formulas and efficiently use Excel or any other spreadsheet. Let's return to the exercise. The formula must be adjusted so that it can always find the exchange rate in the right cell; A2 in our box. But what must you know before adjusting the formula? Is there a part of the formula that you want to fix or freeze? For the exercise, you must "fix" the A2 part of the formula. You must know if you will copy the formula horizontally, vertically or both? For the exercise, the formula will be copied horizontally in the B3 to D3 cells. Because it's horizontally, you only need "freeze" or to "fix" the letter of the column and not the number of the row in the cell address contained in the formula. It's now time to change the formula of the A3 cell to adapt it.
To change the contents of the cell.
The formula was changed but the value remains the same: 7 (5 * 1,4). It's only when you'll copy the formula and see the results that you'll notice the change.
If you need a reminder, refer to the instructions for copying above.
B3: =B1*$A2 You have now the right result because you "froze" the A column of the A2 cell. Look how it practical it can be:
All the values in worksheet have been changed by changing the content of a single cell! What do you prefer now; change the contents of a single cell or change several cells? Which one is the most efficient? (Answer: change a single cell) But you must pay attention in the way that you use the relative and absolute references.
What exchange rate! Let's verify the contents of the A4 to A8 cells. A4: = A2*$A3 You see the problem! The formula was copied vertically and not horizontally. Although the column is frozen, the rows are not. It's for that reason that the results climb so quickly. Be careful when you apply relative and absolute references. Be sure to freeze the right column or row. Video: exercice on cell referencing by calculating sales commssionsVideo: Excercice on cell referencing with If() functionVideo: Excercice on cell rferencing with Vlookup() functionPlease. The answer to the relative question about copying the formula =a1+a2 from the A3 cell to the Z20 cell is: =Z18+Z19. It adds the content of the two cells just above the formula, wherever it's located in the worksheet. Click here to return to the text. |
button. 
