{"id":12111,"date":"2011-12-19T19:17:56","date_gmt":"2011-12-19T19:17:56","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1107"},"modified":"2011-12-19T19:17:56","modified_gmt":"2011-12-19T19:17:56","slug":"relative-and-absolute-reference","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/","title":{"rendered":"References In Excel Formulas – Absolute and Relative"},"content":{"rendered":"

This article is not about Excel Macro but this is about Absolute and Relative References<\/strong> 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. <\/strong> This Cell reference is categorized in two category called : Absolute Reference<\/strong> and Relative Reference<\/strong><\/p>\n

1. Relative Reference:<\/h3>\n

While referring any Cell in Excel if you give the reference as A1, B50 etc<\/strong>. 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”<\/strong> of the formula =A1<\/strong> 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<\/strong> of the formula =A1<\/strong> 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.<\/p>\n

2. Absolute Reference:<\/h3>\n

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<\/strong>
\nIn below example we will see what is the difference between all the above mentioned 3 ways of referring a cell A1.
\n <\/p>\n

\n\n\n\n\n\n\n\n
Reference<\/strong><\/td>\nExplanation<\/strong><\/td>\n<\/tr>\n
=A1<\/strong><\/td>\nBoth 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<\/strong><\/td>\n<\/tr>\n
=$A$1<\/strong><\/td>\nBoth 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.<\/strong><\/td>\n<\/tr>\n
=$A1<\/strong><\/td>\nColumn Name will remain always fixed but Row Number will be changing. Here Column Name is Absolute but Row Number is Relative.<\/td>\n<\/tr>\n
=A$1<\/strong><\/td>\nRow Number will remain always FIXED but Column Name will be changing. Here Row Number is Absolute but Column Name is Relative.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n

How to Toggle between all these above References:<\/h3>\n

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.<\/p>\n

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<\/strong> function. For example: =INDIRECT(“A1”)<\/strong>.<\/p>\n

What is =INDIRECT():<\/h3>\n

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<\/strong> function.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672],"tags":[],"class_list":["post-12111","post","type-post","status-publish","format-standard","hentry","category-excel-formula"],"yoast_head":"\nReferences In Excel Formulas - Absolute and Relative - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"References In Excel Formulas - Absolute and Relative\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2011-12-19T19:17:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"References In Excel Formulas – Absolute and Relative\",\"datePublished\":\"2011-12-19T19:17:56+00:00\",\"dateModified\":\"2011-12-19T19:17:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\"},\"wordCount\":514,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Formula\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\",\"name\":\"References In Excel Formulas - Absolute and Relative - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2011-12-19T19:17:56+00:00\",\"dateModified\":\"2011-12-19T19:17:56+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"References In Excel Formulas – Absolute and Relative\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"References In Excel Formulas - Absolute and Relative - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/","og_locale":"en_US","og_type":"article","og_title":"References In Excel Formulas - Absolute and Relative","og_description":"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 […]","og_url":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2011-12-19T19:17:56+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"References In Excel Formulas – Absolute and Relative","datePublished":"2011-12-19T19:17:56+00:00","dateModified":"2011-12-19T19:17:56+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/"},"wordCount":514,"commentCount":1,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Formula"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/","url":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/","name":"References In Excel Formulas - Absolute and Relative - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2011-12-19T19:17:56+00:00","dateModified":"2011-12-19T19:17:56+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2011\/12\/relative-and-absolute-reference\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"References In Excel Formulas – Absolute and Relative"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12111"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12111"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12111\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}