You can increase productivity and efficiency by using hyperlinks in your Excel documents. Hyperlinks are incredibly easy to setup and even easier to use. The easiest method for inserting a hyperlink is to create a link to a website. Excel recognizes the syntax of ‘www.something.’ When you type a website into a cell, Excel will automatically create the link for you. Try typing a familiar website into a cell in Excel.
For the situations when you do not know the website address, it is quite easy to first navigate to it in the browser, copy the address using the CTRL + C keyboard shortcut, and then paste the website address in an Excel worksheet cell with the CTRL + V shortcut. At this point it is not a hyperlink, it has just been pasted in as text.
To quickly change it to a hyperlink: click out of the cell, then double click on the cell and exit the cell one more time (by clicking ENTER, TAB or mouse click to another location). Excel will then finish recognizing the website address and will automatically create the hyperlink.
Here, you could also use this type of hyperlink to create a website, username and password file for a convenient and efficient solution to maintaining this information, as well as using it for productivity.
Another useful and productive technique of using hyperlinks is to create links to open other applications. For example, in the following worksheet I have created a link to open the Word application.
Next, I will show how to do the same type of hyperlink to open an Excel application. If you know the location of the file that starts the application, you can open the hyperlinks form window (more on how to do this later) and navigate to the file location.
To find the file location to start an application, locate the application on your start menu. If it is not there, you can create a shortcut to put it there. Right click on the icon, and select ‘Open file location’.
Right click on Excel to open the file options menu. Then click on ‘Properties’ to find the target location of the shortcut; this is the same as the application file location.
Copy the Target location with CTL + C
Back in Excel, select the cell where you want to place the hyperlink. Right click and select ‘Hyperlink’ at the bottom of the cell options menu window.
Here is a quick look at setting up a hyperlink. Click in a cell à right click to bring up your options menu. At the very bottom of the menu is the Hyperlink setup tool. Click on it. Then paste the target location of the Excel shortcut (the location of the Excel.exe file.) in the field labeled ‘Address:’ Click OK.
You may have noticed that you can also setup a hyperlink to navigate to another place in your worksheet or workbook. To illustrate the usefulness of this feature, I have created a worksheet titled ‘Workbook Menu’. In this next section we will create a hyperlink to another worksheet.
For this example, a hyperlink will be created in A2 or the cell that has the text ‘Website Addresses’. The hyperlink will navigate to the worksheet named ‘Website Addresses’. Click on the cell A2, then right click to open the cell options menu.
The Insert Hyperlink form opens. This is a fairly straight forward window. On the left are ‘Link to:’ options. You can link to an ‘Existing File or Web Page’, link to another ‘Place in This Document’, create a link that will actually ‘Create New Document’, or create an ‘E-mail Address’ link.
We need to select ‘Place in This Document’, then ‘Website Addresses’, and ‘OK’
The hyperlink formatting is applied and a hyperlink to the worksheet ‘Website Addresses’ was created. Now when you click on cell A2, the worksheet ‘Website Adresses’ will open with A1 as the active cell.
A nice touch is to create a hyperlink back to the ‘Workbook Menu’. For the illustration below, I have typed in a cell the text “Back to Workbook Menu”, and then right clicked to open the cell options menu window. The next step is to select ‘Hyperlink…’
In the Insert Hyperlink window: Select ‘Place in This Document’ and select the ‘Workbook Menu’
A hyperlink was created in cell E1 that will open the worksheet named ‘Workbook Menu’, and the active cell will be A1. To create the same hyperlink on any other worksheet in this workbook: navigate to the cell E1. Do not click on it, as it will navigate you away from this page. Use TAB, ENTER, Go To, arrow keys or the Name Box. Copy the hyperlink. You can use the keyboard short cut CTRL + C, or Right click + Copy or use the copy icon from the Excel Ribbon. Once copied, navigate to the next worksheet.
Select a place to paste. Click in that cell and paste using the keyboard shortcut CTRL + V.
Another feature that can be easily used with the hyperlink formatting is to create a hyperlink to a named range of cells. For this example, I have created a worksheet with a sample Chart of Accounts and created ranges – defined names for ranges on the same worksheet. This feature would be convenient for large worksheets. For this example, I am keeping everything visible on one screen. The named ranges would be visible in the Name Box. For hyperlinking purposes, I have created a ‘menu’ in the first column.
In cell A2, we will create a hyperlink to the named range ‘Property Plant and Equipment’.
Select cell A2, right click to open the cell options window, and then click on ‘Hyperlink…’ to open the ‘Insert Hyperlink’ window.
Select ‘Place in This Document’ and notice that there is a section called ‘Defined Names’. This is a list of the names that I have defined for the Chart of Accounts. With ‘Property_Plant_and_Equipment’ selected, click OK.
A hyperlink is created in A2. When the hyperlink is selected the Named Range is selected.
As you can see from the above examples, there is good reason to start getting hyper about hyperlinks in Excel. This very easy to use tool will increase your productivity, efficiency and make navigating in and out of Excel much more exciting.
Cathy Goetz | Business Software Consultant
Cathy is responsible for supporting clients with their Microsoft Dynamics GP solutions. From the beginning, she helps her clients determine the scope of their project and provides software demonstrations. Her expertise in gathering requirements will produce a carefully planned system for successful implementation and configuration.
Cathy holds a Bachelor of Arts in English and Elementary Education from Jamestown College in Jamestown, ND. Cathy has over 28 years and a wide range of industry experience in Agriculture, Retail Logistics, Construction, Contracting, Education, Finance, Food, Gaming, Real Estate, Property Management, Legal, Government and Nonprofit. Her roles included management positions, database analyst, application developer, software consultant and trainer, business owner, founder, teacher, and senior accountant. She has been involved with several ERP implementation projects, including Dynamics GP. Her specialties include data migration, Microsoft Access application development, data integration, data cleansing, database normalizing, and developing relational databases from legacy systems. She is highly skilled in report writing, documentation, developing training materials, and presentations. Cathy is certified as a Microsoft Certified Master Instructor for Microsoft Office and has recently completed an accounting program at Frederick Community College. She is currently preparing for the CPA examination.