range (spreadsheet)

Range in Spreadsheets

A range in a spreadsheet is a rectangular region of a spreadsheet. Many formulae work on ranges, for example =sum(A1:A10) will add up the values in the cells from A1 to A10.

Specifying a range

To specify a range, identify the cells at the top left and bottom right, for example C2 and E7. Then write the range as C2:E7.

To specify a range in another sheet, write the name of that sheet first (in quotes if it contains spaces), followed by an exclamation mark, and then the range.

'Another Sheet'!C2:E7

(In LibreOffice Calc, use a period instead of an exclamation mark.)

Copying a range

Suppose a cell contains a formula which includes a range, such as =sum(C2:E7). If that formula is copied into another cell or cells, the range is modified so that it stays in the same relative position. So if =sum(C2:E7) is in cell E8 and is copied across to H8, it will read =sum(F2:H7).

To prevent this behaviour, use dollars. That is, putting a dollar in front of part of a range specification prevents it from changing.

  • =sum($C2:$E7) will mean that the C and E don’t change.
  • =sum(C$2:E$7) will mean that the 2 and 7 don’t change.