0

When you refer to a cell in an Excel formula, you can use any of three different ways of referring to that cell, known as relative, absolute and mixed references.

Relative cell references are the most commonly used. A relative cell reference in a formula is based on the position of the formula’s cell relative to the cell to which it refers. That means if you move the formula cell, or copy it elsewhere, the reference changes.

You denote a relative reference simply by using the cell’s column letter followed by its row number: A1. A simple formula that uses relative cell references to add the numbers in cells B1 through B9 is:

=SUM(B1:B9)

If you place this formula in cell B10 and then copy it across from B10 to C10, Excel makes the sensible assumption that you want to total the values in the same relative positions in column C – that is, cells C1 to C9 – and so it automatically adjusts the formula to read:

=SUM(C1:C9)

An absolute reference refers to a cell in a fixed location. Such references come in handy when you want to refer consistently to the same cell, or range of cells, throughout a worksheet.

For example, if you use a worksheet to estimate a mileage allowance for business travel, you could put the allowance rate per mile in cell D2 and then use an absolute reference to that cell anywhere you use a formula based on the mileage rate. To indicate an absolute reference use dollar signs, thus: $D$2.

Name that cell

You can make life easier for yourself by naming a cell or range of cells. This is particularly handy when you want to refer to a certain cell repeatedly in formulas. When you name a cell, Excel automatically makes it an absolute reference.

For example, to name our mileage rate cell in Excel 2007:

  1. Right-click the cell and select Name A Range from the pop-up menu.
  2. Type a name in the Name box, such as MileageRate and click OK. Although you can’t include spaces in the name, you can use underscores (Mileage_Rate), periods/full stops (Mileage.Rate) or slashes (Mileage\Rate). Excel ignores the case, but it can make your worksheets more readable if you mix lower- and uppercase.

To name the cell in Excel 2003:

  1. Click the cell.
  2. Click Insert -> Name -> Define.
  3. Type in the name, MileageRate, and click OK.

Once you’ve named the cell, you can use its name in any formula, thus:

=E7*MileageRate

As you define the name, notice the value in the Refers To box. You’ll see the full absolute reference consists of the worksheet name followed by an exclamation mark and the absolute cell reference, for example:

=’Travel Expenses’!$D$2

That means you can use the named reference – in our case, MileageRate – in any worksheet in your workbook, not just the current worksheet.

Specifying the scope of a name

That’s great, but what if you’ve set your workbook up with a separate worksheet for each employee and each of those employees has a different mileage rate? In that case, you won’t want a reference to MileageRate on Mildred’s worksheet grabbing the value from Darren’s worksheet.

In Excel 2003, you deal with this situation by specifying the worksheet when defining a name: in the Name box, first type the current worksheet’s name, followed by an exclamation mark, followed by your chosen cell name. For example:

Darren!MileageRate

Excel 2007, makes this easier by including a Scope box in the New Name dialog: when you define the name, select the current worksheet from the drop-down Scope box to restrict the reference to the current sheet, and Excel will name it appropriately.

Deleting a name

If you ever need to delete a cell/range name, in Excel 2003:

  1. Click Insert -> Name -> Define.
  2. In the Define Name dialog, click the name you want to delete then click the Delete button.

In Excel 2007:

  1. On the Formulas tab click Name Manager.
  2. In the Name Manager dialog, click the name you want to delete and click the Delete button.

Mixed references

Mixed references are a combination of relative and absolute: either the column is relative and the row fixed (absolute), for example D$2, or the column is fixed and the row relative: $D2.

When would you need such a reference? One case is when you create any table where the values are derived by multiplying the x axis by the y axis. A multiplication table is the simplest example of this.

The easiest way to get a feel for mixed references is to give them a try:

  1. Place the values 1 through 12 across in cells B2 to M2. This is the x axis of your multiplication table.
  2. Place the values 1 through 12 down in cells A3 to A14. This is the y axis of your multiplication table.
  3. In the top, leftmost cell of the results grid, cell B3, enter the formula: =B$2*$A3.
  4. Copy that formula down and then across to include the entire table.

This formula translates as: multiply the value in row 2, column x by the value in column A, row y. For the first cell referenced in the formula, the row remains constant (row 2, the x axis where you placed the values 1 through 12) while the column changes. For the second cell reference, the column remains constant (column A, the y axis where you placed the values 1 through 12), while the row changes. No matter where you click in the table, you’ll see row 2 and column A referenced in the formula bar, while the other values vary.

Changing reference types

If you find you’ve used the wrong type of reference in a formula, Excel offers a shortcut for changing the reference:

  1. Click the offending cell.
  2. In the formula bar, click the incorrect cell reference and press F4. Each time you press F4, Excel will cycle the reference through relative -> absolute -> mixed (relative/absolute) -> mixed (absolute/relative).
  3. Repeat this process for each incorrect reference in the formula.

A memory jogger to help you remember to use the F4 key for changing references is to take a look at your keyboard: notice how the $ symbol, used to denote absolute references, is above the number 4. Use it as a reminder (a trick I learnt from a Kiwi school teacher).