ALL YOU Need to Know About Excel HYPERLINKS (Function & Feature)

Let’s take a look at how we
can use hyperlinks in Excel so we can quickly jump to
different places in our file. This is especially good for
the bigger files you work with. Now we’re also going to take a look at how you can quickly
create a back button to your starter index sheet from all the other tabs in one go. Now we’re also going to take a look at creating friendly
hyperlinks using formulas, and how you can change the
color of the hyperlinks in case that color bothers you. (rhythmic music) To add a hyperlink in an Excel cell, all you have to do is type in the link, so let’s say I want to add a
link to a specific blog post. Press Enter, and Excel is going
to recognize it as a hyperlink. So I’m able to click on it and it’s going to take
me to this blog post. Now sometimes you might not
want to show the entire link. You might want to show a friendly
name or add a screen tip. You can edit this hyperlink. So just right-mouse click,
go to Edit Hyperlink. So let’s call this Excel Sort, and add a screen tip, Click to Learn More, click on OK, and OK. So now when I hover
over with my mouse here, it says “Click to learn
more” and I click it. It takes me to the blog post. (graphic whooshes) So let’s go and insert a shape
that looks like a button. Let’s go with this one. Just draw it out, adjust the color and the formatting as you like. Right-mouse click, Edit Text,
then click on the object, right-mouse click, and go to Link. You can also do this from the Insert tab, and select the link
from here, Insert Link. Now you have different options here. You don’t necessarily
need to add a web address. You can also add a link
to an existing file. So you just have to browse
here and select a file that you need, or you can add a link to a place in this document. So I have different tabs in this workbook. Let’s say I want to add a link
to the Transpose worksheet. I don’t want to jump to
the default, which is A1, but I want to jump to
A10, and click on OK. When I click this button, I jump to A10 of my Transpose sheet. In addition to these options,
if I go in Edit link, notice that we also have the opportunity to add a link for email. So we could type in the
person’s email address. Let’s say this should be
an email to the help desk. You can add the email address
of the help desk here, put in the subject, and click on OK. And when the user clicks this, it’s going to open their
email app and create an email to this address with this subject line. But if you want to remove the
link that you’ve just created, you can also click on Remove Link, and this just becomes a normal shape. So in addition to using these
shapes, you can attach a link to any object, any picture, or any icon. So let’s go with this icon here. Just adjust it slightly,
select another color, right-mouse click, go to
Link, create a hyperlink to the Report tab of this document. So when I click this
icon, it jumps directly to the report tab to the
default cell A1 of my workbook. (graphic whooshes) So let’s say I wanted
to add a link from E1 from all these tabs back to the Start tab. Now first off, we have to make sure that E1 is empty so we’re not overwriting
any existing data in there. In this case, it’s all empty. Now if we select all these shapes, if I hold down the Ctrl key
and click on these tabs, if I right-mouse click
here, I can not add a link. So how could we quickly add a link back to the Start tab without doing it for each single sheet separately? Here’s what you need to do. Let me just deselect and just go back to one of these sheets. Now I’m going to type in Start, so I’m just doing it for one single sheet. Right-mouse click, select Link, Start, and I’ll just go with the
default A1, click on OK. So now I have a link
added to just one sheet. So here’s what you need to do. Copy cell E1, so the cell
that has the hyperlink. Now multi-select all the other sheets by holding down the Ctrl key,
go to E1, and paste, Ctrl + V. Now let me just deselect
these, go to my report sheet, click on Start and it
takes me to the start page. Okay, so the trick is
just do it for one page and then copy that cell
to all your other sheets. (graphic whooshes) Now you might not want
to use the default colors that Excel applies to your hyperlinks. You might want to change that. You can do that by changing the
style that’s available here. So the default style is this
blue color for a hyperlink, and once it’s followed,
you get this other color. To change this, just right-mouse
click and select Modify. So this is for the followed link. I’m going to go to
Format, go to Font color, and I’m going to change it
to, let’s say a yellow color, and click on OK, and OK. So now once I click this, it
changes to that yellow color. And you can do the same for
the existing hyperlink color so you can modify it, change the font under Formats right here. (graphic whooshes) You have a list of hyperlinks here. You want their friendly
name to be the names that we see in column A. Now, you could of course do this manually, right-mouse click, go to Edit Hyperlink, and type in this text as the name. But there’s also an Excel formula that does this for you automatically, and it’s called the hyperlink formula. What you need is to
provide the link location which is sitting in column B right here, and then the friendly name, which is sitting in the A column. Close bracket, press Enter,
and pull this all the way down. So notice these are all external links. This one is a link that’s
inside this document. So when I click on it, I
jump to the Transpose sheet. When I click on these other ones, it takes me directly to the blog posts. Now if you don’t want to show
the original data set here, you can just highlight
them and Hide the columns. (graphic whooshes) Let’s create a drop-down for these topics. When the user selects a topic, we want them to get a link back. And that link should be clickable. So first off, let’s set
up our data validation. Go the Data tab, Data
Validation, select List. For the source, I’m just
going to highlight these here and click on OK. Whenever we select something from here, we want to get the link back here. So let’s just use vlookup in this case. Now if you’re not familiar with vlookup, I have a video on this and
I’m going to put the link to the description of the video and also to the course provided. My lookup value is this one right here. The table array is this right here. Column index, I want
the second column back, so that’s number 2, and I’m
looking for an exact match, so I’m going to go with FALSE. Close bracket, press
Enter, that’s my link. To make this clickable, I can
use the hyperlink function. So I’m just going to wrap the results of my vlookup in the hyperlink function. So link location is my vlookup
result and a friendly name. I can give it this name
here, or it’s optional. I don’t have to provide anything. I’m just going to close
bracket and press Enter. Now that link becomes clickable. These are the different
ways you can use hyperlinks in your Excel files. Now you can also create a table of contents really easily with VBA. This is something I covered
inside my Excel VBA course. If you want to learn how
to do that from scratch, and you want to improve your
Excel skills and learn VBA, check out the course. Link is above and below. So that was today’s video on
working with Excel hyperlinks. If you liked it, give it a thumbs-up. And if you want to learn more, discover some new Excel tips and tricks, consider subscribing to this channel. It will be great to have
you in our community. (upbeat music)

100 thoughts on “ALL YOU Need to Know About Excel HYPERLINKS (Function & Feature)

  1. Leila, Although I knew that I have good experience with links your videos are still adding a lot to my Excel knowledge
    Congratulations for this video, your presentation style still the best I've ever seen
    Waiting for the next video 🙂

  2. Love the work around for simultaneously inserting a 'Home' link on every page – create once and copy to multiple selected sheets. Simple and effective 👍

  3. Great video as always. At time index 2:19 you discuss creating an email using the hyperlink . Can I use a cell reference for the subject? I've tried several formats without success. Thank you in advance!

  4. This is so practical! Thank you!
    ❔❔❔ Is there a way to code email addresses in a cell so that when you click on them they open in Outlook? That used to happen automatically, but doesn't in the newer version of Excel that I have.💖

  5. Leila, please keep doing what you’re doing! Really appreciate the knowledge you share here – always showing us practical usages of excel features.

    Can you do a typical use case video for Power Query’s append function? I use this often, but would like to know other practical uses and maybe advanced tips/tricks. 😄

  6. Hi Laila, I have tried but its not working on folders. PL guide.. I want to open diff folders as per my list.

  7. Thanks for the video! Is there a way to unnamed a list of hyperlinked cells that you have named (like you’ve typed “click here” for an entire list if web addresses) so that you end up with a list of those addresses rather than a list of names?

  8. Great tips. Than you so much! At time index 3:08, you show interesting chart type, do you have a video on how to creat this type of chart? Thanks in advance!

  9. As always another great video.

    Really appreciate that you take time out of your life to share these amazing gems of knowlege with us.

    Love it Leila 🙂

  10. Can you please make a video on excel security and password…. Including all about format editing and everything

  11. This time a roller coaster on hyperlink….truly awesome for those even to those aware of hyperlinks utilities

  12. 7:51 "Link is above…" Like spreading star dust very very cute .Very usefull the hole video but that was my favorite moment Thank for for all you are doing for us!!

  13. I find hyperlinks more annoying than useful most of the time, especially when typing in email addresses.

    To stop Excel automatically converting emails addresses (and URLs), head to File > Options > Proofing > AutoCorrect Options… > AutoFormat As You Type and untick "Internet and network paths with hyperlinks".

  14. Mam you are excel Queen, what a surprise you give in every video, it's very interesting and valuable. Love you…

  15. Hi Leila.. great tips on using hyperlinks, the hyperlink formula, drop-down hyperlinks, the back button trick.. super stuff! Thanks for sharing your know how and creativity. Thumbs up!

  16. Thanks Leila very informative. I use Hyerlinks to link to external files on my hard drive from text in a cell. I would like to use a similar function to your description using Hyperlink and Vlookup but it doesn't seem to work with file linking, no problems with the URL. Any ideas ? Thanks again

  17. So I have hosted training classes at work for co-workers to learn Excel. I think I did alright, though I am not very inexperienced with teaching and I have resorted to developing interactive workbooks to help them develop themselves when they have the opportunity/free time,. In one of my documents, I have links to several sites that provide examples & explanations for every type of formula, but I feel that your videos are far more effective at explaining how each function works. So I will be including links to your videos in my files. I just want to say thank you, because even as my employer sees me as a pro with Excel, you have proven that I still have so much more to learn. Keep up the great work and I will do what I can to support/promote your channel.

  18. Hey great videos! Is there a way to add a link to the next and previous sheet on every sheet automatically? (So on e.g. sheet 4 you would have a link to sheet 3 and 5, and so on)

  19. Built a workbook were I used VBA to hyperlink to a sheet then automatically hide the sheet when I hyperlinked to a another sheet. Great way to remove all the sheets at the bottom

  20. Leila

    Thanks for your information. I made a dropdown list with Data Validation but the hyperlink is gone when it come to the dropdown list.

    Is there any way we can get the droplist clickable without VBA?!


  21. I've seen an Excel file of someone having something like an analogue speedometer (seemed like a chart type;) where the pointer shifted when a value changed. How can we achieve that? 🤔

  22. Dear I have 200+ sheet in one work book and operating via vlookup can we create user form and entry post auto related sheet, kindly support

  23. Great Video. Thanks Leila. I have a question about the date, in excel, it will only let you enter the date with back slash but if you key in number without back slash, it will convert it to different date.
    In access, it will let you either enter numbers with or without black slash and it will convert to date format.
    how to custom the cell of excel that if I key in 121519 with or without back slash, it will automatically convert to 12/15/19.
    Thanks in advance.

  24. I have a question, I have many ranges in a form. i want to color the cell to show that are required. if the cell has data, no color but if the cell is blank, color the cell. Thanks.

  25. Your Excel video are giving great learning.

    I need to know is there any way in excel that we could change the name format like, if in excel its written as Walsh, Whitney Rodriguez and i need it in correct format Whitney Rodriguez Walsh then how i could do or if i want the Walsh in one cell and first name initial in another cell (W of Whitney) how could i do it simultaneously ?

  26. Is there any way to make this dependent drop-down work to jump to any page in the workbook? It appears to say the link isn't valid when I try even is the links work in my lookup range. Thanks all 🙂

  27. it works when it will put the date without stash but when I key in the date with stashes, it will give me different date. for example, when I enter 121219, it gives me 12/12/19 but when I key in 12/12/19, it gives me 04/38/11.
    is there any way to custom the date using VB coding? Thanks in advance.

  28. Hi, mam i want to fetch data from sheet2 to sheet1 by clicking on hyperlink.My hyperlink is in sheet1. data in,please can you guide me for this. it will be major help for me. Thank you

  29. Hi Leila

    Hope you are doing good.

    Can you please help on the below issue.

    How to select multiple ranges by using hyper link in a shape in excel?

    for eg:

    I have to select range(a1:c5) and range(x1:ab5) by one click on the shape.

  30. Hi Leila, I've been trying ways to paste data to filtered range without pasting to the hidden rows/cells but I couldn't find any. The only thing I can find from internet is how to copy visible cells only.
    I'm a sub for about a year now,
    I've watched almost all of your vids and I haven't stumbled that thing yet. Hope you can make a video about it. ♥️

  31. One thing to add: If you have a list of URLs and use the hyperlink function to create the hyperlinks, then there is no way (that I know of) to get rid of the functions and keep the functionality of the hyperlinks using only Excel. You have to copy the cells with the functions to Word first and then back to Excel.

  32. I love the e-mail function and the fact to can add a standard subject. Is there a way to add standard text to the e-mail?

  33. I have learned so many things from your always informative videos. I am hoping you can teach me how to automatically create hyperlinks from one sheet to newly created sheets?

Leave a Reply

Your email address will not be published. Required fields are marked *