When should you use the Insert Hyperlink compared to the =HYPERLINK() function? In Episode #1363, Bill compares the two methods. Also learn how to build 100's of hyperlinks by concatenating together some text in quotes with values in a column.
Transcript of the video:
Learn Excel from MrExcel podcast episode 1363: Insert Hyperlink vs. =HYPERLINK() Hey welcome back to the MrExcel netcast, I'm Bill Jelen from MrExcel.com talking about hyperlinks.
One of the questions I get when we're talking about hyperlinks is, “what's the difference between Insert Hyperlink, the menu feature versus the =HYPERLINK() function?
When should you use one and when should you use the other?
Okay well, let's just type here ww.mrexcel.com press and ENTER.
You see that a hyperlink is automatically inserted there and really that's just a shortcut of using the Insert Hyperlink, all right.
So let me select this text and we'll go to Hyperlink and it says all right well where do you want to go?
Well I want to go to any existing file or web page.
Where am I going?
Mr. excel.com, click Ok and you see there's lots of different options here with Insert Hyperlink I could create a hyperlink to a place in this document or something like that.
Now, like for example if you were going to create a little menu system and you wanted to allow people to jump to a specific section of the workbook we could do that with Insert Hyperlink.
So I want to go to a place in this document and where do I want to go?
I want to go to Report1 and cell A1 is fine.
I just want to get into the sheet but if I wanted him to go to like for example C5000 well we could do it that way.
Click OK.
There's only 5 sections of this report so it wouldn't take me that long to go through and do the 5 sections.
In fact, here let's just do it, less than 30 seconds I guess, once I'm kind of in the groove here—and Report5, click OK and there we go.
Now I have a nice little clickable menu to use and so the Insert Hyperlink, very nice little system for adding hyperlinks, it’s really easy to add navigation but what happens when you don't just have five things to do but you have hundreds of things to do?
Well that's really where the hyperlink function will work.
So I want to create hyperlinks to all of these articles and for each article I have the article description, I have the domain where it's running and notice it doesn't have the http or www or .com and then the article ID.
All right, so here's what we're going to do, we’re going to come out here to the right hand side and say =HYPERLINK( and first thing it wants to know is the link location so I'm going to build this using something in Excel called concatenation.
What a big word.
I just call it joining text, the important thing you have to know is the ampersand.
Shift + 7 on a US keyboard will let you join different pieces of text together.
So the first thing to do in quotes http colon backslash, actually slash, slash www. close the quotes ampersand, whatever's over here in the domain then another ampersand in quotes .com, slash and I'll just make up something like articleID equals ampersand that number ampersand dot aspx.
The formula so far should look like this, =HYPERLINK(http://www.&B4&”.com/ArticleID=”&C4&”.aspx” All right you're going to have to get the exact URL but you get the sense here that using these bits and some text in quotes you can build the complete hyperlink.
All right, so we're done now with the link location, I'll type the column-- and then they want to know the [ friendly_name ].
Well hey the [ friendly_name ] is the description over here in column D. So I just click on D4 closing parenthesis and I now have a hyperlink that works.
The completed formula is: =HYPERLINK(http://www.&B4&”.com/ArticleID=”&C4&”.aspx”,D4) Now check this out, one of my favorite tricks, we're going go to that fill handle, this tiny square dot in the lower right hand corner and double click it.
That just added hundreds of hyperlinks a lot faster than going through and using the Insert Hyperlink function right, just absolutely an amazing way to go.
Now I really don't need all that stuff over in B, C and D anymore so let's take this whole range, I'll start up here in E4 Ctrl+Shift+Down Arrow then Ctrl+C to copy and then Home tab, select Paste, Paste Special and choose Values.
That gets rid of those formulas and what's left are just the article titles with very nice hyperlinks.
Now your call here, whether you want to hide these columns; hiding the columns would let you come back and get to them later or just delete the columns, either way whatever you think.
All right there is our list of articles.
So I just created hundreds of hyperlinks using the hyperlink function, that for me is the value proposition in the hyperlink function.
Well hey there you have it, the difference from Insert Hyperlink and =HYPERLINK function.
I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
One of the questions I get when we're talking about hyperlinks is, “what's the difference between Insert Hyperlink, the menu feature versus the =HYPERLINK() function?
When should you use one and when should you use the other?
Okay well, let's just type here ww.mrexcel.com press and ENTER.
You see that a hyperlink is automatically inserted there and really that's just a shortcut of using the Insert Hyperlink, all right.
So let me select this text and we'll go to Hyperlink and it says all right well where do you want to go?
Well I want to go to any existing file or web page.
Where am I going?
Mr. excel.com, click Ok and you see there's lots of different options here with Insert Hyperlink I could create a hyperlink to a place in this document or something like that.
Now, like for example if you were going to create a little menu system and you wanted to allow people to jump to a specific section of the workbook we could do that with Insert Hyperlink.
So I want to go to a place in this document and where do I want to go?
I want to go to Report1 and cell A1 is fine.
I just want to get into the sheet but if I wanted him to go to like for example C5000 well we could do it that way.
Click OK.
There's only 5 sections of this report so it wouldn't take me that long to go through and do the 5 sections.
In fact, here let's just do it, less than 30 seconds I guess, once I'm kind of in the groove here—and Report5, click OK and there we go.
Now I have a nice little clickable menu to use and so the Insert Hyperlink, very nice little system for adding hyperlinks, it’s really easy to add navigation but what happens when you don't just have five things to do but you have hundreds of things to do?
Well that's really where the hyperlink function will work.
So I want to create hyperlinks to all of these articles and for each article I have the article description, I have the domain where it's running and notice it doesn't have the http or www or .com and then the article ID.
All right, so here's what we're going to do, we’re going to come out here to the right hand side and say =HYPERLINK( and first thing it wants to know is the link location so I'm going to build this using something in Excel called concatenation.
What a big word.
I just call it joining text, the important thing you have to know is the ampersand.
Shift + 7 on a US keyboard will let you join different pieces of text together.
So the first thing to do in quotes http colon backslash, actually slash, slash www. close the quotes ampersand, whatever's over here in the domain then another ampersand in quotes .com, slash and I'll just make up something like articleID equals ampersand that number ampersand dot aspx.
The formula so far should look like this, =HYPERLINK(http://www.&B4&”.com/ArticleID=”&C4&”.aspx” All right you're going to have to get the exact URL but you get the sense here that using these bits and some text in quotes you can build the complete hyperlink.
All right, so we're done now with the link location, I'll type the column-- and then they want to know the [ friendly_name ].
Well hey the [ friendly_name ] is the description over here in column D. So I just click on D4 closing parenthesis and I now have a hyperlink that works.
The completed formula is: =HYPERLINK(http://www.&B4&”.com/ArticleID=”&C4&”.aspx”,D4) Now check this out, one of my favorite tricks, we're going go to that fill handle, this tiny square dot in the lower right hand corner and double click it.
That just added hundreds of hyperlinks a lot faster than going through and using the Insert Hyperlink function right, just absolutely an amazing way to go.
Now I really don't need all that stuff over in B, C and D anymore so let's take this whole range, I'll start up here in E4 Ctrl+Shift+Down Arrow then Ctrl+C to copy and then Home tab, select Paste, Paste Special and choose Values.
That gets rid of those formulas and what's left are just the article titles with very nice hyperlinks.
Now your call here, whether you want to hide these columns; hiding the columns would let you come back and get to them later or just delete the columns, either way whatever you think.
All right there is our list of articles.
So I just created hundreds of hyperlinks using the hyperlink function, that for me is the value proposition in the hyperlink function.
Well hey there you have it, the difference from Insert Hyperlink and =HYPERLINK function.
I want to thank everyone for stopping by.
We’ll see you next time for another netcast from MrExcel.
