Locked lesson.
About this lesson
When and where to use hyperlinks to aid end user navigation.
Exercise files
Download this lesson’s related exercise files.
Hyperlinks.xlsx14 KB Hyperlinks - Solution.xlsx
22.3 KB
Quick reference
Hyperlinks
Discover how to use a Hyperlinks in a workbook.
When to use
Hyperlinks are a great way to move around a file. If you create a central worksheet with hyperlinks to all of the other worksheets, you are only ever two clicks away from anywhere else in the workbook. They make life very easy for end users and once you know how to construct them, they take mere seconds to insert.
- Accessing:
- Insert -> Hyperlinks
- Properties:
- Cell reference (destination cell)
- Displayed text
- Screen Tip
- To insert a Hyperlink which links back to a table of contents:
- Insert
- Hyperlink
- Select ‘Place in this Document’
- Ensure you have named the cell that you want to link and select the name from the list
- You can insert a screen tip by selecting the ‘ScreenTip’ button and write a message such as “Go to Table of Contents”
- OK
- 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.