Using hyperlinks for rapidly switching worksheet locations and jumping to websites

- 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.