Join Dennis Taylor for an in-depth discussion in this video Using hyperlinks for rapidly switching worksheet locations and jumping to websites, part of Excel Tips Weekly.
- With Excel's Hyperlink feature, you can set up links within worksheets to websites, to different worksheets within the same workbook, to a range name, or even to another file, or in effect, open another file, say, a Word file, Excel file, PowerPoint file, something like that. And if you simply type in a web address, you automatically have a hyperlink. For example, maybe I frequently use data from the Census Bureau, www.census.gov, Federal Census Bureau.
As soon as I type a web address, and press Enter, an automatic hyperlink, and if I click this, we're quickly at the census.gov site, there it is. I don't really need that right now but we can get there quickly and easily from that button at any time. Now, we've already created the hyperlink here. If we right click this hyperlink, we'll see we can Edit it. Now, the Text to display is what we typed in, and there's nothing wrong with that, but on the other hand, depending upon who might use this, you might say Federal Census Bureau or US Census Bureau, however you want to say it.
Whatever the official name is, possibly, or a name that might make sense to someone maybe isn't too familiar with website addresses. US Federal Census Bureau. That's probably not their official name, but you want something that would ring true to somebody. You could provide a ScreenTip. Now, the ScreenTip, of course, can be anything you want it to be but let's say it guides a user into clicking that particular option. So you might say something like, Click here to get to census bureau statistics, so we'll see this as we slide over this.
Click OK. The Address stays the same just as we put it in, don't have to change that at all. Let's click OK, it will look different now. Now it says US Federal Census Bureau. Slide over this, what do we see? A Tip. Click here to get census bureau statistics, plus easy to use. If at a later time, we don't need that, we can right click and Remove the Hyperlink. The text stays, but it's no longer a hyperlink, and, used to be in the past, we could only do these one at a time. We can now do them for entire columns or sets of cells at once.
Now, possibly, you can also set up a website link simply by inserting a hyperlink anywhere you want without doing any typing initially. We can press Ctrl + K, here's Hyperlink on the INSERT tab, or you can right click a cell and choose Hyperlink, and of course, one of our options is Existing File or Web Page, so here, we could put in the Address, www.census.gov, and pretty much what we had done before, Text to display, we can display anything we want to, so we could, without repeating what we had done before, I'll just say Census Bureau, and I won't provide the Tip.
There will be a ScreenTip, by the way, automatically, so we'll see that as well here, too. So this time, we're simply saying Existing File or Web Page, right here, census.gov, and there it is, Census Bureau up top, click OK. As we slide over this, the pop up Tip gives us the Address, and it says Click once to follow, so, almost what we did before, but in a different way. Now, this particular workbook has a sheet for each state. We'll see all the states here. Maybe as we work with this data, we frequently go to certain states a lot.
For example, Ohio. I'm going to set up a hyperlink so that when we click on Ohio here, we will automatically jump to that sheet. So, we can begin with Ctrl + K or right click, Insert Hyperlink, or from the INSERT tab in the Ribbon, there's Hyperlink. There we go. So this time, it shows us all sheet names, and if we have any, range names. Ohio, click OK. It's going to jump us to the Ohio sheet. Here, too, we can control the ScreenTip if we wish.
Not really necessary, maybe, but part of the thought might be, who's using this, who might be using it. Does that scare people away a little bit or is it innocuous? We don't care. Click here, what happens? We jump to the Ohio sheet. Now, we might do that for some other sheets, eventually, we might do it for all of them, that's going to take some time. If we Ctrl + click the left arrow at the bottom, we'll see the first few sheet names. Let's go back to Links. It's possible that we might want people to visit a certain range, it's on the last sheet, to get these 2014 State Population Estimates.
Now, I don't have a range name set up yet, but I'm going to go to the last sheet. I'll hold down Ctrl and click the right arrow. Lower left corner here, see the last few sheet names, including one called Census, and here's the data I'm talking about. So I'm going to give this a range name, this data right here. Clicking on the top cell, holding down Shift, double click, certainly do it that way, and then, on the FORMULAS tab, Define a Name, and we'll simply call this... We could go with the default, in other words, Excel sees the heading there and suggests that as a possible entry.
I think we can certainly go with that. That's, nothing wrong with that. It already has the addressing set up since I highlighted it. Let's click OK. We now have a range name. So back to the first sheet again. Ctrl with the left arrow at the bottom, Links, I'll click here. I want to turn this existing text into a hyperlink. Ctrl + K. Remember it shows... When we choose Place in This Document, we see all the sheet names, as well as range names if they've been defined. Now I just define once, so there it is, right there.
Click OK. That's an automatic link to that location. Click here, it jumps here. Now it's highlighted, nothing wrong with that but it does take us to the entire range name that way. Once again, back to the beginning. Now, maybe it's important at different times to refer to a particular document that we have. I've got a Word document, maybe, that's relevant to this data. We can insert a hyperlink here for the moment that's nameless. Once again, Ctrl + K, or INSERT Hyperlink.
Do it that way this time possibly. Existing File or Web Page, and I've got some data here that's in the Exercise Files group, and it's a Word file about structured references with Excel table. Maybe it's relevant to some of the things we're doing with this worksheet. Click OK. Now, that's a long title. What I would probably do here is right click and Edit this. Nothing wrong with that, really, but maybe change the heading, and how about Structured references, something like that.
Again, keeping in mind what will be meaningful to the audience that might be using this and also sufficiently long enough to explain what's going on. So, if I click this, it will open the Word file that was referred to in the hyperlink, and there it is. It's opened Microsoft Word to take us here. So, lots of different examples here. Press Alt + Tab and get back into Excel. Lots of different examples of how to use hyperlinks. Remember, we can jump to webpages, we can go to worksheets within the current document, we can go to range names within the current document, or we can go to other files which might be of different file type, for example, when we saw with Microsoft Word.
So different uses of the Hyperlink concept in Excel.
Author
Updated
1/19/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 14m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Using hyperlinks for rapidly switching worksheet locations and jumping to websites