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

Sponsored Content


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.

Sponsored Content


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.

Sponsored Content


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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

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

BEST OF ITECHGUIDES

schedule shutdown windows 10

How to Schedule Windows 10 to Shutdown at Specified Time

Introduction If you want to schedule shutdown for windows 10 (turn off) at specified time, this guide is for...
powershell version

4 Ways to Check the Current PowerShell Version on Your PC

Introduction If you are reading this guide because you want to learn how to find the PowerShell version on...
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...
How to Change Profile Picture on Spotify

How to Change Profile Picture on Spotify (from Desktop or Mobile)

Introduction You can change profile picture on Spotify from the desktop or mobile app. This guide covers the steps...
WhatsApp web

How to Use WhatsApp Web from a Browser on Your Computer

Introduction WhatsApp web is a WhatsApp service that allows you to use WhatsApp from a browser on your computer....

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because 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.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
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 could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

powershell windows 10

Frequently Asked Powershell Windows 10 Questions

This guide brings the top 8 Frequently asked Powershell Windows 10 questions. Get answers to common Windows 10 PowerShell FAQs…
dropbox login

Dropbox Login: Your Ultimate Guide to Dropbox

Introduction Dropbox login allows you to sign in to and use Dropbox. But what is Dropbox? Let's kick off...
How to Check Windows 10 Version

How to Check Windows 10 Version in 4 Easy Ways

Introduction Wondering how you can check your version of Windows 10? Here are 4 easy ways for you -...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...
file too large for destination file system

The File is Too Large for the Destination File System [Fixed]

What is the cause of "The File is Too Large for the Destination File System" Error? The "The File...

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