Links to quickly Navigate within Excel Work Sheets with Buttons and Hyperlink

welcome to the channel excel is magical by shaquille in this video you're going to learn how to create links to all the worksheets and also to a particular area within a worksheet in an excel file which you can access by click of a button this will help you quickly navigate in a file which has multiple worksheets with large and complicated data something like this there is an excel file which is opened where there are multiple tabs right like orders categories and all and i have made one tab here my name index where you see some nice background and in this tab i have made few links like we have some buttons here orders categories invoices and all that so when a user goes and clicks on one of the button for example i click on payroll so i'm taken to a tab by name payroll summary and have also made few index buttons so that user can click on this and get back to the first tab so they can travel to any other tab of this workbook not only that you have a tab by name employees you could see that here there's an employee tab and uh each region in that employee tab is in a different row here like you could see 60th row is west 40th row is east so what i've done is i have linked not only a tab and also to a particular area of a tab like if someone clicks on south they are taken to the south area that is a21 index if i click on east they're taken to east area so there is a particular range of excel sheet let's explore how this can be done in excel [Music] this file which is open now contains uh all these tabs just show you that so these are the tabs which are available in this particular file and i also have a tab by name index that is my first tab so in this tab i have made few buttons like this these are actually some shapes all right so right now there is no connection from this buttons to any of the tabs i'll be teaching you this step-by-step before i teach you how to create the links first create this initial tab that is index tab with all this background and buttons and all that let me create a blank sheet in this blank sheet i'm gonna create all those buttons it's pretty simple you need to go to insert tab on insert tab you find illustrations and you find shapes click on that there are various shapes here you can choose according to your requirements let me choose oval let me draw that to make it more appealing you can go to drawing tools when you select on the button you get this drawing tools format i use this shape styles let me pick one of the style maybe this button you can as well customize this so here on this particular button let me give some name to this maybe imagine i would like to go to invoices from this button let me write invoices sheet invoices right then just the size and knowledge to adjust so let me change the size to 20 or 25 whatever then just the alignment and now you have to take care of the sizes alignment and also that it looks pretty good like this i have added multiple shapes here right and you could also see there are no grid lines in this tab so for that what i did just go to view tab on the view tab on this show group you have grid lines which is marked you just need to untick this so that the grid lines disappear and the last thing which is done in this tab is the background right so for the background we need to go to page layout tab in page layout tab you find an option background which is under paid setup group so i click on background so either i can pick a pic from online or you can also go offline as well let me work offline now access folder where you have stored your file i'm gonna navigate to this folder where i have stored few files the same image what you see on that index page so i'll just go click on this i click on insert so you could see that image has come in the background all right so this is how this particular tab has been created now we need to start connecting all these buttons to the tabs available in my workbook to create links to the tabs of this workbook i need to pick one of these buttons and use a command hyperlink hyperlink command or the tool can be accessed by either right clicking on the button right and where you are creating the link basically you find an option link here in the older versions you might also see it as hyperlink both the same either you go click on this or you can also access the same command using a shortcut ctrl k ctrl key gonna bring me this dialog box wherein i will select and choose where exactly i need to be linked from this button now this hyperlink is basically created to link to the files what you see is that is that those files to which you can create a link or you can also link it to an url you can write the website address here but in our case it is not the files or website and all that right we are trying to connect this to a existing tab of this workbook so on the left panel here there is an option linked to all right in this link to you see various options where one of the option is place in this document so i need to click on this place in this document here because we are connecting something in this document for scroll up you could see all the tabs available here like we have this sheet one right index orders and so on so now i need to connect this button to my orders tab right so i just go click on all this tab and that's it see when i click ok now i have created a link from index to orders now let me create one more maybe i want to create a link to a payroll summary for example so i go to payroll i right click on it i go to link choose this i choose the payroll tab in this list here i choose payroll summary tab and i click ok that's it oh look at that see whether it's working or not so i go click on payroll now you go so you're connected to payroll same way if i go to index again i click on others you go to the tab orders now that you have connected to a particular tab you'll also see how do we connect it back to the index tab now it's the same technique again just that you need to create a button so insert i go to illustrations shapes i have chosen a block arrow something like this so i'll just create a block arrow like this right and format this to give a feel of a button let me give some name to it maybe index is more suitable or home page or home sheet whatever you want so i can align this all right so now i want to click this and make a hyperlink to connect to an index tab right same steps again ctrl k go connect it to index tab click on ok now you could see when i click on the index button it takes me to my index tab like this you have created a link between these two tabs so click click here you are in this tab click again on the index to go back to the index tab so this is the connection between the tabs now next i have a tab here by name employees in employees tab i have some areas like north south east and west from my index tab when i use a crits on north it should take me to the north area if they click on west it should be on a west area for creating this you need to create something called range names for creating a range name you need to go to the place where you are creating this name i want to create a link to this north area right so i first click on the cell a1 or you can also pick multiple cells as well and then a range name can be created by going and clicking on this place called name box go to the name box give a name for it here i'll give it as not spaces are not allowed while naming the range there are a lot of other rules and not i'm not covering this that particular thing in this topic now hit enter so now i have name this particular area as north same way i need to name the other areas so i select this i go here give it as south right so this i make it as east you can also click one cell and name as well fine let's see what exactly we have done now i will go to index sheet i click on not create a hyperlink again right click link now you could see just below this tab names which which came up earlier you also have something called defined names defined names keeps those range names which we have just created like we had made east north south right so all those names are appearing here that means i can connect this particular button to that area as well as we are connecting this to not so let me choose the range name north here right so i click on not and i click on ok same way if you want to connect east so i just select this particular button ctrl k click east and then click on okay all right so let us see whether this works so i go click on the button north hey i go to the not now i go click on east it exactly went to the area east that is the cell a40 here right so i could see only that particular range on the screen and again you can create those index buttons whatever is made earlier in the other sheets like like these kind of buttons you can make multiple or else you can just simply copy and paste that i right click on it copy i go to this and paste it ctrl v right resize right so take care for that font size and all that again right click copy maybe i'll just paste it here ctrl v right so now when i when i paste even the links on paste paste india so click and you can easily navigate between index and any particular area of that particular tab so this would be pretty helpful when you have a large amount of data in our excel sheet and reaching to that particular area becomes very faster with this kind of techniques and that's it in this video regarding quickly navigate between worksheets with link buttons thanks a lot for watching if you have liked this video give a thumbs up and if you want to see more videos from me please do subscribe to my channel you

Channel: ExcelisMagical - Shakeel

Views: 2,490

Rating: 5 out of 5

Keywords: MS Excel, Navigate withing excel file, Link in excel with buttons, Create index in excel, Hyperlink in Excel, Index with hyperlink to all sheets, Navigate Within excel file, hyperlink, hyperlink buttons, index, navigation buttons in excel, Links in Excel, Links, Link in Excel, buttons, buttons in excel, create button in Excel, create buttons, navigation buttons, links to tab, links to worksheets, links to sheets, sheet link

Id: bPHoQv0-HBY

Length: 14min 6sec (846 seconds)

Published: Fri Dec 04 2020