Beam Me Up Scotty – Excel Hyperlinks

Posted on March 31st, 2011 in Excel Howtos , Huis , Posts by Hui - 35 comments

What are Hyperlinks ?

A Hyperlink is a reference to a document, a location or an action that the reader can directly follow by selecting the link.

Hyperlinks are used extensively on the Internet and are generally Words highlighted in Underlined Blue <– Like that.

The use of Hyperlinks in Excel has been extended to a number of areas and this includes:

  • Opening Files (of any type)
  • Opening Web Pages (Internet or Intranet)
  • Jumping/Navigating to locations within an existing document
  • Creating New Documents (Excel files only)
  • Sending Emails

Microsoft has added the ability to place Hyperlinks,

  • Directly on an Excel worksheet ,
  • Connected to a number of worksheet objects, including shapes, charts and wordart
  • Included as a worksheet formulas.
  • Programmatically using VBA

These 4 methods above will be discussed here.

Inserting Hyperlinks

As described above there are 4 methods for inserting hyperlinks in an Excel Workbook.

Directly on an Excel worksheet

There are 3 ways to insert a Hyperlink directly into a cell, either:

Right click on the cell and select Hyperlink; or

Use the Insert, Hyperlinks Tab; or

Use a Keyboard Shortcut - Ctrl K

Connected to a number of worksheet objects, including shapes, charts and wordart

You can also add a Hyperlink to many objects within Excel including Pictures, Shapes, Text Boxes, Word Art and Charts.

Right clicking a lot of these objects brings up the Objects Shortcuts Menu, select Hyperlink…,

or

Select the object, Use the Insert, Hyperlinks Tab; or

Select the Object and Use the Keyboard Shortcut – Ctrl K

Hint: Right Clicking on Charts Doesn’t Show the Add Hyperlink option, so Select the Chart and Ctrl K

Adding Hyperlinks using Worksheet Formulas.

Hyperlinks can be added using worksheet formulas.

=HYPERLINK( Link Location, Name)

Link Location: This is the path and file name to the document to be opened.

The Link Location can refer to a place in a document – such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be the path on a server or a URL, HTTP or FTP and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet. The Link Location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.

Name: This is the text or value that is displayed in the cell.  The Name is displayed in blue and is underlined.

Eg:

Jump to a cell on Another sheet

=HYPERLINK(Sheet3!B3,”Monthly Budget”)

The above  will add a Hyperlink, titled “Monthly Budget” and link to Sheet3!B3 of the current workbook

Jump to a Named Range on Another sheet

=HYPERLINK(Budget,”Yearly Budget”)

The above  will add a Hyperlink, titled “Yearly Budget” and link to the Named Range “Budget” of the current workbook

Open a File on a network Drive

=HYPERLINK(“//Server01\01 Administration\Administration.docx”,”Open Admin File”)

The above  will add a Hyperlink, titled “Open Admin File” and link to the file at: //Server01\01 Administration\Administration.docx

Open a File on a network Drive at a specific bookmark

=HYPERLINK(“[//Server01\01 Administration\Administration.docx]Contents”,”Open Admin File @ TOC”)

The above  will add a Hyperlink, titled “Open Admin File @ TOC” and link to the Named Section “Contents” of the file at: //Server01\01 Administration\Administration.docx

Jump to a Web Page

=HYPERLINK(“http://chandoo.org/wp/”,”Goto Chandoo.org”)

The above  will add a Hyperlink, titled “Goto Chandoo.org” and link to http://chandoo.org/wp/

Send an Email

=HYPERLINK(“mailto:chandoo.d@gmail.com”,”Email Chandoo”)

The above will add a Hyperlink, titled “Email Chandoo” and send an email to chandoo.d@gmail.com

Adding Hyperlinks Programmatically using VBA

Hyperlinks can be added to a worksheet or a worksheet object programmatically using some simple code

Sheets(SheetName).Hyperlinks.Add Anchor:=Sheets(SheetName).Range(Range), Address:=””,  SubAddress:=”Address!Range“,  TextToDisplay:=Name

Where:

SheetName: The Name of the Sheet where the Hyperlink is to go

Range:  The Range where the Hyperlink is to go

Address!Range: The address and Range linked to in the Hyperlink

Name: The Display Name of the Hyperlink

Types of Hyperlinks

There are 5 Types of Hyperlinks which Excel offers, each is described below:

  • Existing File
  • Existing Web Page
  • Place in This Document
  • Create a New Document
  • Send an Email Link

Existing File

Select the existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Use the Bookmark… button to jump to predefined Named Ranges and common Cell References dialog

Existing Web Page

Select the Existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Place in This Document

Select the Place in this Document icon in the Link to: area

Type in Cell Reference using the Type in Cell Reference: area of the dialog or select a Defined Names in the Defined Names area

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink


Create a New Document

Select the Create New Document icon in the Link to: area

Type in the Name of the New Document in the Name of the New Document: area of the dialog.

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

You can choose wether to Edit the File Now or Later in the When to Edit area

Send an Email Link

Select the Email Address icon in the Link to: area

Type in the Email Address in the Email Address: area of the dialog.

Add your Display Text in the Text to display: area

Add your Email Subject in the Subject: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink.

Editing Hyperlinks

Once you have a hyperlink established you can edit the hyperlink by right click on the hyperlink and select Edit Hyperlink

The Edit Hyperlink dialog will vary depending on the type of Hyperlink as described above.

Deleting Hyperlinks

Once you have a hyperlink established you can delete the hyperlink by right click on the hyperlink and select Remove Hyperlink


Hyperlink Uses

Hyperlink can be used for a number of uses as described above.

Tables of Contents

One common use of hyperlinks is the creation of Tables of Contents.

The construction of a Table of Contents page was discussed here Table of Contents

The construction of Tables of Contents can also be automated using some simple VBA.

So instead of reinventing the wheel I will direct you to The Microsoft Office Blog where Tables of Conents were recently discussed.

Table of Contents 1 or Table of Contents 2

Dealing with Lots of Hyperlinks

The following 2 posts at http://chandoo.org/forums have solved users problems and will easily be adapted to other Hyperlink issues

Find Dead Hyperlinks

http://chandoo.org/forums/topic/check-broken-external-hyperlinks

Edit Hyperlinks

http://chandoo.org/forums/topic/marco-for-editing-link-in-workbook

How have you used Hyperlinks?

How have you used Hyperlinks?

Let us all know in the comments below:

Your email address is safe with us. Our policies

Written by Hui...

Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

35 Responses to “Beam Me Up Scotty – Excel Hyperlinks”

  1. Rich says:

    I’ve just started using the formula version “=HYPERLINK(“S:\Reserving\Management Judgment comments\check for MJ comments TOS 006 “&TEXT(month,”mmmm yyy”)&”.xls”,”MJ Comments”)” and making it dynamic as some of the files that I use have a new name each month, or even each day.

  2. Dennis says:

    I have an Excel file called “Important Stuff’. Rather than use post it notes, I put information I need into this file. I create hyperlinks to the files I most reference; especially when I have many different versions. I then add comments in the cells next to the hyperlinks to tell me what the differences are. I rarely have to search as everything is right there in the one file.

  3. Gregory says:

    Great article! I’m bookmarking this for reference. Thanks for putting ‘all you need to know about hyperlinks’ in one place.

  4. Kevin says:

    Hyperlinks are great but “Hyperlinks for navigating a spreadsheet are lost if saved as a “pdf”. Even if you are utilizing Adobe Writer 8.0.0″

  5. Patty says:

    Great info! I never thought of using hyperlinks in Excel. Mostly used them in Word, email and especially in Powerpoint.

  6. One cool thing I do is combine the hpyerlink function with a custom function I had help in building. I have a list of contractors from our Accounting Software (Quickbooks). We keep scanned copies of the 1099s in one folder. We can then download the data, which includes the vendors TaxPayer ID. The formula then checks for the file. If it exists, it provides a link to open it. If it doesn’t it says so.

    Here is the custom function code:
    >>>>>>>>>>>>>>>>>>>>>>>>>
    Public Function MyFileExists(MyFilePath As String) As Boolean
    Dim objFSO As Object
    Set objFSO = CreateObject(“Scripting.FileSystemObject”)
    MyFileExists = objFSO.FileExists(MyFilePath)
    Set objFSO = Nothing
    End Function
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    Bobby
    BobbyBluford.com

  7. Swapnil says:

    This is aewsome. I have been using hyperlink since a long time. I like this option as this helps me create my Dashboard like a webpage. Thanks for sharing this, I got more insight of this option. :)

  8. Cameron says:

    Bobby: Fantastic idea! What’s amazing is as soon as I started reading ‘scanned copies’ in your post I was thinking ‘hey that would be cool if you could just link to the files if and only if they exist’. Then you described how you do exactly that!
    Nice one.

  9. Jon says:

    I use hyperlinks to jump from one location in a tab to another. When I add rows, the hyperlink destination location does not reflect the rows I just inserted. Does anyone know a way around this?

  10. Hui... says:

    @All
    If you like playing pranks on you co-workers or friends here is a simple Excel Hyperlink prank.
    .
    Open a workbook, probably not an overly important one, and select a page then either Use Ctrl A twice or select all the cells by clicking in the area to the upper left of A1.
    Right click any cell and insert a Hyperlink, doesn’t matter what its to, Another page, a Web Site or Send an Email
    close the file
    .
    When anybody opens the file and clicks anywhere on the page it will execute the Hyperlink, even on a blank cell, where the Hyperlink isn’t shown
    This works particularly well on Blank worksheets
    .
    To remove right click anywhere on the page and Remove Hyperlinks

  11. JJ says:

    I have been wrestling hyperlinks to PDF files for over a year now, I can insert hyperlinks fine, and they look beautiful, but when click on them it comes up with “Cannot Open the Specified File”. Does anyone know if this is because PDFs are unsupported by Excel or can other people get hyperlinks to PDFs working OK.
    My application is a register of approved capital expenditure projects and the link is to a PDF scan of the signed approved document.

    • V.Fitzsimmons says:

      Did you add “.pdf” when you created the link?

    • Mike Rizza says:

      I know this is an old comment, but we had the same problem too and the solution takes a little digging.

      1. Open Adobe Reader X
      2. Pick “Preferences” from the “Edit” menu
      3. Pick the “General” category on the left.
      4. Uncheck the “Enable Protected Mode at startup” box at the bottom.
      5. Close Adobe Reader and retry opening the PDF file from Excel and it should work now.

      Abobe Reader XI has a similar problem with a different error message. The settings in Reader XI seem to be Edit > Preferences > Security (Enhanced). At first I tried unchecking “Enable Protected Mode at startup” as this was the fix in Acrobat X. This did not work for me the first time. Then I tried unchecking “Enable Enhanced Security” and it started working. Then to test it, I re-checked both boxes… and it still works. I am not sure if other settings changed along the way, so I can’t confirm the resolution other than “try this and see if it works!”

  12. Hui... says:

    @JJ
    What version of Excel are you using ?
    and
    What PDF Reader are you using ?
    I assume you are using the Link to an Existing File or Web Page dialog?
    Because that has worked as described for years without error

  13. JJ says:

    I am using excel 2007
    The PDF reader I am using is Adobe Reader X.
    So nothing really unusual.
    I am using the Insert Ribbon, then the Hyperlink Icon.
    I just had anohter play with it – it seems to work OK if the PDF file is on my C drive, but as soon as the PDF file is on a network drive it comes up with the error.

  14. mabs says:

    Dear Bobby i think your solution is very near to my requriment but can please explain in layman terms . I have a set of files in a folder let say a,b,c,d,e and i have a range of column in excel a1,b1,c1,d1,e1 .So if i click cell a1 file “a” should open. For range of 5 cells we can hyperlink each cell but if i have 100s of cells and files How can i apply hyperlink to all of them at once please explain

  15. Mark says:

    I’m using excel 2010 & adobe reader 10 – getting the same error “Cannot Open the Specified File” when linking to a file on a networked drive. File opens fine if it’s on my PC.
    Hope this helps.

  16. Fiaz says:

    Say in cell Z1, I have the URL: http://www.microsoft.com

    And, I have a rectangle shape near A1… and I want it to have a hyperlink… and the URL for the hyperlink should be the URL in cell Z1.

    So, when someone clicking the Rect will be taken to http://www.microsoft.com

    When the URL is changed in cell Z1 to say http://www.yahoo.com

    Now, when clicking the Rect should take him to the new URL.

    How to achieve this? Thanks.

  17. Nanette says:

    I am using Excel 2007. I have a spreadsheet with hyperlinks that were created using formulas, such as =HYPERLINK( Link Location, Name).

    The hyperlinks work fine while I am in Excel, so I know they are active. However, when I use Acrobat 10 Pro to pdf the spreadsheet (and select the option to “Add Links”) the Web links are no longer active.

    I imagine it’s because Acrobat cannot handle the “behind the scenes” commands such as a formula, and then just uses the visible text in that particular cell.

    Has anyone found a work-a-round for this problem?

  18. Kevin says:

    The problem is that the hyperlinks in Excel are nothing more than cell references. However the cell disappear when saving as .pdf.

    In Powerpoint, the slide references can convert to pdf and continue to work.

  19. Brad says:

    I have a single page dashboard created, where I would like each of the graphs to serve as a hyperlink to the actual data that creates the graph.  The problem I’m running into is the URL for the hyperlink can be very long, depending on the data used to create the graph.  Does anyone know how to get around the 255 characters limit Excel has on URLs?  I used =Hyperlink(A1)&Hyperlink(A2) without Excel returning an error, but when I click on the link, it doesn’t do anything.  Any help would be greatly appreciated.

  20. ABZ says:

    I need to use hyperlink in excel to brows a web page according to each row 
    of a table that it’s data comes from a external source such a sql connection.
    but the text I have genarated from sql query “=hyperlink(“Http://somthing.com”,”WebPage”)”
    appearing just like above text if i edit i in sheet for example adding some space after of before text and accepting changes then link become a real link
    any kelp!
    I would be appreciate 

  21. Ben says:

    The simplest way to add web page links is:
    1. In your browser, copy the url
    2. Paste the url into a cell
    3. Press F2
    Done!

  22. mohanraj says:

    Hi Gents

    i don’t have not much knowledge about hyper link.Could please any

    one can explain how hyper link has been working.

    • Hui... says:

      @Mohanraj

      A hyperlink is like a shortcut
      By selecti8ng the hyper link it takes you somewhere else

      Hyperlinks can:
      Take you some place else, like another sheet or workbook or open other file, they don’t even have to be an Excel file

      Hyperlinks can be placed in cells, ranges or objects in Worksheets like Charts or Rectangles etc, all of which trigger the hyper link when you click them

      The post should tell you the rest of the story on how to use them

  23. Shafi says:

    Hi I am trying to insert an activex control from Developer tab and add hyperlink (web address) to it. Hyperlink button is not enabled in Excel 2010. The same option is available in Excel 2007. I am trying to this by C# code using this function.
    ActiveSheet.Hyperlinks.Add(ActiveXObject,”http:\\www.gmail.com”,Type.Missing,”Screen Tip”,Type.Missing);
    This code throws ‘invalid argument’ exception. I tried to use ‘Application.Selection’ for anchor as first argument with out any success.
    Any help is appreciated.

    Regards,

  24. Thanks with regard to providing these types of
    good information.

  25. sam says:

    Please tell any body..
    Can we add a shortcut key as address of a hyperlink in Excel so that when we click on that hyper link the shortcut key works.

  26. ST says:

    I have an excel sheet with quite a lot of hyperlinks to files and folders in the server. We will be implementing new servers as part of a project, which means the directory structure of the old server will be moved to the new one.
    Is there an easy way to keep the existing hyperlinks intact when moving to the new server? or do I have to edit each hyperlink and start all over again?
    Your help on this much appreciated.
    Cheers
    ST

  27. Mohammad Faizan says:

    useful information

  28. Fahad says:

    trying to hyperlink a cell with an object placed in shared folder.
    It only kinks the shared folder but not the object in it…help!!!

  29. Latoya Agcaoili says:

    I love the details on your websites. Thanks for your time!

Leave a Reply