Wondering about the difference between absolute and relative references in Excel? This article explains their differences with clear explanations and practical examples.
What is Absolute Reference in Excel?
Absolute reference in excel allows you to reference a particular row or column even when you copy to other cells. What this means is that when you copy a cell or drag through cells, formulas will NOT change with the changing cells.
What is Relative Reference Excel?
On the other hand relative reference of a cell means that cells change automatically. When you copy cells to new cells drag through, the formula will change with new cells.
In this tutorial you will learn how to:
- How to use absolute Reference in Excel
- How to use Relative Reference Excel
- Differences between the two with examples
- How to Convert Relative References to Absolute References in Excel
Absolute Reference in Excel (Deep Dive)
Let me kick this tutorial off with a simple example.
Refer to the image above. Say you want to add cell B1 to C3 and show result in cell D3. Then add cell B1 to cell C4 and show result in cell D4 and so on. You will create the formula below in cell D3:
SUM(B1,C3)
This will give you the desired result: 100 plus 10 equals 110.
Generally, to replicate the same formula in cells D4 to D7, you should simply drag through cells D4 to D7). Unfortunately, this will not give you the desired result.
The result of replicating the formula in cell D3 by dragging it through cells D4 to D7 is shown below:
The highlighted result are wrong. Remember we wanted to sum B1 to C4. Obviously this did not work!
What Went Wrong?
When you dragged the formula in cell D3 through D4 to D7, excel used relative reference of cell B1. It simply added B2 to C4. It also added B3 to C4 and so on.
As you can see from the image above, the result in D4 is a sum of B2 and C4. As I said earlier, what excel did was to create a relative reference. This is the default but it is not what you wanted to achieve.
How Do You Solve the Problem?
To resolve the problem identified above, you create an absolute reference in excel instead. To do this, highlight the formula in cell D3. Then add a dollar sign ($) before and after the cell letter, D.
The formula will now change as shown below:
SUM($B$41,C3)
See the image below.
By adding the dollar sign ($) as shown you are forcing excel to reference cell B1.
Note that you add the absolute reference (by including a dollar ($) sign in the first cell.
Now when you replicate the formula in cell D3 by dragging it through cells D4 to D7, you will have the desired result. See the image below.
Bingo! Adding an absolute reference in excel did the magic! Excel simply referenced the same cell in all the cells.
A Bit More About Relative Reference in Excel
Before I walk you through some examples of absolute reference in Excel let me discuss Relative Reference.
There are circumstances where you require relative reference.
Say you have two columns with a set of numbers as shown below. Using relative reference in excel will be more applicable.
In this example, you sum A3 and B3 in C3. To replicate the formula in cells C4 to C7, highlight the formula in cell C3 then drag through to cell C7.
Here is the result.
In this case, the results are as expected. By dragging the formula in cell C3 through cells C4 to C7 excel used relative reference to replicate the formula. In this instance we do not need to keep cell reference constant in the formula.
Real Life Example
In this section I want to show you a real life application of absolute and relative reference excel in business. To begin review the excel sheet in this image below:
Lets say you run a business or you work for a business and you need to determine sales price based on profit markup and sales tax. Say you are also required to sum all costs, sales price plus tax, profit and sales tax.
You will need knowledge of both absolute reference in excel and relative reference in excel.
Based on what you have learned in this tutorial so far, you can see that the profit markup and the sales tax will be referenced using absolute reference. On the other hand, creating formula for the totals will need relative reference.
My Excel Sheet Example Explained
To begin with lets explain how I arrived at the results in the excel sheet above. Let’s start with cell D7.
Here is the screenshot.
The cell has the formula below:
=((C7+C7$C$4))+((C7+C7$C$4))*$C$5
To help you understand the seemingly complex formula, I will break it down below:
- The first part of the formula, C7+C7*$C$4 calculates the profit of “Product A”. This is achieved by adding the cost price in C7 to the profit margin (C7*$C$4). Note that C7 has a relative reference.
- The profit margin is calculated by multiplying the cost price with the absolute value of the profit margin in cell C4.
- To determine the final product cost, I now need to add the sales tax.
Sales tax (or value added tax in some countries) is usually calculated by marking up your sales price with the sales tax percentage.
How I Included Sales Tax Markup
- Based on the tip above, the final sales price will be, (product sales price) + (product sales price) multiplied by the sales tax markup. That is how I arrived at the formula, ((C7+C7$C$4))+((C7+C7$C$4))*$C$5
- (C7+C7$C$4) is the product sales price. ((C7+C7$C$4))*$C$5 is the sales price markup.
- For this second portion of the final sales price calculation, notice that I also referenced the absolute value of the sale tax in cell C5
- To replicate the formula in cell D7, I simply highlighted the formula in D7 then dragged through to cell D11.
- The same logic applies to the formula I used in calculating the profit (column E). The formula in cell E7 is, C7*$C$4. This uses the relative reference of the product price cell, C7 multiplied by the absolute reference of the profit markup, cell C4.
- On the other hand, the sales tax column, F is computed using just relative values. The formula in cell C7 is D7-C7-E7 (final sales price minus cost price , minus profit).
I hope you can see the benefit of this knowledge in a real life business situation.
How to Convert Relative Reference to Absolute Reference with F4
My final task in this tutorial is to show you how to quickly convert a relative reference formula to an absolute value using F4. This knowledge is important as you create a formula using relative value. You then need to convert the formula to absolute values.
To demonstrate this, let me show you how I computed the profit in cell E7.
- First, I added the equals sign in the cell. Then I selected the product price cell, C7, and included the multiplication sign (*). Next, I selected the profit margin cell, C4. As you can see above, both C7 and C4 have their relative values.
- To convert cell C4 to an absolute value in excel, I simply kept my mouse next to the cell letter in the formula bar, then pressed F4. The Dollar sign ($) was added automatically. See the screenshot below.
Conclusion
That is it. Absolute Reference Excel vs Relative Reference Excel.
I hope you found this Itechguide helpful.
Alternatively, kindly give feedback using the “Leave a Reply” form at the end of this page. Alternatively, you can respond to the “Was this page helpful?” question below.
For more emails and productivity Itechguides, visit our Emails & Productivity Explained page.
