References In Excel Formulas – Absolute and Relative

.

This article is not about Excel Macro but this is about Absolute and Relative References in Excel formula. In Excel Formulas, as you have seen, for referring any cell, we use the Address (Reference) of that Cell like A1, B2 etc. This is called Cell Reference in Excel. In other words you can call it as Cell Address. This Cell reference is categorized in two category called : Absolute Reference and Relative Reference

1. Relative Reference:

While referring any Cell in Excel if you give the reference as A1, B50 etc. This is called Relative Reference. In this case suppose you have used formula as =A1 somewhere and drag down in the same column then “1” of the formula =A1 will be incrementing automatically and for example at 50th Row the Formula will turn to “=A50”. Similarly if you drag the same in same row then A of the formula =A1 will be changing as per the Column name like B, C, D etc. For example formula =A1 will be turned to =E1 in the E column.

2. Absolute Reference:

If you refer the same Formula =A1 as =$A$1. This is called Absolute Reference. while dragging in the Column or row Cell Reference remains same before which $ sign is placed. You can also refer a cell like =A$1 or =$A1
In below example we will see what is the difference between all the above mentioned 3 ways of referring a cell A1.
 

Reference Explanation
=A1 Both Column Name and Row Number are changeable. On dragging in same column Number will increase and on dragging in same row, column Name will be increasing as explained Above. This is called Relative Reference
=$A$1 Both Column Name and Row Number are fixed. On dragging formula anywhere Column Name and Row number, none of them will vary. It will remain fixed. Note:This gets changed only when you delete any row or column. At the end of this Article, we have discussed how to handle this as well.
=$A1 Column Name will remain always fixed but Row Number will be changing. Here Column Name is Absolute but Row Number is Relative.
=A$1 Row Number will remain always FIXED but Column Name will be changing. Here Row Number is Absolute but Column Name is Relative.

How to Toggle between all these above References:

In Excel Formula just by pressing “F4” key you can toggle between all the above style of References. So if you want to Change the formula in a specific Style then keep pressing F4 and stop when it is as per your expected style.

Even with Absolute Referencing, if you delete any row or column then formula gets changed. If you want to overcome that also, then you can use INDIRECT function. For example: =INDIRECT(“A1”).

What is =INDIRECT():

This function does not consider the reference as “Address”. It accepts it as a normal Text rather than Address. and that is the reason it never change the Value “A1”. So if you want a Truly Absolute formula then use this INDIRECT function.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Rashid

    Hey it is a great explanation.
    Thanks Vishwa

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest