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.
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.
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.
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.
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.
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.
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.