# Spreadsheet:Absolute and relative addressing, and series fill

Home MS-Excel 2007 < |

Table of contents |
---|

## Introduction

This page looks at two very common techniques in Spreadsheets. The first relative and absolute address and the second filling 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:

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

## Copying down

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.

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

*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)