# Creating and Copying Formula Using Relative References in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

## 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.
Kawser Ahmed

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?

Advanced Excel Exercises with Solutions PDF