Excel allows you to use a reference to a cell in a formula and supports two types of references: absolute and relative. An absolute range is preceded by a dollar sign ($) and indicates that the reference should not change when the cell is filled down or copied to a different cell. A relative reference changes as the formula in the cell is copied to a different cell.
For example, you might enter =A2 in a cell, such as in this example:
The formula in B2 is also in C2 so you can see the output. In this case, we’re using a relative reference in the formula. When we copy the formula down to the other cells, you can see it automatically changes:
These are all relative references. There are four ways you could setup a reference:
Relative Column & Row: =A1
Relative Column & Absolute Row: =A$1
Absolute Column & Relative Row: =$A1
Absolute Column & Absolute Row: =$A$1
If we set our reference to $A$2, you will see the output like this:
As we fill the formula down, you can see it retains the reference to A2: 45.
If you select a reference in a formula, you can press F4 to cycle through the four different types of references, like this:
You can mix absolute and relative references within a formula, e.g. =$A2+$A$3+A4 has a mix of absolute and relative cell references and will only change the relative cell references when copied or filled down.
If you are working with multiple worksheets and copying and pasting between them, the reference will be made to the worksheet you are working in. If you want a true absolute reference to cell A2, you can use the INDIRECT formula,
=INDIRECT(“A2”)
The INDIRECT formula reads whatever is in it as text, not a cell reference, so it will not change as that formula is copied around a spreadsheet.
You will find absolute cell references very useful as you design formulas in Excel and create complex worksheets.
