Hyperlinks

Quick reference

• 00:04 The reason I've covered range names there is they're useful for
• 00:08 the next thing I want to talk about as well, hyperlinks.
• 00:12 Hyperlinks are just like they are on a website.
• 00:17 You click on them and they take you somewhere else.
• 00:20 They don't have to necessarily take you somewhere else in the model,
• 00:24 they can take you to another workbook.
• 00:25 Create a new workbook, or even set up an email.
• 00:28 Very, very simple.
• 00:30 Let's take a look.
• 00:32 Let's head back to our layout file.
• 00:35 You may recall earlier I put a dummy hyperlink in,
• 00:38 in row three, highlighted presently, the table of contents.
• 00:43 What I've actually done to the template is I've created a new sheet,
• 00:47 here at the front, Table of Contents, nothing particularly exciting.
• 00:50 It's got the same formulas here so that they will work at the dummy table of
• 00:54 contents again, and then this is where I can actually put my table of contents.
• 01:01 I want this sheet to link back to the table of contents.
• 01:05 Now remember what I said before that every
• 01:09 hyperlink should go back to the control home cell.
• 01:12 Here cell A5 on the table of contents.
• 01:16 That's the cell I want to go to.
• 01:19 Now there is a function in Excel that will do this.
• 01:21 You can use the hyperlink function.
• 01:26 You can specify the link location, the friendly name, blah blah blah blah blah.
• 01:31 Or you can do it the easy way.
• 01:34 You go to the insert tab on the ribbon, and then funnily enough,
• 01:38 you go to insert hyperlink, keyboard shortcut,
• 01:41 the rather obvious ctrl+K.
• 01:46 Now, this can look a little daunting when you first look at it.
• 01:49 Because it actually is allowing you to do lots and lots of different things.
• 01:53 When you go to an Existing File or Web Page, Place in This Document,
• 01:57 Create a New Document, or Email Address, I wanna place in this document,
• 02:01 and I want you to go back to the Table of Contents, and then cell A5,
• 02:06 I see, it's quite easy really .And, I've got
• 02:11 here the text display table of contents, cuz there's already something here.
• 02:14 You can change it if you want.
• 02:16 And I can put a screen tip in.
• 02:18 So now what a screen tip is,
• 02:20 is something that will appear when you hover over the hyperlink.
• 02:24 Now, remember, I merged cells A3 to E3 before,
• 02:26 so the hyperlink will work in all of those cells, that's a big strong tip.
• 02:31 If I go screen tip here, I can put in here go to
• 02:37 index type of context or whatever, and click okay.
• 02:40 Click OK, you have done it.
• 02:44 Look, come over here if I hover on this now.
• 02:48 Go to index, that's the screen saver.
• 02:50 Click on it, it's taking me to cell A5.
• 02:54 How difficult was that?
• 02:57 Well, yes, it wasn't very, was it?
• 03:00 What happens if I rename this, if I were to rename it and call it Fred,
• 03:05 I wanted something better.
• 03:09 That in cell A one becomes right here.
• 03:11 And if I click this now, reference is invalid.
• 03:14 You see, hyperlinks, you specified what sheet to go to and
• 03:18 then you've changed the sheet name.
• 03:20 It doesn't like that.
• 03:22 So don't do it that way.
• 03:25 What you have to do is put a range name.
• 03:28 In the destination, and I'm going to call it HL_TOC.
• 03:35 The HL stands for hyperlink, and TOC is table of contents.
• 03:39 The range names, because range names update when you change a sheet name,
• 03:44 when hyperlinks don't.
• 03:46 So we actually link to a range name and then it will work.
• 03:51 See what I mean?
• 03:52 If I go back to this and go to insert hyperlink, which is how you edit it.
• 03:57 I'm going to, instead of go for the cell references, I'm gonna click on HLTOC.
• 04:02 That grays out A1, it's not looking at that anymore.
• 04:04 It'll link to this, click okay.
• 04:06 Now it works.
• 04:09 If I change this to something else, let's call it navigate, let's say.
• 04:15 Click on this, it still works.
• 04:19 Isn't that good?
• 04:21 And I can do the same, I can create a table of contents here for first sheet.
• 04:25 So, I'm going to put my sheet name in here by referencing to it.
• 04:29 First sheet, A1, first sheet.
• 04:34 Again, I'm going to widen this rather than merge cells this time,
• 04:37 so that when I click on it, all go.
• 04:39 Then, I'm going to go back here, put in HL_sheet1 or
• 04:46 something else and, that's what I'll link to here.
• 04:52 So, I can go Ctrl+K, insert hyperlink to sheet 1, screen tip,
• 04:59 go to first sheet, click okay,
• 05:04 okay again, there you go, my first one.
• 05:09 Isn't that good?
• 05:11 Through the magic of recording videos, I've jumped ahead.
• 05:16 I've put two more sheets in, third and fourth sheet.
• 05:20 I've given them range names, HL_Sheet3, HL_Sheet4.
• 05:25 And I've actually got these hyperlinks now working to them.
• 05:29 Now the reason I put these prefixes in is if I go to the formula's name manager,
• 05:34 if there is lots and
• 05:35 lots of different ranges in here all the hyperlink ones will be grouped together.
• 05:39 That is why I do this.
• 05:41 You can see it doesn't take long this way to put a table of contents together
• 05:46 using hyperlinks, very useful in navigating round the spreadsheet.

Lesson notes are only available for subscribers.