Home   Excel - Creating and Using Hyperlinks

 
 

 Hyperlink Description

A hyperlink can be either text or an object such as a picture or a chart element that, when clicked on, takes you to another location such as a specific location in your document or brings up information from another file or web page. You have probably used hyperlinks on the Internet. Clicking on a hyperlink in a web page can either take you to a different location on that page or to a completely different web page. Hyperlinks are usually displayed in a different color usually blue and often underlined so that they are easily identifiable.  To make them even more identifiable the mouse cursor changes to a pointing hand indicator when moved over a hyperlink. You can use hyperlinks in Excel to link to the following:

Clicking on a hyperlink to another file opens the file in the program associated with that file type.  For example, if you create a hyperlink to a file with the extension of docx, Excel will open that file in Word when that hyperlink is clicked on.   If you create a hyperlink to a file with the extension of psd, Excel will open that file in Photoshop.

 How to Create a Hyperlink


Hyperlinks are created in the Insert Hyperlink window.   To bring up the Insert Hyperlink window you would perform the following steps:
  1. Click the cell in which you want to place the hyperlink or the object you want to hyperlink.
  2. On the ribbon click the Insert Tab.  In the Links group click the Hyperlink button. Insert Hyperlink window


From the Insert Hyperlink window you:

You select the type of link you want to create in the left hand side of the window.   The options that appear to the right of the Link to: area change depending up your selection.    If there was any text in the cell when you inserted the Hyperlink it appears in the “Text to display” text box.   Whatever you enter in this text box will display in your cell, it will override any text currently in the cell.  To the right of the text box is a ScreenTip… button.   Clicking on the ScreenTip brings a window in which you can enter a description of the hyperlink.  This description will display for a short time when the user moves the mouse over the hyperlink.  The location of where you are linking to would be entered in the Address box.

 Remove an Excel Hyperlink

A text hyperlink can be removed by right-clicking in the cell that contains the Hyperlink then selecting Removing a Hyperlink Remove Hyperlink from the context menu. 

If the hyperlink is an object, you will need to remove the hyperlink through the ribbon by clicking on the Insert Tab, then in the Links group select Hyperlink.  In the Edit Hyperlink window click on Remove Link.   







 Practice Walkthrough for Hyperlinking to a Web Page

Excel automatically creates hyperlinks to an email address if you type an appropriate address.  Excel will also automatically create a hyperlink to a web page if you type an appropriate web address that starts with http:// or www in a cell.  This automatic hyperlinking only works if these AutoFormat As You Type features are turned on.

Perform the following steps for this practice:

  1. Create a new workbook named Hyperlinks
  2. Click on File | Options.  From the Excel Options window select Proofing. Click on the AutoCorrect Options… button on the right pane of the window.  This brings up the AutoCorrect window.

    Click on the AutoFormat As You Type tab. If the Internet and network paths with hyperlinks option isn’t already selected then select it. Click on OK

    Select option for AutoFormat As You Type

    Click on the OK button on the Excel Options window.

  3. Type www.ibm.com in cell A1 then press Enter. Because you set the option to automatically create hyperlinks whenever you type an appropriate web address (starts with http:// or www.) a hyperlink is automatically created.
  4. Move your cursor over the text you just entered in cell A1. The cursor should display as a pointing finger. A tooltip displays the hyperlink address. Click on the hyperlink. Close the Web page. The hyperlink works but what if you wanted the words IBM Corporation to appear in the cell instead of www.ibm.com  You will need to edit the hyperlink.
  5. Right-Click on cell A1 then select Edit Hyperlink.
  6. Change the text in the “Text to display” text box to IBM Corporation.  The address should be correct.  Click OK

    Change the Text for the Hyperlink display

  7. Cell A1 now displays IBM Corporation. Click on the text. IBM’s Website should display. Close your Internet Browser.
  8. Type jhall@hotmail.com in cell A3 then press Enter. Click on the email address you just created.
    When you click a hyperlink to an e-mail address, your e-mail program automatically starts and creates an e-mail message with the correct address in the To box, provided that you have an e-mail program installed such as Outlook.
  9. Type www.MadeinUsa.com in cell A5 then press Enter. Widen the column so that all the text fits within the cell. Move your cursor over the text you just entered in cell A5. The first w in the hyperlink is underlined. Move your mouse cursor over the underline, a menu Tag should appear. Click on the menu tag to bring up a context menu.

    The text remains but the hyperlink is removed.

  10. Click on cell A7. On the ribbon, on the Insert tab, in the Links group, click Hyperlink *Note this step can also be performed by right-clicking cell A1 and selecting HyperLink from the context menu.
  11. In the Link to: area select Existing File or Web Page
  12. Type CDP.com in the “Text to display” text box.
  13. Click on the Screen Tips… button. Enter “Columbia Data Products” for the ScreenTip text. Press OK.

    Screen Tip for Insert Hyperlink window

  14. Type www.cdp.com in the Address: box. Excel automatically adds the prefix http:// to the address. Click OK.
  15. You should now have the hyperlink CDP.com in cell A7. Move your cursor over the hyperlink. The Screen Tip Columbia Data Products displays. Click on the hyperlink. If you are connected to the Internet this will bring up Columbia Data Products home page. Close the web page.
  16. Right-click cell A7. Select Remove Hyperlink.

  Practice Walkthrough for Hyperlinking to a File

  1. Create a new file in Notepad.
  2. type the text "This is text that I am linking to"
  3. Save the file to your My Documents folders or a location you can easily find the path to. Name the file linkedtext.txt
  4. Close the Notepad program
  5. Right-click in cell C5 on your spreadsheet and select Hyperlink...
  6. Enter Notepad file in the "Text to display" text box.
  7. There are several ways to get the address and file name in the Address box. You can type the path directly. You can click the Browse for File button to help locate the linkedtext.txt file or you can click on the down arrow to the right of the Look in text box to get to the correct folder and then select the file from the list box.

    *Note:   If your file is moved from the path you provided, the hyperlink will not work.

    *Note:   If you use Browse for File you will need to click on the down arrow for the file type when the Link to File window appears then select All Files in order to be able to see text files.
    Ways of Getting the Address for the Edit Hyperlink
  1. Click on the OK button
  2. Click on the hyperlink. Note: If you get the following message click on the Yes button.

    Microsoft Security Notice

    Notepad should start with the linkedtext.txt file loaded.
  3. Close the Notepad program.

 Practice Walkthrough for Hyperlinking to a location in the Current Workbook

The capability to Hyperlink to any cell in your workbook is really convenient when working with a large workbook. In this exercise you will create a hyperlink that will jump you to cell DA on sheet2.
  1. Use the same workbook as the previous practice.
  2. Add another worksheet
  3. On Sheet1, right-click in cell D7 then select Hyperlink...

    Hyperlink Screen Tip

  4. Select Place in This Document in the "Link to" area
  5. Enter Oct Sales in the "Text to display" text box.
  6. Enter D35 for the cell address in the "Type the cell reference" text box.
  7. Select Sheet2 for the Cell Reference
  8. Click on the Screen Tip... button. Enter Chart for October Sales for the "ScreenTip.text". Click OK.
  9. Click OK on the "Insert Hyperlink" window.
  10. Move your cursor over the Hyperlink. The ScreenTip that you created displays. Click on the Hyperlink. The active cell is now D35 on Sheet2.
  11. Click on the Sheet1 tab.

 Practice Walkthrough for Creating a Hyperlink to a newly Created Excel Workbook

You can create a Hyperlink that when clicked on creates a new Excel workbook and then opens that workbook. The Excel workbook will be given a name that you have assigned within the Hyperlink options.
  1. Use the same workbook as the previous practice
  2. On Sheet2 enter the text "Hyperlink to a New Document" in cell A1. Press Ctrl + Enter
  3. Right-click on cell A1 and select Hyperlink.
  4. The Insert Hyperlink window appears. In the Link to: area clik on Create New Document
  5. Click on the ScreenTip... button
  6. Enter "Takes you to a newly created Excel workbook named Hyperlink Practice" for the ScreenTip text. Click OK.
  7. Enter "Hyperlink Practice" for the Name of the new document.
  8. Change the path if you need to.
  9. In the When to edit section you have 2 options; you can either select to go immediately to the newly created workbook or you can create the workbook and then open it whenever you choose to.
    Select the option Edit the new document now.

    Change the document in the Insert Hyperlink window

    Click OK.
  10. Your cursor should now be in cell A1 of the newly created workbook named Hyperlink_Practice. Go back to your workbook named Hyperlinks. Click on Sheet2 tab cell A1. Since Hyperlink_Practice has already been created Excel will not try to recreate it, but it will take you to the Hyperlink_Practice workbook. Before opening the Hyperlink_Practice workbook Excel displays a Security Notice window. Since you created the hyperlink yourself you know it is safe so click on the Yes button.

    Microsoft Excel Security Notice