Reference operators
- Two types of reference operators are Range and Union
Union
- Union includes two or more references.
- It consists of two or more addresses separated by comma.
Eg : 'A1, B7 ,C9 refers to cells A1, B7 and C9.
- Excel records cell addresses in formulas and functions in three different ways.
Relative cell references
Absolute cell references
Mixed cell references
- In relative cell reference we call cells by their row and column label.
- Excel changes the cell addresses relative to the row and column they are moved to
Eg : If we move an addition formula
= B1+C1 in D1 to cell D2 , the formula would be changed to '=B2+C2'
Absolute cell reference
- Absolute cell reference refers to the same cell.
- It doesn't change the cell address relative to the cells we copied the formula.
- Absolute cell address uses a dollar symbol ($) in front of column and row label.
- If we copy a formula '=C5+$A$1' in cell D5 to cell D6 formula will become '=C6+$A$1( Relative cell reference C5 changes to C6 but absolute cell reference $A$1 does not change)
Mixed cell reference
- It contains absolute part and relative part.
Eg : 'A$1+$C5' (Here row label of the cell A1 is fixed and column label of the cell C5 is fixed)
- We can add dollar sign ($) symbol in a cell reference by pressing F4 key.
- If we press F4 key once after entering cell address in to formula or functions.
