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

Autosum an Array of Data in Excel When you have an array of data in Excel with Totals at the bottom and to the right of the data, you can quickly fill in the Totals with the Autosum button.STEP 1: Highlight your data including the "Totals" row and column;STEP 2: Click the Autosum button (under the Home or...
Excel Hyperlinks: Buttons Excel´s hyperlink capability is amazing but many people don't use it as they don't know its full capabilities.With a hyperlink you can link an object/text to open an existing file on your desktop, go to a website, open up an email to a specific contact or go to a cell within ...
Summarize Data With Dynamic Subtotals What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
How to Calculate Percentage in Excel In calculating percentages in Excel, there are a lot of ways that you could do this:What is the percentage of a number? What is the percentage change of value i.e. The percentage increase or decrease What is the proportion against a total value?Let us go over ...