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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Row Differences If you have two rows that you want to compare, for example, sale amount versus amount paid, you can easily filter the differences between these two columns by going in to the Find & Select > Go To Special > Row Differences.Once the cells have been identified, you ca...
Extra Excel Table Styles The Excel Table Styles give a user a choice of different styles ranging from Light, Medium and Dark.There are over 50 choices depending on your favorite style or company standard but you are not only limited to those.You have extra Table styles in the Page Layout tab in y...
Highlight All Excel Formula Cells Whenever you are auditing an Excel worksheet and need to know where all the formulas are located, a great way is to highlight the formula cells in a distinctive color.  This is how it is done:1.Select all the cells in your Excel worksheet by clicking on the top left hand corn...
Show The Difference From Previous Years With Excel... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you ...