Cell references in Excel are a big deal, getting them correct can mean the difference between time saved and time lost scratching your head.
By learning how to use cell references effectively in your formulas you are able to write more formulas and create more complex workbooks in a shorter space of time
What does ‘Relative’ mean?
Relative cell referencing is where the formula will change based on the relative position of rows and columns. Excel uses relative referencing by default. For example, if your formula is
Dragging the formula down 1 row would change it to
=A2+B2 i.e the position of the rows has moved +1 because you have dragged the formula down +1 row
Dragging the formula across 1 column would change it to
=B1+C1 i.e. the position of the columns has moved +1 because you have dragged the formula across +1 column
What does ‘Absolute’ mean?
Absolute referencing is when the cell reference remains constant, or locked, to a cell. You can make either the row, column or row & column absolute by typing a $ before the cell reference. For example,
=$A1 makes column A absolute
=A$1 makes row 1 absolute
=$A$1 makes column A row 1 absolute
Why is Absolute Referencing useful?
Perhaps you have a data set with values in columns A-E however, you would like to know what the value of column A * Column B, and Column A * Column C and so on is. By using absolute referencing against column A, you can enter the formula once, drag it along and get the results. Relative referencing would not allow you to do this.
If the cell you need to make absolute always resides in a specific row, you need to move the $ along to the row reference part of the formula.
What about if you have a single cell that contains your multiplier for example, an exchange rate? Simple, make the whole cell reference absolute.
Instead of constantly typing $ in your formulas, Excel has a handy hotkey! Highlight the portion of your formula which you want to make absolute and press the F4 key. Excel will cycle through the options (full, row, column) and enter the $’s for you.