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 points to an actual fixed location on a worksheet.
This article is a part of our definitive guide on Excel Formulas.
How to use absolute reference
The key is the dollar sign: $. For example, insert a dollar sign before the row or column id if you want to fix rows or columns.
Let us see an example of how the absolute reference works for cell B1:
=$B$1
You can apply it for a range (like B6:B8) too:
=$B$6: $B$8
Example
In the example that appears, the formula in D5 will change like this when copied down column D. You can easily track the changes in column F 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 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 easily switch between references (relative, mixed, absolute) by pressing F4.
Learn more on how to toggle between references.
Additional resources:
