By default, Excel uses relative referencing, that’s mean if you want to use the same formula somewhere else in the datasheet, it will be automatically changed with its original reference. In some cases, a relative reference is useful, but when you want to apply the same formula with absolute reference, you need to slightly change the formula. In this post we will check how how absolute and relative references work using a simple example.
To get started, we will create a simple datasheet that illustrates relative cell references. Enter values in the data sheet as shown in the screenshot below.
We have written a simple SUM formula, if you copy the formula by selecting the cell that contains the formula, and paste it in another cell, it would not show the same result i.e. (A1+A2=90) but yields 40, as you can notice in the screenshot below, that the formula pane shows cell A2 instead of A1. This happens because of relative cell referencing.
When you copy the formula, Excel doesn’t only copy it, it remembers the position of the cell and on pasting it into another cell, it pastes the formula with its original cell reference, as we pasted it beneath the cell, which contain value 90. In our case, Excel also changes the location to one cell down, referring to A2 and A3.
For copying the exact formula into other cells, we need to write it in a slightly different way, for example if we want to copy the SUM formula, we will write it with absolute reference, that contains $ sign;
As you can see in the screenshot, Excel ignores the reference when formula is written with $ sign. So, if you want to use the same formula without changing the cell location, just use $ with cell original location.
The absolute referencing may help you easily use the same formula or function in different data sets. Unlike relative referencing, it doesn’t change the cell location and perform the action over the same cells where you first used it. It must be noted that you cannot drag the formula cell to other fields/rows to apply the formula on corresponding cells, as doing so will yield the same result. To quickly apply a function or formula over the corresponding cells, relative referencing must be used.