Copying Formulae
Objective: To duplicate a formula across a range of cells.
Instructions: You will be using a simple mouse technique for this exercise.
Comment: It is helpful if instead of retyping a similar formula several times you can simply copy it.
Activity 6.1 We wish to copy our Totals formula down the sheet. Select the
cell F6. The cell highlight appears around the cell with the little black box
in the bottom right hand corner. In the same way in which we filled in the
series of dates in Task 3, move the mouse cursor over the black box until it
changes to a black crosshair. Click and hold down the mouse button. With the
mouse button still held move the cursor down the sheet until you get to cell
F19. A ghost black outline should follow the cursor as you move down the
sheet. When you get to cell F19, release the mouse button.
You will notice that numbers fill the F column, but they are all different. Click on cell F6 again to deselect the range. Now using the arrow keys move the cell highlight down the F column. As you do this watch the formula bar. You will notice that as you move down the column the formula changes to match the row it is in. What has happened is that the formula has been copied down the column, but the cell references have been changed to take account of the position of the formula. This is called relative cell addressing and is what happens automatically on a spreadsheet when you copy formulae.
When you enter in a cell reference, say C6, the spreadsheet actually stores that as a reference relative to the cell you are typing into. Thus if you are in cell D5 and you reference C6, what the spreadsheet stores is "The cell one down and one to the right". So when the formula gets copied into cell D7 the reference "one cell down, one cell to the right" now becomes C8.
Activity 6.2 The cell G2 contains a number that we want to use in a calculation. Select the cell G5 and type: =F5+F5*G2. Press <Enter>.
Copy this formula down the column in the same way as you did above. When the formula has been copied, browse through the individual formula. You will notice that G2 was addressed relatively, so that the formula in G8 contains a reference to G5 and so on. Some cells display an error message #VALUE! because the formula is trying to multiply a number by the text in F4. Yet we want every formula to reference G2!
Activity 6.3 Select cell G5 again, but this time type: =F5+F5*$G$2.
Press <Enter>.
Now copy this formula down the column in the same way as you did , and browse through the cells again. Notice that whereas all the references in the F column change according to what row they are in, $G$2 remains the same. This is because we have defined G2 as an absolute reference by putting the $ sign in front of both the column letter and the row number. Now whatever we do to the formula, it will always refer to the value in G2.
Select G2 and type in 0.2. Notice how all the values in column G change
with the new value.
OSU | CoF | Community | Search | Forestry Computing Helpdesk
Disclaimer
Last Updated Monday, 25-Aug-1997 13:03:54 PDT