2021What are the four types of cell references in excel with example video.Relative, Absolute,mixed,circular

 Most formula you create including references to cell or ranges . These references enable your formula to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed value . For example, if your refers to cell A1 you change the value contained in A1 , the formula result changes to reflect new value . If you didn't use references in your formula , you would need to edit the formulas themselves in order to change the value use in the formula . 

there are mainly four type of cell references.

                                       

Relative cell reference

 it refers to the cell by their position in relation to the cell that contains the formula . The row  and column references can change  when you copy the formula to another cell because the references are actually offsets from the current row and column.

  Simple example to explain the concept of relative cell references in excel.

 

                                                                                                                  

In the above picture we can see that there is 4 different product ,quantity and price .And we need to find out total price of all the product.the above formula  give us the total amount of product shirt here and we dont need to apply the same formula every time .instead we just copy paste the formula to other cells.

   

Absolute cell references

It refers to the cell by their position in relation to the cell that contains the formula. The rows and column references do not change when you copy the formula because the references is to an actual cell address.

example

In the above picture we can see that we have to find out GST RATE of each item ,so its clear that same  GST RATE will be applicable to each item so in this condition we will apply ABSOLUTE CELL REFERENCE.

IN THIS SITUATION FORMULA IN CELL D2 IS =B2*C2*$F$8

SELECT CELL F8 AND PRESS BUTTON F4 TO FIX THE GST RATE  AS WE HAVE SAME GST RATE FOR DIFFRENT ITEM SO IT WILL BE SUITABLE TO FIX THE GST RATE.              

   

Mixed cell references

Any of the row or column reference is relative and the other is absolute .

example

In the above picture we can see that we need  to find out phone bills for various call made and phone rate the formula in cell C3 IS =$A3*C$2

 SELECT CELL A3 AND PRESS BUTTON F4 FOR THE 3 TIMES (FOR COLUMN WE ALWAYS PRESS F4 BUTTON 3 TIMES)

     

SELECT CELL C2 AND PRESS BUTTON F4 FOR THE 2 TIMES(FOR ROW WE ALWAYS PRESS F4 BUTTON 2 TIMES)

 

Circular cell references

when a formula refers back to its own cell ,either directly or indirectly it is called a circular reference.

example

The formula in cell A3 below directly refers to its own cell. this is not possible. note : excel returns a 0 if you accept this circular reference.