How to Use Relative Cell Reference in Excel (5 Suitable Examples)

Whenever working with Excel, relative cell reference is frequently used. There are various uses of relative cell reference in Excel. The main objective of this article is to explain how to use relative cell reference in Excel.

Download Practice Workbook

What Is Relative Cell Reference?

In Excel, a relative cell reference denotes the application of a point of reference to a cell or formula where the return value is positioned in relation to the cell.

Thus, the return value changes based on whether the cell or the formula is moved inside the same sheet or to a different sheet when the cell or the formula is moved.

When the same computation must be performed across various rows or columns, relative cell reference can be useful.

5 Suitable Examples to Use Relative Cell Reference in Excel

To explain this article, I have taken the following dataset. This dataset contains student’s Name and marks in different subjects like Math, Physics, and Chemistry.

1. Use of Relative Cell Reference to Copy Formula in Columns

In this first example, I will show you how to use relative cell reference in Excel for copying formulas in columns.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Total marks. Here, I selected cell F5.
• Secondly, in cell F5 write the following formula.

Here, the SUM function will return the Summation of the values in cell range C5:E5. In this function, I used relative cell reference to enter the cell range.

• Thirdly, press ENTER to get the result.

• Now, drag the Fill Handle to copy the formula.

Here, I have copied the formula successfully. You can see that the cell range changed accordingly as I copied the formula. This happened because I used a relative cell reference to enter the data.

Now, I used absolute cell reference while selecting the range like the following Image.

Here,  the SUM function will return the Summation of the values in cell range C5:E5 and the absolute cell reference will fix this range.

• Now, press ENTER and you will get your result.

You can see the result I got here is correct.

• After that, drag the Fill Handle and copy the formula.

You can see after copying the formula the result remains the same as the cell range remains the same. This is the reason, I used relative cell references before.

Read More: Excel VBA: Cell Reference in Another Sheet (4 Methods)

2. Using Relative Cell Reference to Copy Formula in Rows

In this method, I will show you how to use relative cell reference in Excel while copying formulas in rows. I will explain 2 different examples here.

Example-01: Use of Copy Paste Option

In this example, I will use the Copy Paste option to copy formulas in rows. To Explain this example I have taken the following dataset.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want your Total marks. Here, I selected cell C9.
• Secondly, in cell C9 write the following formula.

Here, the SUM function will return the Summation of the values in cell range C5:C8. In this function, I used relative cell reference to enter the cell range.

• Thirdly, press ENTER to get the result.

• Now, Right-click on the cell that contains the formula.
• Next, select Copy.

• After that, Right-click on the cell where you want your formula to be copied.
• Then, select Formulas from Paste Options.

Now, you will see that the formula will be copied and the cell reference will change accordingly as I used relative cell reference while selecting the range.

You can use this copy paste option to copy formulas when you are copying formulas in only one or two cells. In other cases, this method is not that effective.

Read More: How to Use Cell References in Excel Formula (All Possible Ways)

Example-02: Using Fill Handle

In this example, I will use the Fill Handle to copy formulas in rows. To Explain this example I have taken the following dataset.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want the Total marks. Here, I selected cell C9.
• Secondly, in cell C9 write the following formula.

Here, the SUM function will return the Summation of the values in cell range C5:C8. In this function, I used relative cell reference to enter the cell range.

• Next, press ENTER to get the result.

• Finally, drag the Fill Handle to copy the formula.

Now, you can see that I have copied the formula and the cell range changed accordingly as I used relative cell reference while selecting the cell range.

Read More: How to Use Variable Row Number as Cell Reference in Excel

3. Use of Relative Cell Reference for Calculations

In this method, I will show you how to use relative cell reference in Excel for calculations. Here, I have taken the following dataset to explain this example. It contains the student’s Name and marks in Math, and Physics. I will calculate the Total and Percentage using relative cell reference.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want your first Total marks. Here, I selected cell E5.
• Secondly, in cell E5 write the following formula.

Here, I Summed C5 and D5. The formula will return the summation of cells C5 and D5. I used relative cell reference while selecting the cells.

• Finally, press ENTER to get the result.

• Now, drag the Fill Handle to copy the formula to all the other cells.

Here, you can see that I have copied the formula to all the other cells. And, the cells have changed accordingly because I used relative cell reference while selecting the cells.

Now, I will calculate the Percentage.

• Firstly, select the cell where you want to calculate the Percentage. Here, I selected cell F5.
• Secondly, in cell F5 write the following formula.

Here, I divided E5 by 200 and then multiplied the result by 100%. The formula will return the number percentage. I used relative cell reference in this case also.

• Finally, press ENTER.

• After that, drag the Fill Handle to copy the formula.

Now, you can see that I have copied the formula. Here, the cell changes accordingly.

Read More: Excel VBA: Insert Formula with Relative Reference (All Possible Ways)

Similar Readings

• Excel VBA: R1C1 Formula with Variable (3 Examples)
• How to Reference Text in Another Cell in Excel (14 Ways)
• Excel VBA Examples with Cell Reference by Row and Column Number
• How to Find and Replace Cell Reference in Excel Formula
• Relative and Absolute Cell Address in the Spreadsheet

4. Employing Relative Cell Reference in Functions

In this example, I will show you how to use relative cell reference in Excel in functions. For this example, I have taken the following dataset. This dataset contains Name and marks in English. Here, I will show the Status as Pass or Fail.

Let’s see how it is done.

Steps:

• Firstly, select the cell where you want to show your Status. Here, I selected cell D5.
• Secondly, in cell D5 write the following formula.

Here, in the IF function, I selected C5<60 as logical_test, “Fail” as value_if_true and “Pass” as value_if_false. The formula will return Fail if the mark is less than 60 otherwise it will return Pass. I used relative cell reference while selecting the cell.

• Finally, press ENTER.

• After that, drag the Fill Handle to copy the formula

Now, you can see that I have copied the formula and the cell has changed accordingly as I use relative cell reference while selecting it.

Read More: [Fixed!] Relative Cell Reference Not Working in Excel

5. Using Relative Cell Reference to Insert Dynamic Value

In this method, I will explain how to use a relative cell reference to insert dynamic values. Here, I have taken the following dataset. I will use a relative cell reference to insert the First Date and Last Date.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want your First Date. Here, I selected cell C6.
• Secondly, in cell C6 write the following formula.

Here, in the DATEVALUE function, I used “1” and C4  as date_text. Thus, the formula will return the First Date of the month that is in cell C4. I used relative cell reference for selecting cells.

• Finally, press ENTER.

Now, I will insert the Last Date.

• Firstly, select the cell where you want your Last Date. Here, I selected cell E6.
• Secondly, in cell E6 write the following formula.

Here, in the EOMONTH function, I selected cell C6 as start_date and 0 as months. The formula will return the Last date for the month.

• Finally, press ENTER to get the result.

Now, If you change the Month name here the First Date and Last Date will change automatically. In the following picture, you can see that I have selected February as the month and the First Date and Last Date update automatically as I changed the month. This happens because I used relative cell reference in the formula.

Read More: How to Use Sheet Name in Dynamic Formula in Excel

How to Use Absolute Cell Reference

In this section, I will show you how you can use an absolute cell reference. I have taken the following dataset to explain this example.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Tax on Income. Here, I selected cell D5.
• Secondly, in cell D5 write the following formula.

Here, I multiplied cell C5 by cell C11. The formula will return the Tax on Income by multiplying Income and Tax Rate.

• Finally, press ENTER.

• Now, drag the Fill Handle to copy the formula.

In the following image, you can see that the result is not correct. The reason behind this is, that the cell changes accordingly as I copied the formula. This happens because I used relative cell reference while selecting cells.

Here, you will need to use an absolute cell reference to make cell C11 fixed.

• Firstly, select the cell where you want to calculate the Tax on Income. Here, I selected cell D5.
• Secondly, in cell D5 write the following formula.

Here, I multiplied cell C5 by cell C11. The formula will return the Tax on Income by multiplying Income and Tax Rate. I used an absolute cell reference while selecting cell C11.

• Finally, press ENTER to get the result.

• Now, drag the Fill Handle to copy the formula.

In the following image, you can see that I have copied the formula and cell C11 remains fixed because of the absolute cell reference.

Read More: Absolute Cell Reference Shortcut in Excel (4 Useful Examples)

Things to Remember

• It should be noted that the copy paste method is suitable for a small set of data where you need to copy the formula to 1 or 2 cells. For big datasets using Fill Handle is more appropriate.

Practice Section

Here, I have provided practice for you to practice how to use relative cell reference in Excel.

Conclusion

In this article, I tried to explain how to use relative cell reference in Excel. I explained it with different examples. I hope, this article was helpful for you. If you have any questions, feel free to let me know in the comment section below.

Related Articles

• Mixed Cell Reference in Excel (4 Examples)
• Cell Reference in Excel VBA (8 Examples)
• Example of Mixed Cell Reference in Excel (3 Types)
• How to Lock a Cell in Excel Formula (2 Ways)
• Reference Another Sheet in Excel (3 Methods)
• Difference Between Absolute and Relative Reference in Excel
• Different Types of Cell References in Excel (With Examples)