{"id":2775,"date":"2016-04-07T23:48:30","date_gmt":"2016-04-07T21:48:30","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2775"},"modified":"2024-04-06T19:47:51","modified_gmt":"2024-04-06T17:47:51","slug":"fix-excel-hyperlinks-named-range","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/fix-excel-hyperlinks-named-range\/","title":{"rendered":"Fix Excel Hyperlinks to a Named Range"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/insert-hyperlink-in-excel\/\" target=\"_blank\" rel=\"noopener\">Hyperlinks in Excel<\/a> must be one of the funkiest features that I love playing around with!<\/p>\n<p>They allow you to create interactive buttons within Excel (without the need to create a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-macros-examples\/\" target=\"_blank\" rel=\"noopener\" title=\"Macro\" data-wpil-keyword-link=\"linked\">Macro<\/a>) and you can make\u00a0them\u00a0take you to any cell or range within your Excel worksheet.<\/p>\n<p>One shortfall is that when you set a Hyperlink to go to a cell reference, it will always reference the said cell, regardless of any additions\/deletions\u00a0to your\u00a0rows\/columns.<\/p>\n<p>For example, if you tell it to go to C10, it will always go to C10. \u00a0Add a new column in Column B, the hyperlink will still end up at C10.<\/p>\n<p>Sometimes this is not the outcome we\u00a0want to achieve.<\/p>\n<p>I will show you a trick where you can fix the referenced cell\/range using a Named Range, so that it does not move as the worksheet changes.<\/p>\n<p><br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/n6be5-5XU_U?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<span style=\"text-decoration: underline;color: #0000ff\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff;text-decoration: underline\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/Hyperlink-Buttons-Named-Range.xlsx\" rel=\"\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Hyperlink-Buttons-Named-Range.xlsx<\/span><\/a><\/span><\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong> <\/span><strong>Highlight the range<\/strong> or <strong>select the cell<\/strong> that you want the Hyperlink to refer to:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/select-named-range.jpg\" rel=\"attachment wp-att-2777\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Excel Hyperlinks to a Named Range\" class=\"alignnone size-full wp-image-2777\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/select-named-range.jpg\" alt=\"Fix Excel Hyperlinks to a Named Range\" width=\"359\" height=\"513\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/select-named-range.jpg 359w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/select-named-range-210x300.jpg 210w\" sizes=\"(max-width: 359px) 100vw, 359px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong> <\/span>Go to the <strong>Name Box<\/strong> on the top left hand corner of the worksheet and enter a name (with no spaces):<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/name-box.jpg\" rel=\"attachment wp-att-2778\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Excel Hyperlinks to a Named Range\" class=\"alignnone size-full wp-image-2778\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/name-box.jpg\" alt=\"Fix Excel Hyperlinks to a Named Range\" width=\"133\" height=\"63\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong> <\/span>Right Click on an Excel shape and choose <strong>Hyperlink<\/strong>:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/right-click-on-shape.jpg\" rel=\"attachment wp-att-2780\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Excel Hyperlinks to a Named Range\" class=\"alignnone size-full wp-image-2780\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/right-click-on-shape.jpg\" alt=\"Fix Excel Hyperlinks to a Named Range\" width=\"288\" height=\"327\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/right-click-on-shape.jpg 288w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/right-click-on-shape-264x300.jpg 264w\" sizes=\"(max-width: 288px) 100vw, 288px\" \/><\/a><\/p>\n<p>This will open up the <strong>Insert\u00a0Hyperlink<\/strong> dialogue box.\u00a0<strong>Select the Defined Name that you set up in Step 2<\/strong> and press <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/insert-hyperlink.jpg\" rel=\"attachment wp-att-2779\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Fix Excel Hyperlinks to a Named Range\" class=\"alignnone size-full wp-image-2779\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/insert-hyperlink.jpg\" alt=\"Fix Excel Hyperlinks to a Named Range\" width=\"640\" height=\"330\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/insert-hyperlink.jpg 640w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/insert-hyperlink-300x155.jpg 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:<\/strong> <\/span>Clicking on the Shape will\u00a0highlight your\u00a0referenced range.<\/p>\n<p>You can add extra Columns\/Rows in your worksheet by selecting the whole Column\/Row and pressing the CTRL+ + <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/333-excel-shortcuts-for-windows-and-mac\/\" target=\"_blank\" rel=\"noopener\" title=\"shortcut\" data-wpil-keyword-link=\"linked\">shortcut<\/a>.<\/p>\n<p>Clicking on your Hyperlink will follow your referenced range! \u00a0Cool hey \ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/myexcelonline.leadpages.co\/leadbox\/140153773f72a2%3A17c5e0714b46dc\/5659822271758336\/\" target=\"_blank\" rel=\"attachment noopener wp-att-2738 noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone wp-image-2738 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/728x90-2.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/728x90-2.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/728x90-2-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hyperlinks in Excel must be one of the funkiest features that I love playing around with! They allow you to create interactive buttons within Excel (without the need to create a Macro) and you can make\u00a0them\u00a0take you to any cell or range within your Excel worksheet. One shortfall is that when you set a Hyperlink [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17193,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Fix Excel Hyperlinks to a Named Range","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,41],"tags":[88,64],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2775"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2775"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17193"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}