Hyperlinks are a useful concept to understand in Excel, they are particularly helpful in large reporting suites or when creating winning Excel dashboards as they can be used as an effective navigational tool. This guide will show you how to make the most of hyperlinks in Excel…
What is a Hyperlink
A hyperlink is a link that they user can click on to take them directly to another piece of information. It can take the user to a whole document, like a help file or a website or it can take the user to a specific element within the document, like a specific cell reference or another worksheet in the Excel file.
Why use Hyperlinks in Excel 2010?
The simple answer is to produce a better experience the users of your Excel report or Dashboard and it allows you to create a more professional Excel report with that “internet site” feel to it because of the hyperlinks.
Some of the main uses of hyperlinks in Excel are:
- Link to help files or FAQ files
- Link to other Excel reports containing more information
- Link to websites (internal and external) containing reference information
- Link to cells or the start of table in large Excel spreadsheets so users don’t have to hunt around
That’s just a few but I expect you can come up with more uses once you start implementing them into your Excel reports.
How to use Hyperlinks in Excel 2010
To start with a hyperlink can be inserted within an Excel spreadsheet by selecting a cell, right-click the mouse and select “Hyperlink” from the bottom of the options box that appears, or for those of you who love shortcut commands you can select your cell and press the CTRL and K keys:
- Existing file or Web Page
- Place in This Document
- Create New Document
- Email Address
Depending on which option is selected the Insert Hyperlink options box will change the information it needs you to enter. The default setting you first see is for Existing file or Web Page.
Existing File or Webpage
Select this option if you want the hyperlink to take the user to another file, like a word help document or another Excel report, or if you want to direct the user to a particular website.
In the Insert Hyperlink options box you need to complete the empty boxes for Text to Display and Address. The Text to display is the text that will be inserted in the cell for the user to see and the Address is the full path of the file or full address of a webpage that you want to open when this hyperlink is clicked by the user.
For example if you want to direct the user to DedicatedExcel.com with the text Click here for website the options box would be completed like the below image shows:
Select this option if you want the hyperlink to direct the user to a cell reference or another worksheet within the Excel file you are using. This is very useful if you have multiple worksheets as you can create a home page style to your reports.
For example, if we wanted to insert a hyperlink which takes the user from a Sales worksheet to the Volumes worksheet we could complete the options box like below:
As with before you complete the Text to display box with your text but this time you have to select a cell to go to and the worksheet you want to direct the user to. This is also a useful way of navigating around 1 large worksheet as you can specify which cell the hyperlink will take the user to and just select the current worksheet.
Create New Document
The create new document hyperlink allows you to use a hyperlink to create a file, you can be quite creative off this option but opening up certain file types to allow a user to edit code etc. but the principles remain similar to the other options:
You need to again specify the text to display first then you can specify the full path of the new document, including the name it needs to be called. Finally you can select whether to open the new file instantly or just store in a location for future use.
Select this option if you want the hyperlink to automatically populate the users email software with an email address. This is great for putting in ‘Contact Me’ style hyperlinks which the user can click on, for example if they want to get hold of more data, put in a request for an amendment or just contact you to discuss some of the finer details of the Excel report. Again it helps achieve that internet site feel to your Excel reports and increases the functionality and usability of the report:
In the Text to Display box you can write your text details like ‘Contact me’ or ‘Email me’ then in the Email Address bar you need to display the email address that you want the hyperlink to direct emails to. Note that this will place the words mailto: before your address, this is necessary for the function to operate correctly so make sure you leave that in place. Finally you can complete a Subject message if you like, this message can be left blank but depending on what the email is related to it can be useful to already pre-fill the box.
Hopefully this has provided you with a good understanding of how to use hyperlinks in Excel and when they can be useful for providing a better experience to the end-user or your clients.
I find them extremely useful within large reporting suites that I create and they are a must to consider when you are creating dashboards in Excel. You can achieve that website feel to dashboards when you implement hyperlinks correctly while maintaining the power and functionality that makes Excel a great business tool.