Cool Tricks with Excel Hyperlink Buttons

hyperlink buttons are a slick way to enable your users to navigate through your workbook it's especially handy when you have large workbooks or you want your users to focus on specific sheets in this video i'll show you how to set them up and a clever way to create a tabbed effect in your workbooks plus i'll show you how to prevent hyperlinks breaking when sheets are renamed okay let's take a look in this file i've got multiple sheets for my regions and the budget or actual values with some dummy charts just for effect now i want to make it easy for my users to navigate to the various sheets in the file and one way is to set up an index page that allows them to easily jump back and forth to the index using shapes that look like buttons to give a nice user experience that's also intuitive to use we'll find the buttons on the insert tab under illustrations shapes and i like to use the rectangle with the rounded corners just draw in by left clicking and dragging and then type in the text for the button i'm going to say here click to go to budget and we'll just go to the home tab and format that text so that it's centered vertically and horizontally i'll change the shape formatting i'm going to go with this green down here that already has the shadow on it and that just helps it to look more like a button so there's my shape and i'm ready to insert my hyperlink we can do that by the insert tab and then link or you can use the keyboard shortcut control k which i prefer in the insert hyperlink dialog box you can link to an existing file or web page a place in this file or an email address now i want to link to another sheet in the file up here i can type in the cell reference it defaults to cell a1 and that's fine for me now this button is going to take me to the north region's budget so i'll select the north budget sheet and click ok now i can click the button and it takes me to the north budget now the problem with setting up hyperlinks this way is that if i rename the sheet the hyperlink no longer works so the way to prevent them from breaking is to define names for the locations you want the hyperlinks to so i need a hyperlink to take me to the north budget sheet cell a1's already selected and i can simply use the name box and give this cell a name so now i've set up that name we can go back to the button holding down control you can left click that will just prevent the hyperlink from executing then ctrl k to open the hyperlink dialog box and now you can see i have defined name north budget so i can select that and click ok and now if i rename the sheet here it's not going to break the hyperlink i'll quickly define names for the remaining sheets including one for the index sheet and now i can simply copy this button holding down control and shift will allow me to left click and drag to copy it and it will also keep it aligned to the other button so now i just need to change this to say click to go to actual and then selecting the outer edge ctrl k to open the hyperlink dialog box now i can select it from the list of defined names so now all i need to do is copy these two buttons again holding ctrl and shift left click and drag to copy and ctrl k to edit the hyperlink so this is the south region's budget and this one is the south region actual so there's my hyperlink so i can click on any one it will take me to that sheet but what i need is another button on these sheets to take me back to the index so let's insert another shape pop it up here and this one is called index and let's format the font so that it's centered and then i'll make this shape this gray button ctrl k to open the hyperlink and this time i want to go to the define name for index now i need one of these buttons on each sheet so just with it selected ctrl c to copy and then go to each sheet paste it in and then you'll be able to move back and forth between the sheets and the index another way i like to use hyperlink buttons is to create a tabbed effect much like you might see in a web browser i start by adding a cell border so i want my buttons to sit up here on row 1 and with those cells selected ctrl 1 to open the formatting pane on the border tab i want this thick border and i'm going to make it in this dark blue so there's my border now all i need to do is insert my buttons again insert illustrations this time i want to use this button here that has the rounded top corners and the flat bottom and i'll left click and draw it in i'm just going to move it down to the bottom line we'll make it almost as wide as the cell so that's my first button i'm just going to nudge it up with the arrow key i'll change the format to remove the outline and we'll make it a slightly darker blue so this one is for my first sheet that i'm on which is north budget so let's give it some text and make sure that that text is centered vertically and horizontally so there's my first button let's left click and drag while holding shift and control to copy that three times and we'll just need to make this a little wider and that one a little less wide okay let's rename this so this is north actual this one's south budget and this one is south actual now these are the buttons for my inactive sheets that is the other sheets in the file so let's give them a different color so rather than a dark blue we'll make them a paler blue and we'll make the font on those buttons a dark gray i'll just move this on over a smidge okay so they're evenly spaced this one i also want to give it some shadow effect and that will just make it pop so we'll give it this all-round shadow and that just makes it stand out a little bit more from the other buttons to indicate that this is the current sheet that we're on now all i need to do is add the hyperlinks so ctrl k now we're currently on this sheet so i technically don't need a hyperlink on this button but i'm going to put it on because i'm going to copy these buttons and paste them on the other sheets and that's just going to save me having to add the hyperlinks one by one i'll do it once and then copy so this is self budget and lastly south actual okay so all my hyperlinks are set up now notice i don't need an index page with this technique i can simply jump from one sheet to the other i don't need to go back to the index so with them all set up i'm just going to select the row ctrl c to copy go into the next sheet and paste it in now here i need to change the dark blue to the north actual sheet so rather than go through all that formatting again i'm just going to use the format painter and holding down control paste the format and then holding down control to select this one i'm going to copy this format holding control paste it onto that one which is no longer the active sheet so let's copy that and we'll paste it on to the next one and you simply rinse and repeat using the format painter and then painting that format onto there one more to do this one's now my south actual is now my current sheet my active sheet and south budget is no longer so now i can jump between the sheets without the need for that extra index sheet i think this is a really great user experience when you have multiple report tabs in your file well i hope you found these techniques useful you can download the file for this lesson from the link here if you like this video please give it a thumbs up and subscribe to my channel for more and why not share with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 23,082

Rating: 4.990099 out of 5

Keywords: excel hyperlinks, excel hyperlink buttons

Id: cue1tyAKWxs

Length: 8min 49sec (529 seconds)

Published: Thu Aug 12 2021