1 00:00:00,350 --> 00:00:06,450 The advantage of hyperlinks is that you can quickly jump to different locations. These different locations 2 00:00:06,450 --> 00:00:09,580 can be places on the existing sheet. 3 00:00:09,690 --> 00:00:15,140 They can be another tab, another workbook, or a Web site. As a first step, 4 00:00:15,150 --> 00:00:18,310 let's try to create a mini table of contents. 5 00:00:18,400 --> 00:00:24,060 I'm currently on the hyperlink sheet and I want to create a table of contents that allows me to jump 6 00:00:24,060 --> 00:00:26,520 to these three sheets here. 7 00:00:26,520 --> 00:00:33,780 All I have to do is start by typing the name I want to have in the cell. And then I just have to right 8 00:00:33,780 --> 00:00:36,650 mouse click and go to link. 9 00:00:36,660 --> 00:00:43,260 I come to this dialog box. The first option here is existing file or web page. If I wanted to jump 10 00:00:43,260 --> 00:00:46,350 to another file, I can find a file here. 11 00:00:46,740 --> 00:00:50,910 I can search for it in my directory here. If I wanted to jump to a web address, 12 00:00:50,940 --> 00:00:52,880 I just have to type the address here. 13 00:00:52,950 --> 00:00:56,840 In this case we want to jump to a place in this document. 14 00:00:56,880 --> 00:01:03,030 I can see the names of my tabs here and any defined names I have in that workbook. 15 00:01:03,030 --> 00:01:09,510 If you're using a defined name for a range or a cell, you can quickly jump to that range by selecting 16 00:01:09,510 --> 00:01:11,540 the defined name from here. 17 00:01:11,550 --> 00:01:14,970 So for this style one, I want to jump to the style tab. 18 00:01:15,000 --> 00:01:23,340 Default is to jump to A1. If I don't want A1, I want another cell, I can type it in here. And let's add 19 00:01:23,430 --> 00:01:25,860 a screen tip as well. 20 00:01:26,280 --> 00:01:28,720 Click on ok and ok. 21 00:01:28,800 --> 00:01:35,100 Now the moment you hover over this you see that screen tip. When I click on it, it jumps to the style sheet 22 00:01:35,100 --> 00:01:40,830 to cell A3 because I change the default from A1 to A3. 23 00:01:40,920 --> 00:01:43,770 We can also add more information here. 24 00:01:43,890 --> 00:01:49,020 Let's add a link to the second and the third tabs. This time for the second tab, 25 00:01:49,020 --> 00:01:50,870 let's add it another way. 26 00:01:50,880 --> 00:01:56,670 Let's go to the Insert tab, go to link here, and click on insert link. 27 00:01:56,700 --> 00:01:58,260 I'm going to go with theme. 28 00:01:58,260 --> 00:02:01,710 I'll keep it to A1 and I'm not going to add a screen tip. 29 00:02:02,430 --> 00:02:03,750 Let's add the third one 30 00:02:06,500 --> 00:02:07,800 and click on ok. 31 00:02:07,940 --> 00:02:13,520 Let's also add more information about these. Now every time we click these, 32 00:02:13,520 --> 00:02:16,580 we jump to that tab. 33 00:02:16,650 --> 00:02:23,200 So how about creating a back button to our table of contents from each of these tabs. 34 00:02:23,400 --> 00:02:27,710 And let's put that back link to cell F1. 35 00:02:27,720 --> 00:02:33,930 Well first of all I have to make sure that cell F1 is empty on all these 3 tabs. And I've already checked 36 00:02:33,930 --> 00:02:35,760 that, it's empty. 37 00:02:35,760 --> 00:02:42,360 Next step, I'm going to highlight these three tabs, right mouse click and go to link. But I can't add a 38 00:02:42,360 --> 00:02:42,990 link. 39 00:02:43,020 --> 00:02:50,100 It's grayed out. You can't add hyperlinks at the same time to many sheets. You need to use a work around 40 00:02:50,100 --> 00:02:55,430 for this if you want to do it at the same time. Because you could do it one at a time 41 00:02:55,440 --> 00:03:01,380 but if you have a lot of tabs it's going to take a lot of time. So the way you need to do this is to 42 00:03:01,380 --> 00:03:04,830 go to one of the tabs and create your link. 43 00:03:04,830 --> 00:03:11,520 I'm going to call this "TOC" for table of contents, right mouse click, go to link and let's jump 44 00:03:11,520 --> 00:03:16,230 back to hyperlinks to cell A3 and click on ok. 45 00:03:16,280 --> 00:03:24,290 Now, these other tabs they don't have "TOC" but F1 has to be empty so let's just add some more 46 00:03:24,300 --> 00:03:25,500 space here 47 00:03:25,500 --> 00:03:27,530 and this one, F1 is empty as well. 48 00:03:27,960 --> 00:03:29,900 This is what you need to do. 49 00:03:30,030 --> 00:03:31,540 You need to go to the tab 50 00:03:31,590 --> 00:03:38,670 you did the first hyperlink in and copy it. Control+C. Now highlight all the other tabs. 51 00:03:38,760 --> 00:03:44,430 If it's pretty much every sheet on your workbook, you can right mouse click and select all sheets and 52 00:03:44,430 --> 00:03:48,770 then deselect the ones you don't need by holding down the control key. 53 00:03:48,780 --> 00:03:55,070 Then go to cell F1 where you want to have your hyperlink in and press control+V. 54 00:03:55,170 --> 00:04:00,130 This automatically will put that back link to all your tabs. 55 00:04:00,270 --> 00:04:04,350 Now when I click this, it jumps directly to my hyperlinks tab. 56 00:04:04,710 --> 00:04:08,130 Okay so you can add hyperlinks to cells. 57 00:04:08,130 --> 00:04:12,540 We saw that you can link to other files, you can link to a web page. 58 00:04:12,690 --> 00:04:17,350 But now how about adding hyperlinks to an icon or to a shape? 59 00:04:18,700 --> 00:04:26,990 In Excel you can add hyperlinks to any object in your workbook. So let's go with insert icon and select 60 00:04:27,140 --> 00:04:29,360 an icon from this list. 61 00:04:29,630 --> 00:04:33,970 Click on insert, adjust the icon the way you like. 62 00:04:35,180 --> 00:04:40,950 And then all you have to do is right mouse click, select link and pick where you want to link this to. 63 00:04:41,250 --> 00:04:47,030 So in this example I'll go place in this document and I'll go with sample report. 64 00:04:47,060 --> 00:04:53,510 When I hover over this, it's clickable and it takes me directly to my sample report tab. 65 00:04:53,520 --> 00:04:56,970 Now what about linking to a Web site? 66 00:04:56,970 --> 00:05:04,620 Well, the moment you start typing in a web address directly in a cell and you press enter, Excel recognizes 67 00:05:04,620 --> 00:05:10,740 that it's a link. You don't have to manually add the link. But if you wanted to have a friendly name 68 00:05:10,740 --> 00:05:14,310 for this, you can go and edit this hyperlink. 69 00:05:14,370 --> 00:05:16,790 Just right mouse click, edit hyperlink. 70 00:05:16,830 --> 00:05:19,790 The address is taken from what was in the cell. 71 00:05:19,800 --> 00:05:25,900 Now I'm just gonna change the text to say "Visit site" and then click on ok. 72 00:05:25,960 --> 00:05:31,950 Now when you click this, your default browser is going to open and it's going to take you directly 73 00:05:31,950 --> 00:05:33,160 to that Web site. 74 00:05:33,180 --> 00:05:36,310 You can also insert a shape. 75 00:05:36,420 --> 00:05:38,150 So let's go with this shape. 76 00:05:38,190 --> 00:05:39,330 Just draw it out. 77 00:05:40,410 --> 00:05:50,310 Adjust it as you like, add text to this and add a hyperlink. Just right mouse click, select link and type in 78 00:05:50,730 --> 00:05:54,110 the web address directly in here and then click on ok. 79 00:05:54,110 --> 00:05:57,940 Now this becomes a clickable button. 80 00:05:58,050 --> 00:06:06,840 You can also add hyperlinks to create email addresses. So let's say we want the user to be able to click 81 00:06:06,840 --> 00:06:12,070 this and get their email to open with the correct email address. 82 00:06:12,120 --> 00:06:18,270 We just have to right mouse click, go to link, go to email address. 83 00:06:18,300 --> 00:06:24,090 If you've used any recent email addresses, you can see it here. So you can select it directly from 84 00:06:24,090 --> 00:06:24,760 this list. 85 00:06:24,780 --> 00:06:34,020 Otherwise, you just have to type in the email address and you can add a subject line and then click on 86 00:06:34,030 --> 00:06:34,790 OK. 87 00:06:34,870 --> 00:06:42,060 So now every time we click this, our email opens with the email address and the subject line. 88 00:06:42,060 --> 00:06:47,270 Okay so these are the different ways you can use hyperlinks in your files.