Cell references allow us to use values from different parts of a worksheet and execute and enables us to do the desired calculation. We can use any cell or group of cells in a formula. The reference of the active cell is displayed in the name box at the left end of the formula bar.
There are three types of cell referencing which are below:
1. Relative cell reference
2. Absolute cell reference
3. Mixed cell reference
Relative Cell Reference: A relative cell reference in a formula is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If we copy or fill formula across rows or down columns, the reference automatically adjusts.
For Example: If we take 10 digit in cell A1 and digit 20 in cell A2 and put formula =Sum (A1, A2) in cell A3 then the result comes 30. If we take digit 100 in cell B1 and 200 in cell B2 and copy the formula from cell A3 paste in B3 then we get result 300. So in relative cell reference the values A3 and B3 are different because it takes only cell reference. A3 give the cell reference to B3 and provide result 300.
Absolute Cell Reference: We use absolute references only keeps the cell reference values from changing and when we copy a formula to a new location in worksheets.
Indicate absolute references by putting a dollar sign ($) in front of the column letter or row number that we want to freeze or both.
Steps for Absolute cell references
Type an equal sign (=) and the cell reference we want to be absolute.
Press F4, the absolute reference key, until the correct combination of dollar sign appears.
Type the next operator and continue to enter the formula also.
We can use the F4 key when editing an existing formula.
For Example: If we take 10 in cell A1 and 20 in cell A2 and write formula =Sum ($A$1, $A$2) in cell A3 then press enter button and we get result 30 in cell A3. If we take 100 in cell B1 and 200 in cell B2 and copy formula from cell A3 and paste in B3 then we get result 30 in B3 because in Absolute cell reference cell becomes freeze and provides result as same as previous result whereas cell B1 and B2 have any values.
Mixed Cell References: In some cases we may want either the row to stay fixed or the column to stay fixed when copying the cells from one place to other in a worksheet. In such case we use mixed reference. Such reference contains both absolute and relative reference.
For Example: We take 10 in cell A1, 20 in cell A2 and type a formula =sum ($A$1, A2) in cell A3 and we get result 30. and if we type 100 in cell B1, 200 in B2 and copy formula from cell A3 and paste B3 then after pressing enter button we get results 210 in cell B3 because cell A1 is fixed by putting dollar.