How to use fixed references in Excel

When using Excel sometimes you need to fix all or part of your cell reference so that, when you copy it, it no longer changes to keep referring to the relative position of a cell, but instead stays fixed to a particular cell or a particular row or column.

This is done by inserting ‘$’ signs in the cell reference before the column reference (e.g. so B becomes $B) and before the row reference (3 becomes $3). Thus if the reference =B3 is replaced by ‘=$B$3’ the link will be fixed in this way.

Cells that refer to other Workbooks (not other Worksheets in the same Workbook) will by default be fixed – which can be changed if you need such references to be relative.

Fixing just columns (or rows)

Fixing references requires two dollar signs (e.g. $AB$31) for a reason. The first dollar sign, which precedes the column reference, fixes the column; the second fixes the row. They can be used separately, so that only the column or only the row is fixed.

Although initially the use of dollar signs to fix references may seem difficult, it is a vital part of creating efficient and reliable spreadsheets.

The F4 shortcut

If you feel that typing $ references in formulae may lead to errors and be time consuming, the F4 key (pressed while editing a cell reference in the formula bar) will cycle through the four possible fixing permutations:

B3 → $B$3 → B$3 → $B3 → B3

Applications

This ‘fixing references’ may seem quite abstract – but there are many powerful practical applications. In particular, fixing references is useful when using formulae.

In the example below, the formula in column D below calculates the difference between any given day’s sales and the sales on the best day (29/01/2010) over a short period. For this formula to work, the MAX function must always act on the whole range of sales values (so the range C4:C22 is fixed as $C$4:$C$22):

Partially fixed ranges

We have looked at ranges where the whole range is fixed (e.g. $A$1:$B$10). Just as with single cells, the columns of a range might be fixed ($A1:$B10). But also, just one ‘corner’ of the range might be fixed (e.g. $A$1:B10). When a reference like this is copied, cell reference B10 will change but $A$1 will stay fixed:

1) We want to calculate how the balance changes in the cells below the yellow one in Column F…

2) …so we write a formula that adds to the yellow cell the sum of cells between C3 and the current row…

3) …when we copy F3, the second unfixed C3 changes – eventually to C12 and the range expands like an elastic band.

Practice in your next spreadsheet. Link cells using fixed references and learn what will happen to these references upon moving the source cells.

These tips were provided by filtered.

AAT students and professional members can access a wide range of Excel resources and training.

Filtered provides algorithmically personalised Excel courses written by subject matter expert.