Cell References

The three types of cell references in Excel are relative, absolute, and mixed. The differences among the types are relative references change when a formula is copied to another cell and absolute references remain the same despite where they are copied. In the case of a mixed reference, either the column or row will be absolute while the other is relative. The examples below will explain this further.

By default, a cell reference is relative. For example, if you have a formula in cell B2 that refers to cell A2 (i.e., B2 = A2) and you copy the formula to another cell, the cell reference changes. If you copy the formula two cells to the right the reference will shift two cells to the right—the formula B2 = A2 becomes D2 = B2 automatically. If you copy the formula two rows down the reference will shift two rows down, and so on. In other words, a formula that contains a relative cell reference changes as you copy it from one cell to another. Relative references are useful when you need to repeat the same calculation across multiple rows or columns.

On the other hand, an absolute or mixed reference remains the same when a formula is copied to another cell. In order to enable absolute references a dollar sign ($) is placed in the cell reference before the column or row you want to lock in, or both.

Using the example above, if we had the formula B2 = $A2 with a $ sign in front of the ‘A’ in the cell reference, the column in the formula will be absolute and locked in—this means if the formula is copied to another column the reference to column A will not change. Notice how the 2 in the cell reference does not have a $ sign—in this case, without a $ in the row reference, the row is still a relative reference and will change if copied to a different row.

To lock-in the entire cell reference, put a dollar sign before both the column and the row references in the formula. The easiest way to accomplish this is to select the formula and then click on the reference in the formula bar and press the F4 key.

Tip: pressing the F4 key on time will lock in both the row and column; pressing F4 twice will lock in the row and three times locks in the column.

Summary of Relative and Absolute Cell References:

Reference Description
A2 Relative reference: the cell reference will change if copied to another cell.
$A$2 Absolute reference: the cell reference will not change if copied to another cell
$A2 Mixed reference: the column is locked in and will not change but the row number will change if copied to another cell
A$2 Mixed reference: the row is locked in and will not change but the column will change if copied to another cell

Leave a Reply