Monday, 4 February 2013

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:

1 comment:

  1. Nice and compact guide. I use it as a cheat sheet for how to use it. If you want to know for what and in what case you should use it, i found another guide for excel absolute reference. Just keep in mind informationen is spread al little too thin there, so i still prefer the compact text.