CELL references and ranges in Excel

Icon showing ABC

An Excel worksheet is a grid with columns labelled by letters, and rows labelled by numbers.

The highlighted cell’s reference (also referred to as ‘name’, or ‘address’) appears in the ‘name window’ at the top left position above the grid area.

A CELL is an individual ‘box’ in the grid.

Cells are referred to by:

  • a RELATIVE reference A7, or
  • an ABSOLUTE reference $A$7, where the row and column identifiers are preceded by a dollar sign ‘$’, or
  • a mix of relative and absolute:
    • $A7 – absolute column, relative row
    • A$7 – relative column, absolute row.

Relative references change when a formula is copied to another cell. Absolute references remain constant, wherever they are copied to.

This distinction between absolute and relative underlies the power of Excel to manage:

  • dynamic formulas which update if moved to another location, or additional cells and ranges are inserted;
  • formulas which refer to fixed cells which are not affected by changes to cells and ranges.
Icon of a keyboard
F4

Use F4 to toggle between relative, absolute and mixed cell references. Click in the cell with the formula. Select the cell reference to change, and press F4 to toggle between the reference types.

A RANGE is a number of adjacent cells, defined by a start and finish cell reference, separated by a colon ‘:’

  • One-dimensional, e.g.:
    • Column: A2:A10 (absolute reference for this range is $A2:$A10)
    • Row: A2:H2 (absolute reference is $A$2:$H$2).
  • Two-dimensional, a number of rows and columns, e.g.:
    • Two rows by three columns: A2:C3 (absolute reference $A$2:$C$3)
    • Four rows by eight columns: A2:H5 ($A$2:$H$5). Note that the ‘name window’ [top left box in the screenshot below] contains just the starting cell reference of the range (A2 in this example).