Use relative, absolute and mixed cell address in MS Excel, Cell references

Опубликовано: 17 Сентябрь 2024
на канале: it Tips

In Microsoft Excel the entire data is entered in cells. Cell is the intersection of row and column. In such type of grid each and every cell has a unique address. The value of cell is obtained in formula or function with the help of cell reference. In Microsoft Excel the cell address for example is A1, it means that this cell is available in first row and first column. By default MS Excel uses the relative cell addresses.
In relative cell address whenever this cell is copied or dragged on the other cells the formula or function that is used inside that cell change its cell addresses relative to the other cells. For example if cell C1 contain the formula A1+B1 in sheet 1. When this cell is copied to the other sheet in same workbook. This formula will take the values of A1 and B1 from sheet 2.
The solution to solve this situation is called absolute addressing. In absolute addressing the address or reference of the formula or function never changed even it is copied to other location. For this purpose we use $ sign as prefix to the column letter and row number. For example if we want to get the sum of A1 and B1 in C1 using absolute address, then in C1 we will write the formula as $A$1 + $B$1.
In the same way as we used above for absolute reference we can also use sheet reference or can say sheet name in address. Such as sheet1!$A$1+sheet1!$B1. After using this type of formula will never change even it is copied among different sheets. The exclamation or not operator is used after name of sheet.