Address modes

When constructing spreadsheets using drag and drop techniques you need to be aware of the two difference address modes. Relative and absolute addressing. The following two examples will highlight the difference.

Relative spreadsheet addresses

Construct the spreadsheet shown below.

• Set out the the column headings, align them as shown and make them bold
• Use the auto series technique to generate a list of 5 items
• Fill in the prices shown, and format this column as currency.
• Enter the quantities shown in the next column

Now in the total cost column, enter the first formula as shown. You can either type in the cell references, or better type "=" then click on cell B2, the type * (i.e. multiply) then click on cell C2. The press return. Change this column to format the cells as currency.

Use the auto fill handle to drag down the formula in column D. Notice that the formula in the cells have now changed relatively. That is, the formula in each new cell (D2, D3 etc. ) still uses the corresponding values in the adjacent cell (i.e. D2 = B2 * C2, D3 = B3 * C3 etc.)

This is the default mode when dragging cells and is known as relative addressing.

Note: Only the row (numeric part ) of the reference changes, the column reference remains the same. I.e.
= B<number> * C<number>

To complete this example and the label Grand total in cell C8 and use the Sum function to calculate the overall total. The quickest way to do this is to highlight cell D8, then click the summation symbol in the formula bar. It will guess the range of cells to be included, which if correct you can simply accept. Alternatively you can type the formula as shown.

Absolute Addresses

To illustrate absolute addresses we will use as an example a wage bill. Set up the spreadsheet as shown below

In this example everybody listed works for the same hourly rate. The formula in cell C2 for Tom is = B2 * C9. I.e. Hours worked * Hourly rate.

You cannot simply use the auto fill handle to drag this formula down for the other workers because the references to both the hours worked and the hourly rate change. Using the auto fill, as the example below shows, Julie's wages are calculated as B5 * C12. The latter cell reference should have been fixed to point to cell C9.

To prevent C9 reference changing, before using the autofill, the formula must be changed to make this an absolute address as follows.

     = B2 * $C$9. 

The $ sign fixes absolutely both column and row. (it has nothing to do with the fact that this column is formatted as a currency). In fact in this particular example it is only necessary to make the row reference absolute, so the formula,

     = B2 * C$9

works equally well when you use the auto fill handle. Fix the formula for Tom, and use the autofill handle to complete the additional formula. Notice this time the reference to cell $C$9 remains fixed. When using the auto fill handle, or cut and paste techniques, its good practice to always check that the formula's are correct.