Spreadsheet:Absolute and relative addressing, and series fill
|MS-Excel 2007 <|
|Table of contents|
This page looks at two very common techniques in Spreadsheets. The first relative and absolute address and the second filling a series.
Objective Absolute and relative addressing, and series fill
By the end of this page you will be able to:
First a little discussion, (activities follow later...)
Relative and Absolute addressing
When copying formulas down a spreadsheet, you will notice that the program automatically adjusts the row number, and when copying across the spreadsheet the column letter. This is known as relative addressing.
When we want to use a fixed value (i.e. where it absolutely has to be that value!) we use an absolute address. This means that when you copy the cell the cell address of the absolute cell does not change.
Consider the following example:
- To compute the GST amount we multiply the amount by the GST (e.g. 10.00 * 0.125 = 12.50 ).
- If you are having trouble with percentages check out the Working with percentages (%) page.
- In the spreadsheet the total in the first row (C7) is calculated by =B7*B4
- If we copy the formula down (copy >paste) and look at the formula we will see (C8) =B8 *B5. While the B8 is correct the B5 does not refer to the GST so gives a wrong result.
- So what we want to do is freeze the GST amount (B4) so that it doesnt change when copying. To do this we use the $ sign in the cell reference.
- So back to C7 and change the formula to =B7*$B$4 (You can use F4 when the cell is entered)
- Now copying the formula will freeze the cell reference and the copied formula is now correct !
The completed spreadsheet with formulas is shown below:
Note: Repeatedly pressing [F4] will change what part of the cell is made absolute. ( $B$4 > B$4 > $B4 > B4 )
By now you probably realise that spreadsheets work really well with series of numbers. The spreadsheet designers knew this so have provided some neat tools to make this easier. So you can create series of numbers like
- 1,2,3,4,... or
- 10,20,30, or even dates
- Jan,Feb,Mar, and
- formulas (as described above)
Method: Copy down
As you can see by the diagram, all you need to do is select the cells you want to create the series from (you usually need at least 2 so that it can get a start value and how much to change by), then float the mouse to the bottom right corner until the black cross appears. Finally, click on the black cross and drag in the direction you want to create the series in.
For the curious: To create my picture I have been tricky and made a copy of my sheet by dragging the sheet tab, then while dragging used [Ctrl] so that a sheet copy is made. Then on the new sheet gone [Ctrl]+[`] (on the [~`] key)to show the formulas. Finally, used the view tab and Window > New Window, then rearranged inside excel
See if you can too !!!