An absolute reference in Excel keeps your formula fixed. So, if you copy the formula to another position, nothing will change. Unlike a relative reference, it is pointing to an actual fixed location on a worksheet.
How to use absolute reference
The key is the dollar sign: $. If you want to fix rows or columns insert a dollar sign before the row or column id.
Let us see an example of how the absolute reference works for cell B1:
You can apply it for a range (like B6:B8) too:
=$B$6 : $B$8
In the example that appears, the formula in D5 will change like this when copied down column D. You can track the changes in column F easily using the =FORMULATEXT function.
Good to know that the absolute reference to C3, which contains the day’s rate, does not change! But the reference to ‘Working Days’ in C5 changes with each new row.
Steps to to insert a dollar sign and make relative reference to absolute
If you want to add a dollar sign to your cell or range you can use two easy methods:
- Start typing the formula using an equal sign. For example, =C5:C10
- After you type a cell reference, press the F4 key once, your reference looks like =$C$5: $C$10
- Excel changes the default relative reference to cell to absolute!
Tip: you can switch easily between references (relative, mixed, absolute) by pressing F4.
Example: The cycle of references (controlled by F4)
Type =C5 and press F4
- once, the result is =$C$5
- twice, the result is = C$5
- three-times, the result is =$C5
- four-times, the result is the default, =C5