Cell References

A cell reference is a link to another 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. Copying and pasting cells containing cell references will have different results depending on what type of reference is being used. A cell reference can be one of three types: Relative, Absolute or Mixed.

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

Reference Type	Description	Example	Result after Pasting to New Cell
Relative	Relative Column & Relative Row	A2	Both the column (A) and row (2) will vary according to the distance from the original cell.
Absolute	Fixed Column & Fixed Row	$A$2	Always will be $A$2
Mixed	Fixed Column & Relative Row	$A2	The column will always be A, but the row (2) will vary according to the distance (number of rows) from the original cell.
Mixed	Relative Column & Fixed Row	A$2	The column, A, will vary according to the distance (number of columns) from the original cell, but the row will always remain 2.

Relative Reference

A relative cell reference is a 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.

• Example: Assume that cell A2 contains the relative reference to cell A1. If cell A2 is copied to cell A8, then cell A8 will contain a relative reference to A7. In this case, the cell reference is essentially saying "point to one cell above me."

Absolute Reference

An absolute cell reference is a reference whose row and column are both fixed and do not change when copied to a new location. A dollar sign ($) indicates absolute and an absolute reference will have a dollar sign ($) before both the row and the column (i.e. $A$1).

• Example: Assume that cell A2 contains the relative reference to cell $A$1. If cell A2 is copied to cell A8, then cell A8 will contain an absolute reference to $A$1. In this case, the cell reference is essentially saying "always point to cell A1."

Mixed Reference

Relative and absolute references can be used in conjunction to create a "mixed" reference. In a mixed reference, either the row or column can be ...

• Example (Absolute Column, Relative Row): Assume that cell A2 contains a mixed reference where the column is fixed to cell $A1. If cell A2 is copied to C then cell C2 will contain a mixed reference to $A1. Similarly, if A2 is copied to C3 then C3 will have a mixed link to $A2. In this case, the cell reference is essentially saying "always point to column A and one row above me."
• Example (Relative Column, Absolute Row) Assume that cell A2 contains a mixed reference where the row is fixed to cell A$1. If cell A2 is copied to C2, then cell C2 will contain a mixed reference to C$1. Similarly, if A2 is copied to D3 then D3 will have a mixed link to D$1 In this case, the cell reference is essentially saying "point to this column and always one row above me."

Creating Cell References

• From the Insert menu select Absolute Reference or Relative Reference

• Shortcuts:

  • Relative: press CTRL+SHIFT+R, or click the relative reference button.
  • Absolute: press CTRL+SHIFT+A, or click the absolute reference button.
• Double click on the cell and type the cell reference directly into the cell.
• Click on the cell and type the cell reference directly into the formula bar.