How to use Excel’s absolute reference ($) and why

The normal way to reference Excel’s cell is like “A1”. This is called relative reference. When copying cell containing formula with relative reference, the reference will change depending on where to paste it. If you for example paste formula containing “A1” reference to one cell above, the new reference will be “B1”. If you want to fix column and/or row of the reference so that it won’t change when pasting, you can use so called absolute references. All you need to do is to add $ to the reference:

  • $A$1 means that both column and row are fixed
  • $A1 means that column is fixed but row reference will change when pasting formula
  • A$1 means that row is fixed but column reference will change when pasting formula
Following video demonstrates this:

Leave a Reply

Your email address will not be published. Required fields are marked *