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.

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

**Creating and Copying a Formula Using Relative References in Excel: ****4 Creative Ways**

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`

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

`=C5*D5`

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

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

`=C5*D5`

- After that, press
**Ctrl+ Enter**.

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

**Read More:** How to Copy Formula in Excel to Change One Cell Reference Only

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

- 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:** How to Copy a Formula in Excel Without Changing Cell References

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

`=C5*$C$11`

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:** How to Copy a Formula across Multiple Rows in Excel

**Download Practice Workbook**

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

**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, give your suggestions in the comment box.

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?