Cell References in excel

It’s always a best practice to use cell references in formulas instead of typing actual numbers. It makes the formula dynamic i.e. your formula will produce updated result when the values are changed on those cell references, it makes the formula robust.

One of the major advantages of using cell references in excel formula is that we could simply copy and paste the formula across rows or columns to calculate the results in a data table.

There are 3 kinds of references in excel:

Relative cell reference
Absolute cell reference
Mixed cell reference

We could just copy by Crtl+C and paste the formula at some other location by Ctrl+V ,this saves a lot of time and also its less prone to error as compared to writing formula each time.

Relative Cell References

Relative cell references are very simple and they are referred simply by their column alphabet and row number like A3 which denotes it’s in 3^rd row of column A.

When formula are copied which contain relative cell reference, excel changes the cell addresses relative

To the row and column they are moved to i.e. Excel will not create an exact copy of the formula or function whereas the new cell reference in the formula would be in relation with the new cell that the formula is pasted to.

EXAMPLE: If a formula of ’=A2+B2 which is available in cell C2 is copied to cell C3, the formula would

changes to ’=A3+B3’ to reflect the new row and which will add the values in cell A3 and B3 as shown below in the picture.

Both the row number and column will change in case of relative reference.

Absolute Cell References

Absolute cell reference always refers to the same cell irrespective of where you paste it; it locks the reference and does not move with the pasting location hence the name Absolute.

Dollar sign “$” is used to make a reference absolute one (’$’) before the column

And row identifiers for the cell which is referred.

EXAMPLE: When we copy the formula ’=A5*$B$3’ from cell D7 to cell D8, you would see the relative cell reference changes ’A7’ to ’A6’, but the ’$C$1’ which is an absolute cell reference remains unchanged.

Give it a try in your excel workbook as a practice exercise.

We use absolute reference in a formula when one value that is used in the formula is constant, one example could be rate of interest which will be constant for the calculation irrespective of principal and other factors.

We could enter the rate of interest value in one cell ad could use that as an absolute reference for all of our calculations also it gives us a lot of flexibility over typing and hard coding the value for each of the calculations, even if the value changes, we need not change the value by going into each of the cell that uses it, we will just go to that particular cell and update the value and absolute reference will take care of the rest.

In the above example I have entered the rate of interest in cell G2 and have calculated the simple interest in column C, by using formula which is displayed in column D.

Simple interest=(Principal X Rate X Time)/100

And since the rate is constant here, I have made rate an absolute reference by using dollar sign $G$2.

By doing this I have prevented excel from changing the reference of rate i.e. G2 to any other cell.

Mixed Cell References

The combination of absolute and relative cell reference is called mixed cell reference. In mixed cell reference either row reference is locked or column reference is locked based on the requirement.

Add a dollar sign “$” before the entity, either row or column to make it absolute.

EXAMPLE 1

The reference D$4 has row number 4 as absolute and it will not point to any other row irrespective of where we paste it but column reference will change as D is not prefixed by dollar sign here similarly the formula $C6 is also mixed reference where column C is absolute and it will not point to any other column irrespective of where we paste it but row reference will change as 6 is not prefixed by dollar sign here.

We could easily add dollar sign by pressing F4 key accordingly.

In the cell where you want to type the formula, enter “=” sign and then enter the cell reference by manually adding or selecting by mouse.

Adding dollar sign in excel formula by F4 key

F4 key press	Functionality
Press F4 once	Two dollar signs (’$’) are entered (both the column and row identifiers are absolute).
Press F4 again	The column identifier is now relative and the row identifier is now absolute.
Press F4 again	The column identified is now absolute and the row identifier is now relative.