Creating and Copying Formula Using Relative References in Excel

There are three types of cell references in Excel. Relative reference is one of them. This article will discuss about creating and copying a formula based on the relative references in Excel with the proper illustration.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What Is Relative Reference in Excel?

Relative reference is one kind of cell reference in MS Excel. In Relative reference, cell references change when we copy or move that formula to another location in Excel.

4 Ways of Creating and Copying a Formula Using Relative References in Excel

In this article, we will show different methods to copy a formula based on the relative reference.

We have taken a sample dataset, that contains the item name, price, and quantity. We will create a formula based on the relative reference of the 1st row of the dataset and then copy that formula onto other cells.

1. Use Fill Handle Tool to Copy Formula with Relative References

In this section, we will copy the formula based on the relative reference using the Fill Handle icon. Here, we want to calculate the total of each product by applying a formula of relative reference.

📌 Steps:

• First, we add a new column on the right side.

• Now, put the following formula on Cell E5.

• Press the Enter button.

• We can see the Fill Handle icon at the right bottom of each cell.

• Double-click this Fill Handle icon.

If our calculated cells are not adjacent, drag the Fill Handle icon.

• Edit any of the cells where we copied the formula. Go to Cell E7 and press the F2 button.

We can see a formula based on the relative reference.

Read More: How to Copy a Formula in Excel with Changing Cell References

2. Copy Formula with Relative References Using Copy and Paste Feature

In this section, we apply the Copy & Paste method to copy the formula with relative reference in Excel.

📌 Steps:

• First, create a formula with relative reference and put it on Cell E5.

• Press the Enter button.

• Now, select the rest of the cells.
• Then, press Ctrl+ V.

• Look at the dataset.

The formula has been copied to the rest of the cells.

• To check the formula of Cell E7, make that cell editable by pressing the F2 button.

Read More: Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)

Similar Readings

• How to Copy Formula to Another Sheet in Excel (4 Ways)
• [Fixed!] Relative Cell Reference Not Working in Excel
• How to Copy Sheet to Another Workbook with Excel Formulas
• Use Cell References in Excel Formula (All Possible Ways)
• How to Copy Formula Down with Shortcut in Excel

3. Create and Copy the Formula in All Cells at Single Entry

In this section, we will enter a formula with relative reference at a single stroke. We will enter the formula at one cell and that formula will copy to other cells at a single entry.

📌 Steps:

• Select Range E5:E9.
• Press the F2 button to make the cell editable.

• Enter the following formula based on the relative reference.

• After that, press Ctrl+ Enter.

• Now, double-click on any of the cells of the selected range to check the formula.

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

4. Keyboard Shortcut to Copy Formula with Relative References

In this section, we will use the keyboard shortcut to copy the formula with relative reference in excel.

📌 Steps:

• We can see Cell E5 contains the following formula with relative reference.

We will copy this formula using the keyboard shortcut.

• Go to Cell E6 and press Ctrl +D.

• Look at the dataset now.

The following formula has been copied for respective cells. We can also copy the formula on the right side.

• Go to cell a which has a formula on its left side. Here, we move to Cell F5.
• Then, press Ctrl+R.

• We can see the formula has been copied successfully.

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

How to Use Absolute Reference in Excel

Absolute Reference is something where cell references do not change after copying that formula or changing the location of the formula in Excel.

In this section, we will show the use of absolute reference in Excel. We will also show how to copy absolute references.

We will calculate the amount of tax for each product in this section. Where the tax rate is fixed, so we will use absolution reference for referencing the cell related to tax.

📌 Steps:

• Put the following formula on Cell D5.

We press the F4 button for absolute reference.

• We can see the Fill Handle icon on the dataset.

• Double-click the Fill Handle icon.

Our formula has been expanded on the rest of the cells.

• Now, double-click on any of the cells to view the formula.

We can see the absolute reference in Cell D7.

Read More: [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)

Conclusion

In this article, we described 4 methods for creating and copying the formula based on relative cell references in Excel. We also showed how to copy the absolute cell reference in Excel. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.

Related Articles

• How to Copy a Formula across Multiple Rows in Excel (5 Ways)
• Copy a Formula in Excel Without Changing Cell References
• How to Calculate Data Across Worksheets with Formulas in Excel
• Excel VBA: Cell Reference in Another Sheet (4 Methods)
• How to Reference a Cell from a Different Worksheet in Excel
• Excel VBA Examples with Cell Reference by Row and Column Number
• Difference Between Absolute and Relative Reference in Excel