Tuesday, 19 July 2016

Cell References and their types

Hi, visitors today I am coming another more interesting and useful topic from ms excel. so, let us starts,While using the functions or formulas in ms excel we are only using the cell names( references). We can't  perform the formula/function directly on data.Using references in excel formulas/functions is known as cell references. They four types of cell references techniques in ms excel. They are
  1. Relative cell reference    
  2. Absolute cell reference     
  3. Mixed cell reference     
  4. Named cell reference     

1.Relative cell reference

                                      In this type of technique the formula give to one row/column is automatically adjust to all renaming rows are columns. For in the below example the formula from the first row is automatically applied to all others when one can drag it.

Observe the above two figures you have a clear idea about Relative cell reference.

2.Absolute cell reference ($)

                                             So, there may a situation where your formula is unique to only one row/column at this time the relative reference is causes a problem. For this type of requirements we have Absolute cell reference where the formula is unique to a only one row/column. This can be achieved using the '$' symbol while giving the formula like this

In above figure the formula is actually is given to the first row and it is unique to that only, it can't copied while dragging it the first row sum is displayed renaming two. This type of reference is known as Absolute cell reference.

3.Mixed cell reference    

                                       In mixed cell reference we can use both above two reference techniques to give the formula.

  4.Named cell reference     

                  When writing functions, referring to a name rather than using the cell references reduces the chance of errors.

Names can be used almost anywhere that you would enter a range of cell references. Common uses of names are in formulas, functions, and print ranges. When writing a function, simply use the name instead of the cell references. A SUM formula is written as follows:

With the range C3:C15 named Payroll, the function could also be written as follows:
EXAMPLE: =SUM(Payroll)

Both functions will yield the same result.

The circular reference problem

              While writing the formula in excel, the formula directly or indirectly if it refers the same cell, then it is called circular reference. For example, cell b3 contains 10 and c4 contains 20 and I want to add these two numbers in cell d3. The the normal excel formula we write in cell d3 is =b3+c3, But if your write like =b3+c3+d3 ( i.e., refereeing the same cell where formula going to results) then it will results a value zero and it shows an error called circular reference.


Post a Comment

Note: only a member of this blog may post a comment.

Find Us On Facebook

python tutorial


C Programming


Java Tutorial


Data Structures


MS Office


Database Management