Difference between absolute and relative reference
In normal excel use, we are facing issues with cell values when we are copying a cell from one place to another place. It happens due to references used in the cell. Usually, there are two cell references in excel 1) absolute reference and 2) relative reference. We also use mixed references. Let’s understand it in detail.
Relative References
BY default every cell reference is the relative reference. When copied across cells the values also change relatively.
In the above example, we can see that we first refer to value in CELL B3 and when we copied it to cell E3 the cell reference got changed from cell B3 to C3. Again when we copied it to E6 the value changed to C6. So, this means when a cell has relative reference its value will change relatively when copied to any cell.
Absolute References
There may be times when we do not want the cell reference to get changed when copying a cell. Unlike relative references, absolute references are constant and don’t change value when copied to any cell either horizontally or vertically. An absolute reference looks like $R$C, the dollar is being used before and after row number or you can say it is used before row and column number.
In the above example, we multiplied Project column value to Project Weight and sum it with Exam column value multiplied with Exam weight value. Now when we copy this cell downward we observe that cell values of weight remain constant though the column values changed relatively. Now when we look at the formula then we found cell values of weight contain $ before row and column numbers though values of the column have no $.
So it is now clear that when we use relative reference values change relatively when cells are copied on the other hand when we use absolute reference values remain constant even when we copied cells anywhere.
Mixed Reference
As we already see two types of references where one is moving relatively while another one is constant. But in a few scenarios, we need row or column value to be constant while its column or row values should be moving. To get this kind of reference we use a single $ sign before row or column whose value we wanted to keep constant.
In the above example, we can see we used $ sign before row number(4) while column number(D) remains free. In this scenario, we filled the below rows and pasted them into column F. in both scenarios row 2 remain constant while columns and their values changed accordingly.
That was all for the Difference between absolute and relative reference in Excel, will meet again with some new topic.
You can read about the difference between absolute and relative reference in Excel from the below articles.
https://trumpexcel.com/absolute-relative-mixed-cell-references/