Moving Referenced Cells
Objective: To move cells to see the effect on the formulae.
Instructions: You will use the mouse.
Comment: Once you have set a spreadsheet up it is often necessary to edit it and move bits about. In order to do this successfully it is necessary to know what the effect of altering the layout of the spreadsheet will have on the existing formulae.
Activity 7.1 Select the cell G2 containing the figure for VAT. Move the
mouse cursor over the thick black border until it changes to a white arrow,
click the mouse button and with the button held down move the outline to I4 and
release the mouse button. The numbers in column G do not changed. Select cell
G5. You will see the formula below displayed in the formula bar:=F5+F5*$I$4
All the absolute references to $G$2 have changed to become $I$4. Now move the cell back to its original position in G2.
Activity 7.2 Select cell G6. Move this cell across to I8. Check to see if the formula has changed. Move it back.
Activity 7.3 Move the mouse to the row labels (the numbers) on the left hand side of the spreadsheet. Click on row label 7. The whole of row 7 should be highlighted. From the Edit menu select Insert. A new blank row is inserted into the table.
Now select the cell C21 which has the AVERAGE formula in it. What has happened to the cell references compared to when you typed them in?
Select row 7 again (the blank one) and then from the Edit menu select Delete. Does the AVERAGE formula still make sense?
Excel is able to keep track of what it's formulae reference and where those references go when you alter the layout of a spreadsheet. This means that it is usually safe to add row and columns or move cells about without any adverse effects to your calculations. Having said that, it is always a good to maintain a healthy degree of suspicion for your spreadsheets results.
Activity 7.4 You will have noticed that as we have been copying formulae down columns, some of the formatting applied to the cells has been removed. This is because when you copy or move a cell you copy or move its contents plus its formatting. Now would be a good time to reinstate the missing formatting and save your worksheet. You will need a:
medium thick right hand border on the range A5:A19.
normal thickness bottom borders on ranges A8:G8, A13:G13 and A18:G18
medium thickness bottom borders on ranges A9:G9, A14:G14 and A19:G19
To apply a border select the range a choose Borders from the Format menu. A dialogue box will appear in which you choose the borders you require. When you have finished save your worksheet.
OSU | CoF | Community | Search | Forestry Computing Helpdesk
Disclaimer
Last Updated Monday, 25-Aug-1997 13:03:42 PDT