Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.
=C5*D5 

Apply a formula based on relative reference in Excel

  • Press the Enter button.

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

Use Fill Handle feature to copy a relative reference in Excel

  • 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.
=C5*D5

  • Press the Enter button.

Copy cells with relative references in Excel

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

Paste cells with relative references in Excel

  • 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


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.

Make cells editable for copying relative references

  • Enter the following formula based on the relative reference.
=C5*D5

  • After that, press Ctrl+ Enter.

Press Ctrl + Enter for relative references

  • 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.
=C5*D5

We will copy this formula using the keyboard shortcut.

  • Go to Cell E6 and press Ctrl +D.

Keyboard shortcut for copying formula with relative references

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

Copying formula in the right side with relative references in Excel

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

Dataset for calculating formula based on absolute references in Excel

📌 Steps:

  • Put the following formula on Cell D5.
=C5*$C$11

Creating formula with absolute references in Excel

We press the F4 button for absolute reference.

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

Copying formula with absolute references in excel

  • 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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1 Comment
  1. New to all this. LOVE your site. Wondering if there is a way to populate cells on an alternate page, but force the population to skip down to the next row if the current row is populated?

    CONTEXT
    I’m researching real estate properties. Would like to run my numbers on one sheet. One the 2nd sheet, I’d like to keep a running tab of every property I’ve investigated for easy reference. So once I complete my numbers on sheet one (financials), I’d like it to populate sheet 2 (Master list) on row 1. Then the next time I run numbers on a property, those numbers populate row 2, and so on. How do I “lock” row 1 (for example) so that the next time I run numbers it doesn’t replace?

Leave a reply

ExcelDemy
Logo