Hi, this is Wayne again with a topic “Using Excel Hyperlinks”.
In this article, i will show you how to use hyperlinks inside of microsoft excel we’ll look at how to do it as well as why it might be a good idea here. You can see a simple outline of the lesson plan for this video. So first, let’s look at hyperlinking within a workbook, so you can see in my workbook here i have my first sheet, which is a dashboard that pulls in information from the rest of the workbook, and if you want to learn how to create your own dashboards in Excel you should watch my excel dashboards videos, but let’s say when i’m done with this dashboard, i decide you know what the viewer might want to see more information than just the highlights more than just the gross sales total number of sales, etc. What if the viewer wants to drill down and look at the data itself? Yes, the viewer could just go down here and click on one of the spreadsheet tabs, but instead i might want to use hyperlinks to help the viewer quickly get to the information they want to see. So i’ve entered some dates and text into my spreadsheet.
To turn this into a hyperlink, all i have to do is click on it and go to the insert tab and there on the insert ribbon in the links group. There’S a link button and i’ll go ahead and click that, and you can see that there are four main options when you create a hyperlink in excel. I want to start with option number two, a place in this document, so i’ll click that and it brings up a list of all of my spreadsheets there’s 2020. I just select it and click.
Ok. So now that is a hyperlink just like you would find on the internet when you click a link on a web page, it takes you to another web page, it’s the same kind of thing so as the viewer. Now, if i click on that cell, it takes me to the 2020 spreadsheet i’m going to go back to the dashboard. With the cell that has 2019 selected, i can just go up and again click the link button and this time i’ll just double click.
On 2019. You don’t have to click and then click. Ok, you can just double click to select it now. What if you don’t want to link just to a specific worksheet, but rather to a place inside that spreadsheet? Well, when you click to add the link, in addition to choosing which worksheet just go here where it says type the cell reference, maybe i want to link to h25. I click ok. So now, when the viewer clicks that button, he or she is taken to h25 on the 2018 spreadsheet jumping back to the dashboard.
I do want you to see that this also works within the same worksheet. So, for example, with business plan i could select. The text. Add the hyperlink but i’ll just keep it on dashboard and i’ll. Just click here and type the cell reference. Let’S say: n500 click! Ok! So now, when this is clicked, it takes me to yes, the same spreadsheet, but down on cell n500, i’m going to quickly jump back up to the top. Now, of course, that really isn’t my business plan. So i’m going to hold ctrl and tap z to remove that hyperlink. Now, at this point, we’ve looked at how to add hyperlinks within a workbook to other sheets and places in the same sheet or in another sheet. I’D also like to show you how to link to defined names, so, for example, if i go to the business plan checklist, i can click and drag to highlight an important part of this workbook and then here in the upper left, i can go to the name Box and remove, what’s there and put in some other name i’ll, just call this good. I tap enter so now. This section of my worksheet, the business plan worksheet, is called good, so, let’s jump back to the dashboard. If i want this button to take me not just to the business plan sheet but to that specific defined name, i just click on the cell go up to link and of course i could select here to link it to the business plan checklist. But look there is a defined name section here, and one of the defined names is the word good, i’ll click that and click ok.
So now, when i click the button, it takes me to that specific part of the spreadsheet it just so happens to be the top. I should have used the bottom as an example, but i think you get the idea. Jumping back to the dashboard.
Give me a minute to add hyperlinks to these other two pieces of text and then i’ll resume the video. Next, let’s look at how to link this workbook to another workbook. Let’S say i have a separate workbook that has more information that is relevant. I could type in some text, click on it and choose link, but this time, instead of linking to a place in this document, i’m going to link to an existing file and in this case i’m going to choose another excel workbook. But it doesn’t have to be another excel workbook i’ll just choose this one here i double-click to select it and now, when anyone clicks that link, it will open up the link to workbook.
If you’re going to do this, you need to make sure that the two workbooks stay together if one of the workbooks is on a different computer or if it’s moved out of its existing folder, that link may not keep working. Another way to use hyperlinks is to link to a website. So once again i can type in some text and then click back on the cell to select it and then go up to link. But this time, instead of placing this document, i am going to pick existing file, but it’s not a file on my computer. It’S an address.
A web address so pulling up a web browser. This is the link i would like it to go to so i’ll click on it to select it ctrl c to copy and then back in excel i’ll click and then do control v to paste click, ok and now the viewer when he or she clicks that Link it’s going to ask: how do you want to open this document or web page in this case and i’ll pick one of the web browsers, and it opens right up to the web page. Finally, i’d like you to think about two other options for using hyperlinks. Instead of always using them with text like i’ve shown here, why not use text boxes or images as hyperlinked buttons, so, for example, here on the insert tab insert ribbon, i can go to the text group and click to insert a text box click and drag to Draw out a text box i’ll name this 2020 and i can click away from the text box and then click back on it. I could center it and then i could change the size of the text box. I could change the font size of the text itself. Whatever i want to do to make this look the way i want it to look and to kind of look like a button actually so now that i’ve done that, i can click on it to select it and again go to insert and link place in this Document 2020 i’ll just double click on it. That now will work as a button when it’s clicked it takes people to the 2020 spreadsheet. Now i may want to create another button on 2020 spreadsheet that says return to dashboard or just dashboard. That would be a nice easy, quick way to get back to the dashboard, in addition to using text boxes as buttons, you can also insert images, so i can go to insert illustrations, pictures and there’s lots of ways. You can get those images i’m going to choose online pictures i’ll just do a search for demographics. That looks good i’ll select, it click insert so now i’ve got an image. I can shrink it down if i’d like, and then i can turn that into a hyperlink.
Very easily, just by selecting the image insert link and i want it to link to competitor demographics, click. Ok and now that is a working button, so hyperlinks in excel can really add. Some nice touches to your spreadsheets can add a little bit of interactivity to them and there are creative ways to use these to create some exciting excel projects. Now you may have noticed that there are two other kinds of hyperlinks.
You can add one that will create a new document. You would just give that document. A name. Click, ok and it creates a link and creates a new document for you to develop.
And then the other option is to link the hyperlink to an email address and doing so will make it so that whoever clicks the link it will open up their dedicated email program if it’s installed on their computer and the beginnings of an email will be prepared. With the address put in perhaps a subject and they’ll just have to type their message, so i hope you see the potential in using hyperlinks in excel thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe and when you do subscribe, click the bell so you’ll be notified. When i post another video, if you want to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below .