Updated: May 26
By: Tony Gaskell
If you had this table and copied the formula in the Profit column down, then it would be correct. The next cell formula would be =B3-C3, then next would be =B4-C4, and so on.
This is because it is using relative cell references – internally, the formula =B2-C2 is essentially ‘subtract the value in the cell one cell to the left (C2) from the cell two cells to the left (B2)’.
So when you copy the formula, it is using that relativity to infer what the other formulas will be.
Excel logically expects this to be the case and most times it is.
However, there are times when you don’t want this to happen, and that’s when you need to use absolute cell referencing.
Consider the table to the right.
The VAT value is calculated using the cost multiplied by the VAT rate, which is stored in cell F1.
If we were to copy this cell down, the next cell would read =B3*F2.
The B3 is correct, but we want the cell reference of the VAT rate to be consistent and unchanging – absolute.
We do this by putting $ signs in front of the row letter and column number =B2*$F$1
When this formula is copied now, the $ signs will ensure the cell reference doesn’t change.
If you want the column to remain constant and the row to change, or vice versa, you can put the dollar sign in front of just one, e.g. =B2*F$1 will mean that the column will change but the row won’t.
TIP: You can put the dollar signs on a cell reference by selecting it and pressing F4.