Cell References in Excel

While using excel, there may be times when you want to keep the values same while copying formulas. This can be easily done by using certain cell references. When you are constantly using formulas in Excel, it becomes important that you know the differences between the cell references. There are 3 types of cell references: Relative Cell reference, Absolute Cell Reference and Mixed Cell Reference.

Relative Cell Reference

By default Excel uses the relative cell reference. In the following example, you can see that in cell D2 the reference of B2 and C2 are both relative.

When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (D9 in our case) and check the formula in formula bar. Cell D9 has the references as B9*C9. Similarly, if you check other cells you will notice that D3 references B3*C3, D4 references B3*B4 and so on.

So, when the cell reference is relative, it automatically changes when you copy it or move it. To simplify it, the cell reference is relative to its location.

Absolute Cell Reference(Keeping value constant in an excel formula)

When you want certain cell reference to remain unchanged or want to keep a value constant, that is the time you will have to use absolute cell reference. Like in the example below, to find the total, we require the price to be constant and thus we shall make the price cell (B1) absolute by adding a dollar ($) sign before the column name (B) and row name (1) that is $B$1.

When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (C12 in our case) and check the formula in formula bar. You will notice that cell C12 has references as B12*$B$1. The cell reference of quantity changed, whereas the cell reference of price remained unchanged as the cell reference was locked or absolute.

So, when the cell reference is absolute, the value remains unchanged. So when you want your value to remain unchanged then don’t forget to lock your cells.

Mixed Cell Reference(Keeping row/column constant in an excel formula)

As the name suggests mixed cell reference is a mix of Relative and Absolute cell references. It helps to keep one variable constant with other one changing. In a mixed cell reference either the row or the column remains unchanged. In the example below, we want the 2nd row to remain unchanged while applying the formula and thus we lock the row only and the not column.

When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (D9 in our case) and check the formula in formula bar. You will notice that cell D9 has references as B$2*C9. The price remains the same as we have locked the 2nd row.

Mixed cell reference is rarely used, but it plays an important part when you want to keep a single row or column unchanged while copying the formula.

Note: For changing the references you can also use the shortcut key F4.

If you found this article helpful, then share it with your friends and colleagues, and if you have any suggestions, let us know in the comments box below.