Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - using Hyperlinks in a spreadsheet

Using hyperlinks in a spreadsheet can be a useful way of directing users to other resources. This can be helpful if data that is linked to in is unable to be included in the present spread sheet or is simply better presented elsewhere.

Excel covers two hyperlink options, either a link to a file on the local computer or network (UNC) path, or a hyper-text path to an internet or intranet URL.

Let's look at linking to a file that exits locally or on a mounted drive; First we need to get the full path to the file we need. We can do this by navigating to the file in question and then selecting the drop down to the left of the UNC address bar. This shows us the UNC, C:\Users\Peter\Documents\Excel_Files which we can copy and paste onto our clip-board.

We then proceed to our spreadsheet and construct the following formula; =HYPERLINK("C:\Users\Peter\Documents\Excel_Files","Sales League Table") We can see here that the formula takes the format =HYPERLINK(link _ location,friendly _ name) We can also see that both values require quotation marks and are separated by a comma.

We can see that the first value is the location and the second is a friendly description. If we were to use the formula without the friendly description, the UNC location would be displayed.

Using HYPERLINK to link to internet page follows the same a pattern. In the example below, we have selcted the cell below the UNC link and used CTRL+D to copy that formula. We then simply edit the values to include the http:// headed link to the web page of our choice and then change the name of the link to so that it is relevant.

Our new hyperlink formula is =HYPERLINK("http://www.excel4business.com/contact.php","Excel4Business Training")

Our final sheet can have as many or as few links as required.

For more help with Excel formulas, contact our experts.