Hyperlinks in Excel: Link to Other Documents

excel hyperlinkOften, you will find that you need to link a document to another document.  Whilst this is easy to do if you are sending an e-mail or writing a website, most programs don’t allow external linking, especially to files outside of the programs opening capabilities. Microsoft’s Excel program was built to complement their Word, Powerpoint and Outlook programs, so have the functionality to make some interesting hyperlinks built in to them.

At the end of this article, you’ll know how you can link to other documents, both on your local disk and across the net. This is a fantastic way to save time and reduce the amount of headaches that locating files across intranets for workmates or family can be, relieving you of the stresses of being everyone else’s IT support guy. Excel has a reputation of being confusing, yet incredibly powerful. Whilst it is a widely used program, you’ll find that most users don’t really know how to make the best use of it and consequently, they end up using a very small amount of the program.  Becoming an expert of Excel will let you save time and it isn’t too hard to become a power user. If you are interested becoming a Microsoft Excel Power User, why not try these Excel Tutorials to help you.

Excel is frequently used as a spread sheet program and a fancy calculator. One of the functions that Excel  offers is the ability to link to any other document that you have, opening them up in the correct program. To start using these hyperlinks, it is important for you to make sure that the formula bar is enabled. If your formula bar isn’t visible, you can find the option to enable it in the view options at the top of the application.

Hyperlinks

Hyperlinks are a web standard way of getting from one place to another. Used within Excel though, they are accessed using the formula bar. The syntax that you must follow to get a working hyperlink is as follows.Untitled

=HYPERLINK(link_location, [friendly_name])

Syntax must be strictly followed, else your formula in Excel will not work. The equals sign at the start of the formula depicts the start of a formula and without the = sign, the program will not be able to distinguish between a formula and a static data cell.

To create a hyperlink without using a formula, you can press CTRL + K on Windows and ⌘ + K on an Apple Mac computer to create a simple hyperlink. Whilst this will ‘get the job done’ it doesn’t offer the same level of control that using a formula allows for.

HYPERLINK is the command, with the variables coming in to the equation to tell your computer where to go. Excel allows both files and web addresses, using the standard file system addresses for files and Internet addresses respectively.

 Untitled

  =HYPERLINK(http://www.google.com, [Google])

This example above would take you to Google.com, and display the term Google on the spread sheet, rather than a long formula. Using hyperlinks like this allows you to designate the location of the link and the ‘friendly name’ that the cell displays.

=HYPERLINK(C:\Documents\Finance.doc, [Monthly Finance Reports])

This example would instead link you to a Microsoft Office document, as a Word file and open up in the program you use to look at Word documents, if you have one installed. The text in the square brackets is what is displayed to the user in the spread sheet.

If you are having trouble with the formulas and getting them to do what you want to do, first of all, check your syntax. It’s very important and will not work at all without them. If you still have trouble with these kinds of things, it is worth looking at this Excel 2013 tutorial set as they will teach you all you ever need to know about Microsoft’s Excel program in a quick and easy manner, making you an expert in no time.

Hyperlinks also allow you to make even more accurate jumps in to documents, such as selecting the correct cells in a document. This can be really handy if you are the administrator of a website and need people to enter data in to specific documents on your server. For example,

=HYPERLINK(“[http://yoursitehere.com/reports/billing report.xls]Annual!F10”, C1

This would open a file on the users computer located at Reports/billing report.xls on your server, whilst opening the page to Annual, and selecting the C1 cell in the document. If you need a lot of users to open certain cells so they can send or receive invoices, for example, this is a great way of getting people to enter information in to your system without them messing things up or entering the wrong information in to the cells. Another thing that would make this go easier for everyone would be locking all the other cells in the target documents so that no data can be changed or inputted inside these cells. This way, you end up saving even more time and your customers can only enter data where you allow them.

There are many other ways you can use hyperlinks, which are all inputted in a similar manner to those that I have shown you in this article. Hyperlinks are great to use in Microsoft’s programs as they just work effortlessly and are very easy to implement. Make sure you enter the friendly name variables as some of the formulas can be rather unsightly.

This Excel Dashboard course is a great way to show you how to use hyperlinks in more detail than I can go in to here. It will also show you how you can set up worksheets with certain permissions, lock and unlock cells and designate read only sheets on your Excel spread sheets. Making sure that your employees or customers do not fiddle with your carefully crafted spread sheets has never been easier.