Absolute Reference

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: $. 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 track the changes in column F easily using the =FORMULATEXT function.

reference example

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:

  1. Start typing the formula using an equal sign. For example, =C5:C10
  2. After you type a cell reference, press the F4 key once, your reference looks like =$C$5: $C$10
  3. 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

Additional resources: