One of Excel's features that I use every day to save time is Hyperlinks.
Hyperlinks allow me to instantly jump to other places in my worksheet or workbook with one click, just as they do on the Internet. In Excel, a hyperlink can be linked to other workbooks, other documents, websites, and many other places.
Once you see the benefit of hyperlinks, you will want to use them everywhere.
Let's look at how we can use some simple hyperlinks to create a powerful navigation system in any Excel workbook.
Say you have a Main sheet in a multi-sheet workbook. You are constantly going back and forth between the Main sheet and the other sheets. That requires a lot of clicking and scrolling of sheet tabs. Wouldn't it be easier if you could just click a link on your Main sheet to jump to a specific spot in any worksheet and then click another link on each worksheet to jump back to the Main sheet?
Creating hyperlinks in Excel is really simple. Let's try it now.
1)
Open a new blank workbook. Ensure that there are 3 sheet
tabs;
2) Rename Sheet 1 to 'Main';
3) In column cell A1 type 'Navigation';
4) In A2, right-click and choose Hyperlink... from the menu that pops up;
5) Click 'Place in this Document' under 'Link to' on the left side of the Insert Hyperlink dialog;
6) Select Sheet2 from the document map. If you want to jump to a specific cell on Sheet 2, you can type it in the 'cell reference' field. The default is A1;
7) The 'Text to Display' field shows 'Sheet 2!A1'. This is the text that will appear in the cell as your hyperlink – it is not the hyperlink itself. If you want, you can change this text to anything you want or you can simply edit the cell contents afterwards. For this example, edit the 'Text to Display' to be just 'Sheet 2'. If the cell you are adding a hyperlink to already has text, its contents will show up in 'Text to Display' field;
8) Click OK. You now have a hyperlink that will take you to from your Main sheet to Sheet 2;
9) Repeat steps 4 to 8 to add hyperlinks in cell A3 for Sheet 3;
10) Select cell A1 in Sheet 2 and repeat steps 4 to 8, but this time, link to your Main sheet;
11) Now, Copy and Paste this cell to A1 on each of the other sheets.
2) Rename Sheet 1 to 'Main';
3) In column cell A1 type 'Navigation';
4) In A2, right-click and choose Hyperlink... from the menu that pops up;
5) Click 'Place in this Document' under 'Link to' on the left side of the Insert Hyperlink dialog;
6) Select Sheet2 from the document map. If you want to jump to a specific cell on Sheet 2, you can type it in the 'cell reference' field. The default is A1;
7) The 'Text to Display' field shows 'Sheet 2!A1'. This is the text that will appear in the cell as your hyperlink – it is not the hyperlink itself. If you want, you can change this text to anything you want or you can simply edit the cell contents afterwards. For this example, edit the 'Text to Display' to be just 'Sheet 2'. If the cell you are adding a hyperlink to already has text, its contents will show up in 'Text to Display' field;
8) Click OK. You now have a hyperlink that will take you to from your Main sheet to Sheet 2;
9) Repeat steps 4 to 8 to add hyperlinks in cell A3 for Sheet 3;
10) Select cell A1 in Sheet 2 and repeat steps 4 to 8, but this time, link to your Main sheet;
11) Now, Copy and Paste this cell to A1 on each of the other sheets.
You have just created a navigation system for quickly moving among the sheets in your workbook.
Now, this is just a very basic example of using hyperlinks, but even so, you can see how useful they can be if you have a large complex workbook.
If you don't want the hyperlinks to print or interfere with your data, you can add them in cells outside your print area or you can use text boxes as hyperlinks.
Using Text Boxes for Hyperlinks:
A hyperlink doesn't have to be in a cell. Sometimes a better solution is to use Text Boxes for hyperlinks:
1)
Insert a Text Box;
2) Add the text you want for your link;
3) Right-click the text box and choose Size and Properties to change its Print property so it doesn't print when you print your worksheet;
4) Right-click on the text box, click Hyperlink and follow the same steps as above to create hyperlinks.
2) Add the text you want for your link;
3) Right-click the text box and choose Size and Properties to change its Print property so it doesn't print when you print your worksheet;
4) Right-click on the text box, click Hyperlink and follow the same steps as above to create hyperlinks.
You can move, copy, resize and format the text boxes however you want. You can set the background of text boxes to no Border and no Fill so that just the text is visible.
Here are some other ideas to help you make hyperlinks more useful:
- A hyperlink can be linked to any document or folder on your computer or a network. From the Insert Hyperlink dialog, choose 'Existing File or Web Page' and browse to the file or folder.
- A hyperlink can be linked to any Web Page. From the Insert Hyperlink dialog, choose 'Existing File or Web Page' and either type the web page address into the Address field or click the 'Browse the Web' button.
- A hyperlink can be used to link to open a new email message with the email address and subject already filled in. From the Insert Hyperlink dialog, choose 'Email Address' and type the email address and subject.
- Click the 'Screen Tip..' button on the top of the Insert Hyperlink dialog to add text that will display when a user hovers their mouse over the link.
- You can remove the underline from a hyperlink by pressing CTRL+U.
- You can change the font color and size of a hyperlink the same as any other cell in Excel.
- Right click a hyperlink to edit or remove it.@You can use almost any object or picture as a hyperlink.
- If a workbook has defined range names, they will be listed in the document map when you choose 'Place in this document' from the Insert Hyperlink dialog.
- In Excel 2007-2013, only the text within a cell acts as a hyperlink. In earlier versions of Excel, the whole cell is the hyperlink. To select a cell with a hyperlink, click in the blank area of the cell.
If you found this tip helpful, please share it with your friends and colleagues.
To get more tips every week like this one...
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
Plus you also get my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.