Relative Cell Reference in Excel is one kind of cell reference where the referred cell changes when it is copied, moved or used in an array. It is a very useful feature of Excel and provides great help when you need to apply the same formula over a range of columns or rows. In this article, I will discuss everything you need to know about relative cell reference with example.
Download Practice Workbook
Function of Relative Cell Reference
When you type a formula in any cell, you can use another cell as a reference. Then You can drag the cell in which the formula is applied to apply the same formula over other cells in the column or row. While doing so, the cell you used in the formula automatically changes and that’s why it is called relative reference. It allows you to use the same formula over different columns or rows without typing the formula in different cells. This is the beauty of relative reference. It saves a lot of time because instead of inserting a formula every time, you can simply drag a cell to apply a formula over your dataset.
How to Make a Cell Reference Relative
When you select a cell or insert a cell in a formula it is by default relatively referred. Relatively referred cells show only the column and row number in the formula bar such as B4. If a cell has any dollar sign ($) such as $B4, B$4 or $B$4, the cell has absolute reference. Click here to know more about absolute reference. In this case, just simply delete the dollar sign ($), the cell will be relatively referred.
1. Relative Row Reference with Absolute Column
When you put the Dollar sign only before the column number you will get relative row reference with the absolute column. It means when you drag the cell, only the row of the selected cell will change, the column will remain unchanged. It will look like $B4. When you want to change the row but want to keep the column constant you can use relative row reference with absolute column. It is generally used when you copy your data vertically.
Related Content: Relative and Absolute Cell Address in the Spreadsheet
2. Relative Column Reference with Absolute Row
When you put the Dollar sign only before the row number you will get relative column reference with absolute row. It means when you drag the formula cell, only the column of the selected cell will change, the row will remain unchanged. It will look like B$4. When you want to change the column but want to keep the row constant you can use relative column reference with absolute row. It is generally used when you copy your data horizontally.
Related Content: Absolute Cell Reference Shortcut in Excel (4 Useful Examples)
3. Switching Reference of a Cell
- First, select the cell in the formula bar and press F4, it will make it absolutely referred.
- Then Press F4, the cell will have an absolute row with a relative column.
- Press F4 again, the cell will have an absolute column with relative row.
- Press F4 again, the cell will be relatively referred.
For example, let’s use the B4 cell to demonstrate the switch operation.
B4>press F4> $B$4>press F4>B$4>press F4>$B4>press F4>B4
3 Suitable Examples of Relative Cell Reference in Excel
1. Relative Cell Reference in a Formula
Now, let’s see how we can use relative cell reference in a formula. Consider the following examples. Here we have different principal amounts and years of deposit for different people. We want to find out how much Interest they will get for a specific interest rate.
➤ Type the following formula in cell E8,
In the formula, cells C8 and D8 are relatively referred. So, if we drag cell E8, cell C8 and D8 will change in the formula. But cell C4 won’t change because it has absolute reference.
➤ Press ENTER
As a result, we will get the Interest for the first entry.
➤ Drag cell E8 to the end of your dataset.
As a result, we will get the Interest for all of the entries.
Now, if you click on any of the cells of column E except E8, you will see that the relatively referred cells have been changed in the formula bar but the absolutely referred cell has remained the same.
Related Content: How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)
2. Relative Reference for a Range of Cells
You can also use relative references for a range of cells. Consider the following example, you want to find out the total price for pen, pencil and notebook for different customers. The unit price for those items is given in cell C4:C6.
➤ Type the following formula in cell E9,
Here, D9:D11 is the selected range of cells with relative reference and $C$4:$C$6 is the selected range of cells with absolute reference.
➤ Press ENTER.
You will automatically get the value in the E10 and E11 cells along with cell E9.
➤ Copy the cell E9 and paste in cell E12 and E15.
As a result, you will get the prices of the items for all of the customers.
Now, if you click on any cell from E12 to E17 you will see that the range of relatively referred cells has been changed but the range of absolutely referred cells has remained constant.
- Cell Reference in Excel VBA (8 Examples)
- Absolute Cell Reference in Excel (4 Examples)
- Difference Between Absolute and Relative Reference in Excel
- Excel Sheet Name in Formula Dynamic (3 Approaches)
3. Reference across Different Sheets
You can refer to a cell relatively or absolutely in a formula from a different worksheet of the same workbook. Suppose you have data of some deposits in the sheet named Deposit and want to use these data to calculate interest for different deposits in the sheet named Interest.
➤ Type the following formula in cell C5 of the Interest sheet,
Here, Deposit!C8 and Deposit!D8 are used to refer the cells C8 and D8 of the Deposit sheet relatively and Deposit!$C$4 are used to refer to the cell C4 of the Deposit sheet absolutely.
➤ Press ENTER.
As a result, you will get the Interest for the first deposit.
➤ Drag cell C5 to the end of your dataset.
So, you will get the Interest for all of the deposits.
Now, if you click on any cells of column C except C5, you will see that the relatively referred cells of the other sheet have been changed but the absolutely referred cell has remained the same.
Related Content: Reference Another Sheet in Excel (3 Methods)
Relative cell reference becomes very handy when you have to work with a large dataset and have to apply the same formula over multiple cells. I hope you have found this article useful, understand the relative cell reference example and are ready to utilize it. If you have any kind of confusion, please leave a comment.