This tutorial is intended for the beginner at Microsoft Excel and covers the topic of relative and absolute cell references. In a previous tutorial I discuss the use of the fill down command to speed up the entry of data. However, one of the common issues I see when people try to use the fill down command is a lack of awareness that Excel treats all references as relative cell references, unless told otherwise.
A relative reference can be thought of in terms of its position from the current cell. For example if my formula contains a reference to a cell that is two spaces to the left then when II use the fill down command the reference will always be two cells to the left.
To demonstrate this I’ve created a video above which shows this particular issue in action. When using the fill down command it should be remembered that as the formula is filled down from row to row, each of the cell references will increment by the same amount. For example a reference to the cell A5 will become a reference to A6 on the next row and then to A7 on the following row.
To prevent Excel from continually incrementing the reference it is necessary to define what is called an absolute cell reference. To define an absolute cell reference we precede the letter and the number of the cell reference with the dollar symbol $. So a reference to the cell B1 would be made into an absolute reference using $B$1.
Now as the fill down command is used, each row will continue to reference B1.
Although beyond the scope of this particular tutorial it is worth mentioning that the dollar symbol can be used before just one of the characters. For example $B1 will result in an absolute reference to the B column but a relative reference to the rows. Conversely B$1 will result in a relative reference to the B column but an absolute reference to row one.
If you liked this article then share it with your friends and subscribe to our monthly newsletter.