Cell References

In this lesson you can learn about types of cell references in Excel.

In Excel, there are three types of references:

Relative reference

  • Relative reference is a reference to the relative position of the cell that contains the formula and the cells to which it refers. When you change the position of the cell that contains, the formula changes the reference. By default, new formulas use relative reference. For example, if you copy a relative reference one cell down it will automatically change from =A1 to =A2

Absolute reference

  • Absolute reference is an absolute cell reference in a specific location. When you change the position of the cell containing the formula, the absolute reference remains unchanged. When you copy the formula across rows or columns, the absolute reference Excel doesn’t match it. To change the relative reference to absolute, use the $ symbol. For example, if you copied the absolute reference, reference =$A$1 doesn’t change

Mixed reference

  • Mixed reference is an absolute reference to column and relative to the row, or absolute to the row and relative to the column. Absolute column reference looks like that: $A1, $B1. Absolute reference to row looks like that: A$1, B$1. When you change the position of the cell containing the formula, the relative reference changes, and the absolute reference does not change. When you copy a formula in rows or columns, the relative reference is automatically matched, and the absolute reference does not match. For example, if you copy a mixed reference one cell right it will match the appeal of =A$1 to =B$1

In one formula you can use both relative reference and absolute.

A quick way to convert addressing mode is to press the F4 keyboard shortcut. Pressing this key changes the address. Of course you can type the $ (dollar) sign from the keyboard.

Previous articleSheet Formatting
Next articleEntering formulas