Absolute and Relative References

Cells in Excel have unique references, which is its location. References are used in formulas to do calculations, and the fill function can be used to continue formulas sidewards, downwards, and upwards.

Excel has two types of references:
1. Relative references
2. Absolute references

1. Relative reference

References are relative by default, and are without dollar sign ($).
The relative reference makes the cells reference free. It gives the fill function freedom to continue the order without restrictions.
Let's have a look at a relative reference example:

Let we need to calculate the 10th and 12th marks of each student. For that we need to add B2 with C2 and so on respectively cells.
The result is: D2(934):

Next, fill the range D2:D5 by dragging from the point marked in the following figure:

The references being relative allows the fill function to continue the formula for rows downwards.

Have a look at the formulas in D2:D5. Notice that it calculates the next row as we fill.

2. Absolute References

Absolute reference is when a reference has the dollar sign ($). It locks a reference in the formula.

The dollar sign has three different states:
• Absolute for column and row. The reference is absolutely locked.
Example =$A$1
• Absolute for the column. The reference is locked to that column. The row remains relative.
Example =$A1
• Absolute for the row. The reference is locked to that row. The column remains relative.
Example =A$1 Example: Data explained There are 4 buyers and they bought something, and we have the quantities they bought and the price per quantity, which is 4.

We want to find the total cost of each buyer on purchasing. The price's reference is F6, we do not want the fill function to change this, so we lock it.
The reference is absolutely locked by using the formula $F$6.

After this select the multiply operand and the cell B2 and hit enter. Then drag and drop the formula to autofill on the range B2:B5.