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?