Absolute Reference vs Relative Reference Excel: Quick Guide

-

|

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

Introducing Absolute Reference in Excel

Let me kick this tutorial off with a simple example.

Absolute Reference Excel vs Relative Reference Excel

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.

Absolute Reference Excel vs Relative Reference Excel

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:

Absolute Reference Excel vs Relative Reference Excel

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.

Absolute Reference Excel vs Relative Reference Excel

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.

Important Tip
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 of Absolute Reference and Relative Reference in Excel

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.
Important Tip
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 in Excel 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.

That is it. Absolute Reference Excel vs Relative Reference Excel. Hope I made life a little bit easier for you. If I did kindly let me know using the “Leave a Reply” form at the end of this page.

Other Helpful Tutorials

Additional Resources and References

  • Was this post Helpful?
  • YesNo

LEAVE A REPLY

Please enter your comment!
Please enter your name here

ADVERTISEMENTS

FEATURED POSTS

Best Christian Movies on Netflix

The 10 Best Christian Movies on Netflix

This Itechguide reviews the 10 best christian movies on Netflix. The movies are listed in reverse order – with the 10th movie...
How to Change Orientation in Google Docs

How to Change Orientation in Google Docs

This Itechguide teaches you how to change orientation in Google Docs. The guide covers steps to change orientation in Google Docs from...
How to Delete Google Photos

How to Delete Google Photos

This Itechguide teaches you how to delete Google Photos. The guide offers steps to delete Google Photos from the Google Photos App...
How to Install Windows 10 1909 Update Manually

How to Install Windows 10 1909 Update Manually

This Itechguide teaches you how to install Windows 10 1909 update manually. Windows 10 1909 (otherwise known as Windows 10 November update)...
How to Create a Poll on Facebook

How to Create a Poll on Facebook

This Itechguide teaches you how to create a poll on Facebook. You can create a poll on Facebook from a Facebook page...

AMAZON DEALS

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

ADVERTISEMENTS

BEST OF ITECHGUIDES

Microsoft Edge is not in group policy

How to to Make Microsoft Edge Policy Available in Group Policy

Introduction When I was writing How to Set Homepage in Microsoft Edge I realized that Microsoft Edge is not...
Amazon Merch

Amazon Merch: Your Definitive Guide to Merch by Amazon

What is Amazon Merch? Merch by Amazon (MbA or Amazon Merch) is a T-shirt Print-On-Demand platform that allows you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
How to Enable Remote Desktop in Windows 10 (2 Methods)

How to Enable Remote Desktop in Windows 10 (2 Methods)

Introduction This guide demos 2 methods to enable remote desktop in Windows 10. Options to Enable...
0xc000021a server 2016

4 Quick Fixes for 0xc000021a Stop Error in Windows Server 2016

Introduction There are two known causes of 0xc000021a error in Server 2016: Recently installed a...

RECENT POSTS

How to Change Facebook Page Name

How to Change Facebook Page Name

This Itechguide teaches you how to change Facebook page name. The guide covers steps to change Facebook page name from a PC...
How to Change YouTube Channel Name

How to Change YouTube Channel Name

This Itechguide teaches you how to change YouTube channel name. The guide covers steps to change YouTube channel name from a PC.
How to Bold Text on Facebook Post

How to Bold Text on Facebook Post

This Itechguide teaches you how to bold text on Facebook post. The guide covers steps to bold text on Facebook post from...
Best Crime Documentaries on Netflix

The 10 Best Crime Documentaries on Netflix

This Itechguide reviews the 10 best crime documentaries on Netflix. The documentaries are listed in reverse order – with the 10th documentary...
Best Netflix Series

The 10 Best Netflix Series

This Itechguide reviews the 10 best Netflix series. The series are listed in reverse order – with the 10th series on top....

AMAZON DEALS

MUST READ

How to Set Homepage in Google Chrome (Settings or Group Policy)

Introduction You can set homepage in Chrome (Google Chrome) directly from Settings. This method is suitable for individual or...
Wifi doesn't have a valid IP configuration

How to Fix “Wifi Doesn’t Have a Valid IP Configuration” Error

Introduction "Wifi doesn't have a valid IP configuration" error is common among Windows users. When you receive this error...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
windows 10 keeps going to sleep

What to Do If Windows 10 Keeps Going to Sleep

Introduction It can be frustrating if suddenly your Windows 10 keeps going to sleep. You have to log back...
Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

AMAZON DEALS

By using this website you agree to accept our Privacy Policy and Terms & Conditions