Cell References - Relative/Absolute

A cell reference is a link to a different cell. When data in the source cell changes, the value in the destination cell (the one that contains the reference) updates automatically. The link is unidirectional, however, as the source cell does not know that it is being referenced by another cell.

There are two kinds of cell references:

Relative: A cell reference whose row and column addresses can vary when copied to a new location. The amount of change is equal to the distance (both row- and column-wise) between the copied cell and the pasted cell. For example, assume that cell A2 contains the relative reference A1. If cell A2 is copied to cell S43, then cell S43 contains S42. In this case, the cell reference is essentially saying "always point to one cell above me."
Absolute: A cell reference that does not change when copied to a new location. In formulas, a dollar sign ($) indicates an absolute reference; this symbol can be used in conjunction with any row or column (or both) to construct an absolute cell reference.

Relative and absolute references can be used in conjunction to create a "mixed" reference.

The following table lists the four variants of absolute and relative cell references, including what happens after they are copied & pasted to a different cell:

Description	Reference Type	Example	Result after Pasting to New Cell
Relative Column & Relative Row	Relative	B4	Both the row (4) and the column (B) vary according to the distance from the original cell.
Fixed Column & Fixed Row	Absolute	$A$1	Always the same ($A$1).
Fixed Column & Relative Row	Mixed	$D9	The column (D) is always the same, but the row (9) varies according to the distance (number of rows) from the original cell.
Relative Column & Fixed Row	Mixed	G$3	The column (G) varies according to the distance (number of columns) from the original cell, but the row always remains the same (3).

Shortcuts for Creating a Cell Reference

Relative: Press CTRL+SHIFT+R, or click the relative reference button. Cell selection mode allows you to choose the desired cell to reference. For more information, see Cell Selection Mode.
Absolute: Press CTRL+SHIFT+A, or click the absolute reference button. Cell selection mode allows you to graphically select the desired cell to reference from the spreadsheet.

Cell Reference Examples

Reference Type	Column	Row	Example
Relative Reference	Relative Column	Relative Row	Assume that cell C9 contains E14. If C9 is copied to B1, then B1 contains D6.
Absolute Reference	Fixed Column	Fixed Row	Assume that cell E2 contains $F$7. If E2 is copied to R7, then R7 contains $F$7.
Mixed Reference	Fixed Column	Relative Row	Suppose you want a cell reference that always refers to column A when copied to another cell, but the row address varies in relation to the change in distance from the original cell to the destination cell. Assume that 3 data points (5, 18, and 55) exist in cells A2, A3, and A4, as shown: In this case, you would use a dollar sign in front of the column ($A) but not in front of the row (2) yielding a cell reference of $A2, which we could place in cell B2. Cell B2 is then copied and pasted to cells B3, B4, C2, C3 and C4. Observe how the pasted cells always look to column A, but the row is determined relative to the cell in which you pasted the reference.
Mixed Reference	Relative Column	Fixed Row	Suppose you want a cell reference that always refers to row 2 when copied to another cell, but the column address varies in relation to the destination column. Assume that 3 data points (13, 20, and 26) exist in cells A2, B2 and C2, as shown: In this case, you would reference the column relatively (A) and the row absolutely ($2) yielding a cell reference of A$2, which we could place in cell A3. Cell A3 is then copied and pasted to cells A4, B3, B4, C3 and C4. Observe how the pasted cells always look to row 2 but the column is determined relative to the cell in which you pasted the reference.