 |
|
|
|
|
Download the associated Workbook for this lesson
Hyperlinks supply us with a convenient way to jump to a specified document on a hard drive, network, Intranet or Internet. We can also use them to go to a specific location within a document or Workbook. There are two methods we can use to insert a Hyperlink. They are by clicking the Hyperlink symbol on the Standard toolbar (Globe with paper clip) Ctrl+K, or by using the HYPERLINK Worsheet function. This can be found in the Insert function dialog box under the Lookup & Reference category.
Some uses for hyperlinks might be:
To create a list of Internet or Intranet locations
Create a list of Workbooks or Documents stored on a hard drive or network
Create a list of ranges and/or Worksheets in an Excel workbook to jump to easily
Create a list of email addresses
The idea of Hyperlinks is to save time and errors.
Let's look at creating a Hyperlink in a cell using the Insert Hyperlink dialog box.
The quickest way to display this is to select the cell you want the Hyperlink in and push Ctrl+K. We only have two boxes and one option button to work with here so it is very easy.
In the Link to file or URL (Uniform Resource Locator) box either type a valid URL or File path or use the Browse button. For an email address you need to select mailto from the drop-down list, then type the address. If you are using a URL it is usually best to select from the Drop-down list or copy and paste it into the box. As we cannot use a right click in this box, all copying and pasting must be done with Ctrl+C and Ctrl+V.
The second box is optional. We can use it to jump to a specific location within the File or URL chosen above. A valid location is any named range, bookmark (Word), database object (Access) or slide number. (PowerPoint). Again you can type, paste or use the Browse button.
Depending what you have placed in the Link to file or URL will determine what will appear when you click the Browse button. For example If you have placed in a Workbook path and clicked Browse Excel will show the Browse Excel Workook dialog complete with all Worsheet names and Named ranges for the specified Workbook. If we have left the Link to file or URL box blank, the Browse Excel Workbook dialog will be for the active Workbook.
The Use relative path for hyperlink means that if we linked to a Workbook that is on our hard drive or a network and it was moved to another location, the Hyperlink would automatically follow it to its new location. If we clear this checkbox our Hyperlink would always point to the same location, regardless whether the Workbook still resided in that location or not.
Once we have set our options, we simply click OK and the Hyperlink will be inserted in the active cell.
Now all we need to do is click the Hyperlink and Excel will take us straight to our specified location. To delete a Hyperlink you need to select a cell adjoining it and use the arrow key to select the cell, then either go to Edit>Clear>All or right click and select Hyperlink>Edit Hyperlink, then click Remove Hyperlink. The Edit>Clear>All will remove the text and Hyperlink, while Remove Hyperlink will only remove the Hyperlink and not the text.
The second method for creating a Hyperlink is by using the HYPERLINK Worksheet function. The only differences between the two are that the HYPERLINK function allows us to specify a Friendly name. This means instead of having a long URL address we can mask this with any text we want. The other difference is that the URL or File and Location are all stored in the first argument Link _Location. The Excel help gives some good examples of how you would state these. Just push F1 and type Hyperlink