Spreadsheet:Absolute and relative addressing, and series fill

From virtualMV2015wiki
Jump to: navigation, search
 Home  MS-Excel 2007 <

Introduction

This page looks at two very common techniques in Spreadsheets. The first relative and absolute address and the second filling a series.


Icon Objective.png

Objective Absolute and relative addressing, and series fill

By the end of this page you will be able to:

  • understand the difference between absolute and relative addressing
  • create an absolute cell reference
  • to automatically fill a series

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:

Excel 2007: GST Table‎‎
  • To compute the GST amount we multiply the amount by the GST (e.g. 10.00 * 0.125 = 12.50 ).
  • 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:

Excel 2007: Formulas‎‎

Note: Repeatedly pressing [F4] will change what part of the cell is made absolute. ( $B$4 > B$4 > $B4 > B4 )

Copying down

Excel 2007: Fill series‎‎

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.


Icon Activity.png

Activity

  1. Type in your name and the titles (GST Table, Amount, GST, Total)
  2. GST Amount
    1. Enter the value as 12.5% (don't forget the % sign)
  3. Amount Series
    1. Enter 10 and 20 in the amount column
    2. Select both the 10 and 20 (click and drag) - release the mouse button
    3. Float your mouse to the bottom right of cell B8 (the black + should appear)
    4. Click and drag the black cross down to fill the series
  4. Calculate GST amount
    1. In C7 enter = then use the mouse to click on the amount (B7), click * (times) then click B4 (GST). Now press [F4] and the cell value should change to $B$4. The formula in C7 should now read =B7*$B$4
    2. Copy this formula down (click the mouse on each one created to see that indeed they are correct)
  5. Enter the formula for the total and fill down
  6. Test your worksheet by changing GST to 10% (the numbers should be easy to see if they are correct)

Extension exercise

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 !!!


Spreadsheet:Absolute and relative addressing, and series fill. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 17, 2017, from http://www.virtualmv.com/wiki/index.php?title=Spreadsheet%3AAbsolute_and_relative_addressing,_and_series_fill    (zotero)