By Shivani Garg
Cell References in MS Excel
Cell reference refers to a cell or a range of cells in a worksheet that can be used in a formula. Cell references are used while creating excel worksheets and writing formulas.
There are three types of Cell References:
- Relative
- Absolute
- Mixed
Relative Reference
When you enter a cell reference or range in a formula, Excel uses relative reference by default.
Let's learn with Example
Look at the formula in cell D2 below. Cell D2 refers to cell B2 and cell C2. Both references are relative.
>> Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D6.
Result:
Cell D3 references cell B3 and cell C3.
Cell D4 references cell B4 and cell C4.
Cell D5 references cell B5 and cell C5 and likewise it will go on.
Absolute Reference
When we copy and paste any formula in the cell to other locations, it cause cell references to change. But Not in case when we use Absolute Reference
An absolute reference is a cell reference that don't change when an formula is copied.
There are situations where absolute references are helpful like in making hourly pay sheets, currency conversion ratio.
To create an absolute reference to any cell, place a $ symbol in front of the column letter and row number in the formula of cell.
Let's learn with Example
>> To create an absolute reference to cell F2, place a $ symbol in front of the column letter and row number ($D$2) in the formula of cell F2.
>> Select cell F2, click on the lower right corner of cell D2 and drag it down to cell D4.
Result:
Cell D3 & D4 references will not change, the Formula will still remain same.
Let's look at Another Example of Absolute Cell Reference
>> To create an absolute reference to cell C4 for creating Hourly Pay Sheet
C4 equals =B4*$B$2
>> Select cell C4, click on the lower right corner of cell C4 and drag it down to cell C8.
Result:
The reference to cell B2 with $ sign is Absolute and will not change when copied.
Mixed Reference
A mixed cell reference is either an absolute column and relative row or absolute row and relative column.
When you add the $ before the column letter you create an absolute column or before the row number you create an absolute row.
Let's learn with Example
>> To create a Mixed Reference to cell C4 for Product Pricing , C4 equals =$B4*(1-B$2)
>> To create a Mixed Reference to cell D4 for Product Pricing , D4 equals =$B4*(1-C$2)
>> Select cell C4, click on the lower right corner of cell C4 and drag it down to cell C7. The Formula will copy to other cells.
Result:
The Reference to cell C4 and B2 are Mixed.
(B4:B7 are Relative Reference and B2 is Fixed Reference)
The Reference to C4 and C2 are Mixed.
(C4:C7 are Relative Reference and C2 is Fixed Reference)
Read More on Functions :
Also Read,
Related >>
Labels:
MS Excel
Shivani Garg