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 them take you to any cell or range within your Excel worksheet.

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 to your rows/columns.

For example, if you tell it to go to C10, it will always go to C10.  Add a new column in Column B, the hyperlink will still end up at C10.

Sometimes this is not the outcome we want to achieve.

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.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Highlight the range or select the cell that you want the Hyperlink to refer to:

select named range

 

STEP 2: Go to the Name Box on the top left hand corner of the worksheet and enter a name (with no spaces):

name box

 

STEP 3: Right Click on an Excel shape and choose Hyperlink:

right click on shape

This will open up the Insert Hyperlink dialogue box. Select the Defined Name that you set up in Step 2 and press OK.

insert hyperlink

 

STEP 4: Clicking on the Shape will highlight your referenced range.

You can add extra Columns/Rows in your worksheet by selecting the whole Column/Row and pressing the CTRL+ + shortcut.

Clicking on your Hyperlink will follow your referenced range!  Cool hey 🙂

Hyperlink Buttons - Named Range

HELPFUL RESOURCE:

728x90-2

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Replace Excel Formatting with Another Formatting Imagine this, you have a table full of bold text.  The bold text could also be all over your worksheet in random cells. Then you decide that the bold text does not suit your expected design and prefer red colored text instead. What would you do? Changing all of the forma...
Drop Down List with Data Validation One of the coolest features of Excel is to create a drop down list with your data.  It is one of the first things that I learned and use on a daily basis. You can create a drop down list to eliminate manual entry and re-entry of data, as well as reduce data entry mistakes if y...
Excel’s Fill Handle Tips This post was inspired by Mr.Excel's new book MrExcel XL: 40 Greatest Excel Tips of All Time and in particular the 3rd tip that I read called The Fill Handle Does Know 1,2,3... Excel knows to fill down/right when you are working with dates, days, months, years and even quarter...
Autofill Formulas in an Excel Table One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste. When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column. As you add extra...