Absolute cell reference

Updated: 12/29/2017 by Computer Hope

In Microsoft Excel and other spreadsheet programs, an absolute cell reference or ACR is a spreadsheet cell that remains the same and does not change regardless if it is copied or moved.

Absolute cell reference overview

One can identify an absolute cell reference by looking at the formula of the cell. If the formula contains one or more dollar signs ($), it is an absolute cell reference or a partial absolute cell reference. In the example below, the formula bar shows "=SUM(D2:D5)" which is a relative cell reference and not an absolute cell reference.

Spreadsheet

Examples of an absolute cell reference

Absolute column and absolute row reference

=$A$2

The above formula is telling the spreadsheet program to show the value of cell A1 in the current cell. For example, if we were using the above Excel spreadsheet and entered this formula into cell B8, it would always show "100" since that is the current value in cell A2.

What makes the absolute cell reference different than a relative cell reference (e.g., =A2) is that if we copied the cell formula in B8 and placed it in B9, it would still be "100." A relative cell reference would have used the next relative cell for its value, which would have been cell A3 and would have a value of "101" in the cell.

Relative column and absolute row reference

=A$2

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

Absolute column with a relative row reference

=$A2

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

Absolute cell reference with a relative cell reference

=SUM($A$2+B2)

Finally, the above formula will always use the value in A2, but add 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 reference appears.

Why would anyone need to use an absolute cell reference?

An absolute cell reference is not used as much as a relative cell reference. However, using an absolute cell reference can be helpful for when you need to copy a formula into multiple cells. For example, if you need a formula that always uses the same cell value in multiple cells, if the formula was using a relative cell reference it would change values when copied. However, if the formula contained an absolute cell reference, it could be copied anywhere and always keep the same cell value.

Absolute location, Active cell, Formula, Relative cell reference, Spreadsheet, Spreadsheet terms