Relative cell reference

Updated: 08/02/2019 by Computer Hope

A relative cell reference describes how far away a cell or group of cells is from another cell in the same spreadsheet. When dealing with a spreadsheet formula, the relative cell reference is the default behavior of a formula. For example, to add cells A2 and B2 together you could use the formula "=SUM(A2+B2)" in cell C2. If you were to copy that formula into cell C3, it would be relative to C3 and become "=SUM(A3+B3)."

Spreadsheet

Example of a relative cell reference

=A2

The formula above is telling the spreadsheet program to show the value that occupies the cell in the first column (A) and second row (2). Using the Excel spreadsheet example picture, if this formula was placed in the B8 cell, it would show "100" since this value is in cell A2.

As mentioned earlier, relative cell references are the default behavior of any formula inserted in Excel. To prevent the cell, row, or column formula references from changing, copy using an absolute cell reference, which is any column or row starting with a dollar sign ($).

Relative column and absolute row reference

=A$2

The formula above uses a relative column with an absolute row reference. If the cell formula was copied, the row would always remain the same (2). However, the column would be relative to where it is being copied.

Absolute column with a relative row reference

=$A2

The formula above uses an absolute column reference with a relative row reference. If the cell formula was copied, the column would always remain the same (A). However, the row would be relative to where it is being copied.

Absolute cell reference with relative cell reference

=SUM($A$2+B2)

Finally, the formula above always uses the value in A2, but adds that value to the next relative cell. If the cell formula was copied from C2 to C3, it would become "=SUM($A$2+B3)."

Tip

In short, what makes the relative cell reference different than an absolute cell reference is that copying or moving the formula to different cells causes them to change. Absolute cells always point to the exact row or column described, regardless of where the formula or reference appears.

Active cell, Formula, Spreadsheet, Spreadsheet terms