Prantick Bala
Are you looking for relative cell reference example in Excel? Then you are in the right place. 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. Relative references are cell addresses that change based on their positions when copied.
The above picture shows different types of cell references in Excel. In this article, I will discuss everything you need to know about the relative cell reference with examples. Also, we will discuss absolute and mixed references briefly.
Download Practice Workbook
You can download the practice book from here.
What Is Cell Reference in Excel?
In Microsoft Excel, a cell reference is the address or location of a specific cell within the worksheet. It is a way to identify a specified cell by its row and column indexes.
It provides information to Excel on where to find the value that we need. For example, if you type a simple formula ‘=B4’ in Cell D4 and press Enter, Excel will extract the value of Cell B4 into Cell D4.
There are mainly 3 types of cell references in Excel. They are:
- Relative Cell Reference
- Absolute Cell Reference
- Mixed Cell Reference
If you look at the above picture, you can see different types of cell references use the dollar ($) sign differently. In Excel, we need to use the cell references correctly while doing calculations because using the wrong cell reference type will get you wrong results.
What Is Relative Cell Reference in Excel?
In Excel, a relative reference is a type of cell reference that changes when you copy the formula to a different cell or worksheet. It is mainly used for repeating calculations.
When you type a formula in any cell, you can use another cell as a reference. Then you can drag the cell to apply the same formula to 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 the relative reference.
For example, type “=B4” in Cell C4. Then, copy the formula down to Cell C6. In Cell C5, the formula becomes “=B5” and in Cell C6, it becomes “=B6”. This happens due to relative reference.
Relative cell reference allows you to use the same formula over different columns or rows without typing the formula in different cells. 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.
Relative Cell Reference Example in Excel: 3 Criteria
1. Relative Cell Reference in a Formula in Excel
In this example, we will use relative cell reference in a formula. Suppose, we have a dataset for different products that contain Unit Prices and Units. We need to calculate the Total Price using relative cell reference.
- First of all, type the formula in Cell E5:
=C5*D5
- After that, press Enter and drag the Fill Handle down to copy the formula.
- Now, if you click on any of the cells of range E5:E10 except E5, you will see that the relatively referred cells have been changed.
Related Content: How to Use Cell References in Excel Formula (All Possible Ways)
2. Relative Reference for a Range of Cells in Excel
You can also use relative references for a range of cells. Consider the following example, you want to find out the total price for pens, pencils, and notebooks for different customers. The unit price for those items is given in cell C5:C7.
Follow the steps below to see how the example works.
- Type the following formula in Cell E9:
=D10:D12*$C$5:$C$7
- Press Enter to see the price of items for Harold.
- After that, copy the formula and paste it into cells E13 and E16.
- As a result, you will get the prices of the items for all of the customers.
Now, if you click on any cell from E13 to E18 you will see that the range of relatively referred cells has been changed but the range of absolutely referred cells has remained constant.
Read More: Excel VBA: Insert Formula with Relative Reference (All Possible Ways)
Similar Readings
- Absolute Cell Reference in Excel (4 Examples)
- Difference Between Absolute and Relative Reference in Excel
- Excel Sheet Name in Formula Dynamic (3 Approaches)
- How to Reference Cell by Row and Column Number in Excel (4 Methods)
- Cell Reference in Excel VBA (8 Examples)
3. Reference Across Different Sheets in Excel
You can also refer to a cell relatively or absolutely in a formula from a different worksheet of the same workbook. Suppose, you have two sheets: Product Cost and Total Cost. Here, we will import the Total Product Cost (stored in Cell E11) from the Product Cost sheet and store it in the Total Cost sheet using relative cell reference.
- Type the following formula in Cell C5 of the Total Cost sheet:
=’Product Cost’!E11- Press Enter to see the Total Product Cost in the Total Cost sheet.
Now, if the Total Product Cost changes, then it will automatically be updated in the Total Cost sheet. When you are writing a formula to refer cells across different sheets, you need to write the sheet name inside the apostrophe symbols.
Related Content: Reference Another Sheet in Excel (3 Methods)
How Does Relative Cell Reference Change in Excel?
In Excel, cell reference changes when we move a cell left, right, up, or down. Every cell in Excel has a relative reference by default. The cell reference is automatically adjusted when you move or copy the formula to another location in the worksheet.
For example, when you refer to a cell, such as B8, the cell reference will change relative to its original location.
- If you move downward from B8, the reference will change to B9 because the row number has increased by 1.
- If you move upward from B8, the reference will change to B7 because the row number has decreased by 1.
- If you move leftward from B8, the reference will change to A8 because the column index has decreased by 1.
- If you move rightward from B8, the reference will change to C8 because the column index has increased by 1.
Absolute Cell Reference in Excel
In absolute cell reference, the cell address or reference stays the same when the formula is copied. You can use absolute references to keep both row and column constant by putting a dollar ($) symbol before them in the formula.
To explain absolute cell references, we will use the following dataset. It stores the Yearly Interest Rate (which is fixed), Principal, and Years. We will find out how much Interest people will get for a specific interest rate.
- Type the formula in Cell E7:
=C7*D7*$C$4- Press Enter and drag the Fill Handle down to copy the formula.
- Now, if you check any cell in the range E8:E12, for example, Cell E10, you will see that absolute cell reference “$C$4” has not changed.
Related Content: Absolute Cell Reference Shortcut in Excel (4 Useful Examples)
Difference Between Relative and Absolute Cell Reference with Example
In this section, we will show the difference between relative and absolute referencing with examples in Excel. The following dataset contains the Principal, Years, and Interest Rate. We will try to find the Interest using both relative and absolute referencing.
- Type the formula in Cell F5:
=C5*D5*E5- Press Enter and drag the Fill Handle down.
- Now, type the following formula in Cell F5:
=C5*D5*$E$5- Hit the Enter key on the keyboard.
- Then, drag the Fill Handle down.
Related Content: Relative and Absolute Cell Address in the Spreadsheet
Mixed Cell Reference in Excel
Mixed cell reference in Excel is a type of cell reference that contains both relative and absolute referencing. In mixed cell references, either column or row index is absolute and the other one is relative.
In the following dataset, we have calculated tiered commission using the formula below:
=$C6*$D6*F$5
Here, we have used mixed cell references. We can divide mixed cell reference into two types.
- $C6 (and $D6): This is called Relative Row Reference with Absolute Column. You can see the dollar ($) sign is used only before the column index which makes it a mixed cell reference. So, the value of the column will be kept constant in this type of reference. It is generally used when you copy your data vertically.
- F$5: This is called Relative Column Reference with Absolute Row. Here, the row will be constant but the column will change. When you want to change the column but need to keep the row constant you can use a relative column reference with the absolute row. It is generally used when you copy your data horizontally.
How to Switch Between Different Reference Types Using F4 Key in Excel
By using the F4 key on the keyboard we can place a dollar ($) sign in front of rows and columns.
- One press of F4 on the keyboard will make the cell absolutely absolute $C$4.
- Two presses of F4 on the keyboard will make the cell absolute row C$4.
- Three presses of F4 on the keyboard will make the cell absolute column $C4.
By removing the dollar ($) sign from an absolute reference we can make it a relative reference.
Things to Remember
- Excel uses relative references as default for every cell. It changes when we copy the formula.
- Absolute references are the cell addresses that remain constant when we copy the formula.
- The dollar ($) sign makes the rows and columns constant in the case of absolute cell references.
Conclusion
In conclusion, relative cell reference is an essential aspect of Excel formulas. 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. We have also discussed absolute and mixed cell references to show the differences and their uses here. If you have any kind of confusion, please leave a comment. You can also visit the ExcelDemy website for more articles like this.
