Spreadsheet: Relative and absolute cell addresses

If you want to use the value of a cell in a formula in another cell of the spreadsheet, then you refer to this cell by means of its cell address. This cell address consists of a column indicator and a row number, e.g. cell D14 is the cell in column D, row 14.

When a cell address includes a $ character before the column or row number, the address is considered as an absolute address. When cell C10 contains for instance the formula $B$5+$B$6, the actual addresses of these two cells are stored in memory. When the formula is copied or moved to cell C11, e.g. by inserting a row at row 9 by pressing function key F3, the formula will still be $B$5+$B$6.

When a cell address does not include any $ characters, the address is considered to be a relative address. This means that the program does not store the actual address, but instead the program stores the number of columns and rows calculated relative to the cell containing the formula. When cell B5 contains the cell address B4, then the program does not store the address B4 as such, but it stores how to get from cell B5 to cell B4, in this case one row up. When the formula 2*B4 is copied from cell B5 to cell D10, the formula will be changed to 2*D9.

When cell B1 contains the formula SUM(B2:B10), and this formula is copied to cell C1, the formula will be converted to SUM(C2:C10). When it is copied to cell D51, the formula will be converted to SUM(D52:D60).

A cell address or formula may contain a combination of absolute and relative cell addressing, e.g.: A$2, $B10, $C5, SUM(B3:B20)*$A$1.

Ranges of cells are identified by two cell addresses separated by a colon. These cell addresses may also include relative and absolute cell addresses, e.g. B2:E8, $A$10:$A$25, D10:D10, etc.

When you design a spreadsheet, you should pay attention to when to use relative and when to use absolute cell addressing.

Spreadsheet: Relative and absolute cell addresses

See also