If you work in large Excel files, Word documents or even e-mail, you may find it takes a long time to navigate and find the location you’re after.
Hyperlinks are like a time machine in that they can take you there in an instant (but unfortunately they can’t take you back in time), and the good news is they are dead easy to insert.
The few seconds it takes to set up a hyperlink can save hours of time over the life of the document.
By the way, that's a picture of Dr Who's Time Tardis if you didn't know 🙂
What is a Hyperlink?
Hyperlinks are everywhere these days but you’re probably most familiar seeing them on web pages.
They’re typically identified by underlined blue text. When you click on a hyperlink it takes you to a new location, which could be in the same document/workbook or it could take you to a web page, open a file or start a program.
You might also recognise a hyperlink when your mouse pointer changes to a hand when you hover over a picture. This indicates there is a hyperlink attached.
How to Insert a Hyperlink
- Select the cell containing the text or select the object (Shape or Picture)
- CTRL+K to open the Hyperlink dialog box or go to the Insert tab of the ribbon and select Hyperlink.
- You can now choose the location that you want to link to from:
- An existing file or web page
- A place in this document
- Create a new document
- An e-mail address
Hyperlinks to Existing Files or Web Pages
- To link to an existing file simply browse to the file location and select it from the list.
- To link to a Web Page either type or paste the URL in the address box.
Tip: Use this in emails to send files to colleagues as links rather than emailing large attachments. You'll find the Hyperlink tool on the Insert tab of the Ribbon, or remember you can use CTRL+K to open the hyperlink dialog box.
Note: Your colleague needs to have access to the file location for this to work. If they are not in your office then it probably won’t work.
Add a ScreenTip. A screen tip is some text that appears when you hover over the hyperlink. It can give further instructions to the user like in the example below. ScreenTips are optional.
Hyperlinks to a Place in This Document
- Type in the text to display box – this is the text that will appear in the cell that your Hyperlink is attached to. You can either type the text in the cell before you press CTRL+K to insert the hyperlink, or you can type it in the Text to display box as seen in the image above.
- Type in the cell reference you want to land in when you click on the hyperlink
- Choose the worksheet you want to link to.
- Add a screen tip.
Hyperlinks to a New Document
- Insert the text to display if you haven’t done so already
- Give your document a name
- Change the file location of the new document if required
- Choose editing options
Hyperlinks to an E-mail Address
When you click on an e-mail address hyperlink you may be prompted to point Excel, Word etc. to your email client. I use Microsoft Outlook so for me it automatically opens a new e-mail window and I can begin typing my email straight away.
To create an e-mail hyperlink:
- Choose Link to: e-mail address
- Enter the Text to display if you haven’t already typed it in. This can be the email address itself or it could be something like ‘click here to email me’.
- Enter the email address or select it from the list of Recently used e-mail addresses if you have any.
- Enter a subject. This pre-populates your new email with the subject automatically.
Note: When you type an email address into a cell Excel/Word/Outlook recognises the format and automatically sets it up with a hyperlink. But it doesn’t populate the Subject field. You can edit the hyperlink and put a subject field in by selecting the cell containing the email address and pressing CTRL+K to open the Insert Hyperlink dialog box.
Editing and Deleting Hyperlinks
To edit or delete a hyperlink right click on the cell or object containing the hyperlink and select from the menu:
Alternatively when you delete the contents of the cell or the object the hyperlink goes with it.
To delete ALL hyperlinks in a Word document or an Outlook e-mail press CTRL+A to select the entire document/e-mail > press CTRL+SHIFT+F9.
Note Doing this will convert all fields to plain text, not just hyperlinks.
To delete ALL hyperlinks in an Excel worksheet select the cells containing the hyperlinks > right-click > remove hyperlinks
Tip: You can change the formatting of a hyperlink from the default blue underline just as you would format any other text.
My Favourite Uses for Hyperlinks
- Attach them to shapes that look like buttons for a more professional look
- Insert them into emails instead of large attachments so my email gets delivered easily and I don’t end up with huge attachments filling up my e-mail file
- In large workbooks and documents to make it quick to jump from one location to another
- In files to link to supporting workbooks or documents
How do you use Hyperlinks? Share your ideas in the comments below.
Gugu Phiri says
Halo, I am trying to create an excel document and email containing hyperlinks.
The receiver is unable to open the document, is htere a speacial way that I can use to save the hyperlinks with the relevant information linked to them than creating a number of document with many sheets?
Philip Treacy says
Hi,
I’m not sure what he issue is. You said the recipient can’t open the document you send them?
Or can they open it but the hyperlinks don’t work?
If you are trying to create a workbook with links to files then have a look at these blog posts which do just that.
https://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-subfolders
https://www.myonlinetraininghub.com/create-hyperlinked-list-of-files-in-a-folder-using-vba
If the files being linked to are in a central location accessible to all (like a shared network drive), then anybody receiving the workbook with links will be able to open them.
Cheers
Phil
Sylvia says
creating a email hyperlink, although it does create an email for me, I would like attachments already in that email. How do I add attachments to my email without having to click on add attachment(s).
Hopefully this makes sense.
Sylvia
Philip Treacy says
Hi Sylvia,
There is no way to add attachments using mailto:, it’s just not supported.
You can do what you want with VBA though. Have a look at this to see if it suits your needs
https://www.myonlinetraininghub.com/vba-to-create-pdf-from-excel-worksheet-then-email-it-with-outlook
or if something modified from that would be better ?
Regards
Phil
narenderkumar says
send the excel tip and tricks
Mynda Treacy says
You can sign up for the tips and tricks here.
Michael Armstrong says
I copied a table from a website; one column contained hyperlinks. Now I can’t get rid of them using normal methods. Using a VBA Immediate routine got rid of the links, but I now can’t get rid of the little icons that used to identify the hyperlinks. If you want to get your very own cell to illustrate the problem, go to http://www.cocorahs.org/, click on View Data, then Daily Precipitation Reports. I’m not sure exactly which table you’ll get without selecting a state, etc., but one column will be marked “View”. Clicking on the little magnifying glass will link to a detailed view of the data for that table row. If you copy that cell to an Excel cell, you’ll get the icon and the hyperlink. I can’t get rid of the icon to save me. My workaround is to copy the entire sheet, except for the errant cell, into an intermediate sheet, and delete the original sheet entirely. I hope you can tell me an easier way; thanks.
Philip Treacy says
Hi Michael,
there’s a few ways to tackle this.
1. Use this VBA to delete all shapes on the sheet :
Sub DeleteShapes() ActiveSheet.Shapes.SelectAll Selection.Delete End Sub2. When you paste the data, in 2010, right click in Excel, and from Paste Options choose use Match Destination Formatting
3. In 2007, Paste Special -> Text
4. Use Go To, Ctrl+G -> Special -> Objects -> OK, then delete
Regards
Phil
Michael Armstrong says
Thanks, Phil; worked great.
I’m a rank beginner at VBAing, so I used the Immediate pane:
ActiveSheet.Shapes.SelectAll:Selection.Delete
to get rid of the existing garbage, and will use Way (2) to avoid the problem in the future. This also solved other problems with Date and Time formatting.
Philip Treacy says
No worries. Glad to help
Candy says
I have a ton of tracking to do, so I would like to set up hyperlink in my excel spreadsheet to hyperlink my tracking number to the correct shipping website, Fedex or UPS or Averitt, I can get it to go to the page, but how would I get the individual cell to actually be tracked so that when I click on it, it brings up the individual tracking page of where that exact shipment is in their system, i.e., delivered or in transit based on the actual shipment.
Any suggestions are so appreciated.
Mynda Treacy says
Hi Candy,
You can use the HYPERLINK Function to build a link dependent on your data. I’m not clear on the format or your data so I can’t give you a specific solution, but you might find this HYPERLINK Function tutorial helpful.
Kind regards,
Mynda.
gagan sharma says
Hi there
You have some idea about how to use the object function in Insert field in Excel & powerpoint ???
Mynda Treacy says
Hi Gagan,
No, sorry. I haven’t written any tutorials on that yet, but I found this by Microsoft.
I hope that helps.
Kind regards,
Mynda.
Don Miller says
I am looking for a way to attach the hyperlinked file I have craeted in excel and e-mail it to someone that does not have access to my document files. Is there a way to “piggy-back” the hyperlinked file when saving?
Mynda Treacy says
Hi Don,
I’m sorry, I don’t know of a way to automatically attach files in your email that are linked to via hyperlinks.
Sorry, I’m not more help.
Kind regards,
Mynda.
Ken says
Thank you that is some good information. The information on VBA to list the Tabs is great, is there a ways that that routine could be expanded to add the Hyperlink to the TAB next to the name of the TAB.
Mynda Treacy says
Hi Ken,
You could do this with the HYPERLINK function. e.g. in the column beside the tab names enter this formula:
=HYPERLINK(“[your_file_name.xlsx]'”&A2&”‘!A1”,A2)
Where A2 is the cell containing the sheet tab name.
I hope that helps.
Kind regards,
Mynda.
Albert Cato says
Definitely interested in learning more.
Mynda Treacy says
Thanks Albert. You can sign up for our free training and I’ll email you an Excel tip once a week too. Click here to sign up.