Relative Cell Reference Example in Excel (3 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

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.

1-Overview image of relative cell reference example

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.


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.

2-cell reference in Excel

There are mainly 3 types of cell references in Excel. They are:

  • Relative Cell Reference
  • Absolute Cell Reference
  • Mixed Cell Reference

3-cell reference types in Excel

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.

4-relative cell reference in Excel

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.

5-relative cell reference in Excel

  • First of all, type the formula in Cell E5:
=C5*D5

6-apply formula to count total price

In the formula, we are using relative cell reference. So, if we drag down cell E5, cell C5 and D5 will change inside the formula.
  • After that, press Enter and drag the Fill Handle down to copy the formula.

7-use of Fill Handle to copy 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.

8-relative cell reference example in a formula

Related Content: How to Use Relative Cell Reference in Excel


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.

9-relative cell reference for range of cells

Follow the steps below to see how the example works.

  • Type the following formula in cell E9:
=D10:D12*$C$5:$C$7

10-relative cell reference for range of cells formula

Here, D10:D12 is the selected range of cells with relative reference and $C$5:$C$7 is the selected range of cells with absolute reference. Here, we used relative cell reference because we want the quantity range to change in the formula. And used the absolute cell reference because the unit prices of different products are the same for all customers. In the Item column, you must maintain the series {Pen, Pencil, Notebook} for each customer.
  • 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.

11-changes in range of cells for relative reference

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.


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.

12-dataset for relative cell reference across different sheets

  • 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.

13-formula for relative cell reference across different sheets

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: Excel VBA: Insert Formula with Relative Reference


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.

Related Content: [Fixed!] Relative Cell Reference Not Working in Excel


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.

14-dataset for absolute cell reference

  • Type the formula in Cell E7:
=C7*D7*$C$4
  • Press Enter and drag the Fill Handle down to copy the formula.

15-formula for absolute cell reference

In this formula, we multiplied the principal, years, and fixed interest rate. Cell C4 contains the fixed interest rate. So, we need to lock this cell for calculation using absolute cell reference. To apply absolute cell reference, we inserted the dollar ($) symbol before the row and column indexes of Cell C4.
  • 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.

16-absolute cell reference example


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.

17-difference between absolute and relative cell reference

In this case, we are not getting the correct results for the range F6:F10. Because the cells inside the formula change while dragging down. For example, in cell F6, the formula becomes “=C6*D6*E6”. Interestingly, cell E6 contains no value. That is why we are getting 0 in the Interest column. So, how can we overcome this? The answer is: by using absolute cell referencing.
  • 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.

18-use of absolute cell reference to calculate interest

In this formula, we locked cell E5 using the dollar ($) symbol. So, cell E5 remains constant inside the formula. This is how absolute cell referencing works. You just need to insert dollar ($) symbol in front of the row and column indexes of a cell to use absolute referencing.

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

19-mixed cell reference in Excel

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.

Download Practice Workbook

You can download the practice book from here.


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.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo