Fixing cell references
Fixing cell references (otherwise known as Absolute Referencing) is something that seems to confuse a lot of people – being able to change a cell reference within a formula to a fixed Cell, Row or Column…
The reason why you’d want to consider doing this is where part of your formula always needs to refer to the same row or column; in its simplest case this could be to set up a “Times Table” or an invoice where you want to apply the same VAT Rate. To do this, we use the dollar sign ($) in our Excel formula.
Relative cell references
Excel’s “Normal” behaviour is that when you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position, so, for example,
If we have the very simple formula “=A1” in cell B1 it will change as follows when copied and pasted:
- Pasted to B2, it becomes “=A2”
- Pasted to C2, it becomes “=B2”
- Pasted to A2, it returns an error!
In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula. The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A. This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in; as I’ve mentioned, this is Excel’s default behaviour.
However, as noted above there will be some situations, you want some or all of the references to remain fixed when they are copied elsewhere. This is where the dollar sign ($) is used.
Absolute cell references
If you’re creating an invoice, you’ll only want to enter the VAT rate once, but you may well want to show the amount of VAT for each line item. In this case, you would fix the cell reference containing the VAT rate, by converting the cell reference. So, if, for example, the VAT Rate was in Cell E11, every cell that referred to this would use “=$E$11”, for example…
If we expand the cells to show the formulas we can see this in action…
Mixed cell references
This is where only part of the cell reference is fixed using the dollar sign. If you want to fix the column, the “$” goes to the left (or in front) of the column letter; to fix the row, it goes to the left (or in front) of the row number.
So, if we always want to refer to row 1 when copying a formula down, we use, for example, “=B$1” and “=$A2” will always refer to column A.
An example of a Times Table will probably show this best…
Expanding to show the formulas…
If we’d not used the $ sign appropriately & had the first formula in cell B2 as =A2*B1, we’d have ended up with a far different result!
The strange numbers towards the bottom & right of the table are in Scientific Notation – in F6 it means 6.37 x 10 42 times! Somewhat larger than the 25 that we were expecting!
Used correctly to fix a cell reference where appropriate means that formulas can still be copied down or across your spreadsheet quickly & easily
A final Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.