Cell Referencing – What does $ in Excel formula do?


Explaining Cell Referencing and what $ in Excel does

The $ in Excel is used to control absolute cell referencing in your formulas. There are 2 types of references:

  1. Relative references
  2. Absolute references

If you have ever written a formula referencing cells and copy / pasted your formula into other cells, you may have noticed that Excel changes the value of of the row or column position on some of the cell references as you paste your formula.

1. Relative references

When you’ve referenced a cell within a formula and you are now copy/pasting that formula into other cells, the cells referenced within the formula will change relative to the original position copied from.

If I have a formula =B2*3 and I copy/paste it:

  • one cell to the right, it will change to =C2*3 (column has changed)
  • one cell to the left, it will change to =A2*3 (column has changed)
  • one cell down, it will change to =B3*3 (row has changed)
  • one cell up, it will change to =B1*3 (row has changed)

2. Absolute references (using the $ in Excel) lets you control whether you keep:

  • Row/Column (entire cell) fixed, which would look like “$B$1“. The $ needs to go in front of the column AND row.
    • Copy/pasting:
      • Into any other cell, the formula will always point to “B1” since the entire cell is set to an absolute value.
    • Why use this? You have a value in a cell that you always want to reference regardless where the formula is copy/pasted.
  • Row fixed (column relative), which would look like “B$1“. The $ needs to go in front of the row only.
    • Copy/pasting:
      • Up/down into any new rows, the formula will keep the row # (in this case ‘1’) fixed and never change it.
      • Left/right into any new columns, the formula will adjust the column # relative to the position the formula is being pasted into.
    • Why use this? You might have values in a ‘header row’ that you want to reference in a formula, so you want your column # to change when you paste into a new column, but you always want your row # to stay fixed on your header row.
  • Column fixed (row relative), which would look like “$B1“. The $ needs to go in front of the column only.
    • Copy/pasting:
      • Up/down into any new rows, the formula will adjust the row # relative to the position the formula is being pasted into.
      • Left/right into any new columns, the formula will keep the column (in this case ‘B’) fixed and never change it.
    • Why use this? You might have values within a column that you want to reference in a formula, so you always want your column # to stay fixed when you paste into a new column, but you want your row # to change when you paste into a new row.

There are 2 ways you can apply absolute references to your formulas:

  1. Manually type the $ into your formula (actually click into your formulas and type the $ sign as required)
  2. Use the F4 hotkey

How to use the F4 Hotkey

  1. Click on the cell with the formula that you want to change the cell reference type
  2. Either click into the formula bar or press F2 to go into ‘Edit mode’ in the cell
  3. Select the part of the formula that you want to change the cell reference type
    • Single cells (you can either highlight the entire value, i.e. “A1” or click on either side or in between)
    • Ranges (you need to highlight the entire value i.e. “A1:A3”)
  4. Press F4 once (absolute reference on both row and column)
  5. Press F4 again (absolute reference on row only)
  6. Press F4 again (absolute reference on column only)
  7. Press F4 again (back to relative reference)
  8. Note: If you continue to press F4 it will just continue to cycle through the same sequence as listed above.

 

Go back to Excel Tutorials.