Cell references

Cell references are references in one cell to the contents of another cell (e.g. a numerical value or mathematical formula). If we want to sum the values in cells B2 and B3 in cell B4, for example, we enter =SUM(B2:B3) into cell B4; then B2 and B3 are cell references:

excel2016_simplereference

Relative references

When using a relative reference (e.g. A1, B6, or E18), the reference address changes when you copy a formula from one cell to another.

Example: in the spreadsheet below, the formula =B2-B3 in cell B4 changes to =C2-C3 and =D2-D3 if the formula is copied to cells C4 and D4.

In other words, when you use relative references the spreadsheet program can adapt the cell references according to the rows and columns where the formula is copied.

Absolute references

Sometimes, we need to insert a constant value (such as pi) from a certain cell to a formula so that the cell reference with the constant value is not changed when copying the formula to another cell. In such cases, the cell reference in the formula has to made absolute. The reference is made direct with the sign $.

Example: in the spreadsheet below, the cell address for cell B2 is made direct in the formula in cell C5, so that it will remain constant when copying the formula downwards to the other rows.

A spreadsheet with rows for months from January to May, each with one figure in column B. There is a formula =$B$2*B5 in C5 (on the January row). The formula is copied to the rows below, and as a result, there are the following formulas in column C: =$B$2*B6, =$B$2*B7, =$B$2*B8 and =$B$2*B9.

The image above shows how the B2 cell is made ‘absolute’ with the help of two $ signs; the first $ sign defines the column and the second $ sign the row. You can also set the absolute reference to only apply to either the row or the column:

=C2-$D1
the column identifier (D) is not changed, but the row identifier (1) will change when copying the formula

=C2+D$1
the column identifier (D) will change, but not the row identifier (1) when copying the formula