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.
Is there a way to insert the =today() function as part of the subject in an mailto hyperlink? Goal is to have the subject text along with a time stamp
Yes, you can, but you have to use the Hyperlink formula and format it:
=HYPERLINK(“mailto:” & B1 & “?subject=Date: “& Text(Today(),”yyyy-mm-dd”) & “&body=”&”body text here”, “Link name”)
I am trying to insert an e-mail address using the LOOKUP function from a name entered in a field populated from a DATA VALADATION field. Once the name is selected from the data validation, the e-mail string is populated correctly by LOOKUP, but NOT as a hyper link. The source data pulled by the lookup function is formatted as a hyper link, but this format does not carry over with the lookup function. How can this be done?
Hi Bob,
Use the HYPERLINK function, should work.
Cheers,
Catalin
Thank you for your reply, but I still cannot make it work. Allow me to try a better simplified explanation of my task.
Basically, my sheet has a Source tab containing a list of names, e-mail addresses, phone numbers and more of employees. The Main tab will select one of those employees, using data validation, filling with the employees information, and allow the user to enter material items in the Main tab to be ordered for that employee, and then e-mail that employee back with confirmation.
I have a spreadsheet with several tabs. The first tab, I call “Main” is a spreadsheet where the first column is a Data Validation field pointing to an indexed second sheet in the next “Source” tab. This sheet comprised of list of names in column A, and e-mail addresses in column B. There are several more columns which successfully fill the “Main” sheet with more data from the source sheet but they are not my problem. The email addresses in the “Source” tab is properly formatted and if I click on the e-mail field in the Source tab sheet, I open a new email to that address.
My goal is have a user click on the first cell in Main tab, select from one of the names presented and have the subsequent columns in the Main tab fill with data from the Source tab using the Lookup functions. I need to have the user of this sheet to click on the cell in the Main tab containing the found e-mail address, and open a new e-mail to that address. Can this be done?
Hi Bob,
You can use hyperlink to open new emails, the link should be built like this:
=HYPERLINK(“mailto:”&”[email protected]” &”?subject=” & “subject text” & “&body=” & “email body content”,”Friendly link name”)
Replace the static parts with your dynamic formulas, should work.
Eurika! It Works! Thank you, Thank You, THANK YOU!
I have an excel document with email hyperlinks that I’ve been using for some time just fine. I got a new computer and now when I click on the email address hyperlink instead of opening a new email to the person in my Outlook, it tries to create a new email address for me to send from! My outlook email is working just fine, so I don’t know why the hyperlink isn’t opening a new email for me in Outlook. Cutting and pasting the email address into each new email is time consuming.
Hi Trina,
If you edit the hyperlink (right-click > edit link) in the ’email address’ field it should say mailto: before the email address. e.g. mailto:[email protected]
If it doesn’t have mailto: in front of the email address try adding it to see if that fixes the issue.
Mynda
Mynda
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?
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
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
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
send the excel tip and tricks
You can sign up for the tips and tricks here.
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. 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.
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
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.
No worries. Glad to help
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.
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.
Hi there
You have some idea about how to use the object function in Insert field in Excel & powerpoint ???
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.
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?
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.
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.
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.
Definitely interested in learning more.
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.