In Excel, your data could have cell references that you cannot refuse to work on. A cell reference is normally the address, name, or cell range. Most likely the column and row combo are known as a cell reference. It determines the value or data from the sheet that is to be used in the formula. Not only the same sheet, but cell reference also allows you to reference the cells from another sheet.
You will find cell references divided into three categories:
- Relative Cell Reference in Excel
- Absolute Cell Reference in Excel
- Mixed Cell Reference in Excel
Do you know when you reference a cell from another sheet it is called external referencing? On the other hand, when you reference a cell from another program you call it Remote referencing.
Let’s understand the phenomenon of Relative Cell Reference in Excel.
Relative Reference
By default, Excel provides you an option to use relative reference, which is a combination of row number and column name. Also, keep in mind that there will no dollar $ sign. You will notice a change in the relative cell address while copying the formula from one cell to another. The address change is dependent on the relative position of the column and row.
Whenever you need to perform the same functions on multiple cells you can use the relative reference.
Absolute Reference
You are now familiar with the relative reference, let’s have a brief understanding of absolute reference. In Absolute reference, the row and column are formed when putting the dollar $ sign right before the column name and row number. You will not see any change in the absolute reference while copying the formula from one cell to another.
In contrast with Relative reference, absolute reference uses dollar signs, such as $A$1, $B$3, $F$5, etc.
Why Use Relative Cell Reference in Excel
While creating a formula for a cell range you can use Relative cell reference. And the formula you will see needs to refer to a relative cell reference. The formula will be made for one cell and then you will copy-paste it into other cells.
How to Use Relative Reference in Excel
Suppose we need to sum two numbers in cells A1 and A2. The sum should be in cell A3.
As we need the sum in cell A3, we will put “=A1+A2” in cell A3.
You will see the sum of cells A1 and A2 is 100.
Now, we will apply the same formula using different values of cells B1 and B2. The sum should be in cell B3. Basically, you can Sum in two ways; you can apply the formula used for addition in cell B3, or else you can copy and paste the formula of cell A3 to cell B3.
When you copy cell A3 and paste it into cell B3, you will see the answer is not 100. It happens just because cell A3 is copied and since it has a formula and not a value, that’s why it answers in that way. Cells A1 and A2 determine the output of cell A3. When cell A3 is copied you will go ahead to the next cell on the right, where you will notice A1 turns B1 and A2 turns B2. And ultimately the sum value is in cell B3.
Example 2
For now, the above example is clear and you most probably have got an idea of how to use relative references in Excel. However, we will elaborate on the thing even more with another example.
Suppose we need to find the sales revenue with the equation Units Sold*Unit Price=Sales Revenue.
For this, you need to multiply the number of units that the unit price sold.
You will get the sales revenue for product 1 with the formula B2*C2. Well, applying this formula to all the products one by one could be time-consuming that’s why we will copy and paste the formula into other cells. When you copy the formula from cell D2 to cell D3, you will notice the formula reference is changed from B2*C2 to B3*C3.
Now, press CTRL + D to find the sales revenue for each product. Or else you can copy and paste cell D2 into the selected cells.
Definitely, you will not be able to manually apply the formula to each product and for this, you need to use copy/paste or drag the Fill Handle.
Final Thoughts
Basically, relative cell reference in Excel does not use a dollar sign, whereas absolute cell reference uses a dollar sign and this is the main difference you can see. Both references are not similar in function that’s why you can use relative references separately.
