This channel provides the absolute best and most comprehensive excel training on the internet. Absolutely brilliant training. And I cannot believe that it's free.
@alphaxg1 what is your requirement, in my case , I have data in one sheet and I am fetching all the data using lookup and text formula for date and many more, pl see her all the formula videos and let me know if you need any further concerns
Very good video Mynda. I’ve been using the hyperlinks for years but never knew about being able to assign them to buttons. Tabs approach’s very clever.
This is fantastic, thank you! Feels like a stepping stone toward "Turn Your Excel Into An App!" and, like you said, would be great if you have other people using your sheets.
Great video! I like how you used a defined name as the basis for the hyperlinks. I have also found that using VBA as a means to build a navigation system (userform) works well too. I reference the worksheets VBA code name and not the tab name. Of course this takes a bit longer to design, but if you lock down the VBE the end-user is not able to mess things up by deleting a hyperlink, name range or changing the worksheet tab name. Your demonstrated way in the video is really a great way to handle a multi-worksheet workbook. Thank you for another well-produced and informative video!
How I love watching your video tutorials with different interesting solutions! Thank you for your hard work! I am Russian-speaking and you are somewhat similar to Nikolai Pavlov from Russia.
Great video. Have long used hyperlinks in Excel and lengthy Word docs to allow users to quickly navigate large documents. Modifying them into buttons just makes it look cooler so will definitel integrate that into my efforts.
Thank you! Not sure I follow exactly what you mean for cell highlighting. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I guess you got a point, as in 90% of time it can be done. Though in a few cases like large spreadsheet ie: a lot of sheets, it will be faster for the navigation between sheets. You could make a case that the sheets should be arrange for a better navigation. But you may not able to do so. I found interesting this approach (like an apps) as it is creative and could open some new ideas.
I like to hide the sheet tabs because often there are sheets in the file that contain my workings and data that I don't want my report users navigating to. Having navigation tabs at the top focuses the user's attention on the parts of the file relevant to them.
@@MyOnlineTrainingHub I tend to hide the sheets I want to hide, arrange the sheets in an intuitive way, protect the structure (possibly the sheets, too) and call it a day. OTOH: if people/users want to lift the protection in Excel, they always find a way to break things wide open,… and then we have a different kind of conversation. (:-|) :-)
I have workbooks with like 20+ sheets in them. A quick link at the top on each sheet to the 3 or 4 other sheets that I usually refer back to from that sheet (source and outputs of the subject sheet) is intriguing. Navigating all those sheets is time consuming. I would just not want it to show up when I print the sheets. That's doable with print area though. Thanks!
i have been trying to do something similar, but its really difficuly to keep it updated as the excel grows. Hopefully Microsoft implements the Navigation Menu that is in word, which is currently on insider beta channel
Yep, the navigation pane and much more is available in Excel as shown here in my video on 5 new must have Excel features: ruclips.net/video/LLxRQJlQ8A0/видео.html
Thanks for a great video. I need some help regarding the navigation bar. I have bigger sheets with large data so when I scroll down or right my navigation pane also moves and disappears (even freezing panes do not work here), I want my navigation pane to stay on screen always so that I could navigate the other sheets through this pane. (I don't want to use VBA plz). Thanks and regards.
A question on the first part of your video, when you edit the hyperlink by using CRTL+Left click and then CTRL+K, is there any reason why you don't use a right click on it and select "edit hyperlink" (may be not the same wording in English)? The video is great and there are a lot of useful information in it, thank you for your work!
Great tutorial. Love your work! I have a question. I share workbooks in the cloud with only read permission for users. Does this work with only read permission or how do you make it work?
I have added a hyperlink on a sheet to another TAB in my workbook but I cannot used the = to link cells using the hyperlink. I must manually click the tab. Is there a way to do this?
I'm not sure what you mean, Derek. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks! Mynda, this was so lovely but in real life, you don't have 4 to 5 tabs. I have more than 60 tabs so 4 Tabs in Row A look nice but what about 60 tabs. What is your advice for those who have a lot of tabs? Thanks
If you've got 60 tabs, then I'd say you're probably structuring your data wrong. You've probably got it split out by department, product or other attribute, when ideally your data should all be in ONE table and from there you generate the reports that split it out by the attribute your sheets represent, but not necessarily into sheets. You can use Slicers to filter one report to see the data for the attribute you want. I'd be happy to chat with you further about this to see if that's actually the case and what would be the ideal solution if you want to reach out via email: website at MyOnlineTrainingHub.com
@@MyOnlineTrainingHub Thanks, Mynda! Actually, I have used Slicers, Timeline, and Show Report Filter Pages. So When I said 60 tabs I meant that when I used "Show Report Filter Pages", It gave me almost 60 vendor information reports. If I use the same Field in the Filter of the Pivot Table Filed list, I get almost 60 options in that one Field. Thanks
I'd be inclined to just use a Slicer to switch between the views, rather than generating 60 separate sheets. You don't need to put the field in the field list as well as the Slicer. Of course, you'll have 60 items in your Slicer, but this is no different to having a hyperlink to 60 separate sheets, except the Slicer is quicker to set up.
When I am sharing my sheet on a shared drive in office using hyperlink function (not onedrive or 365), my colleague is unable to open the links to view the files. Any help??
@@MyOnlineTrainingHub For instance, a list of items linked to by hyperlinks. When there's addition to it and re-sorted, the links do not follow the items but remains at their original specific cells.
Is it possible to create a button that takes you to the "Insert Hyperlink" dialogue box? I want users to have the freedom to insert a document link to anything on our server with the click of a button.
They should work AFAIK, but I don't have an iOS I can test them on, sorry. You can try posting your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
What is the difference between clicking your "tabs" and clicking on the tabbed sheet names themselves? Surely you have just recreated what Excel provides any way i.e. tabbed sheets?
Great question. I like to hide the sheet tabs because often there are sheets in the file that contain my workings and data that I don't want my report users navigating to. Having navigation tabs at the top focuses the user's attention on the parts of the file relevant to them.
@@MyOnlineTrainingHub its not working in MAC system. also i am preparing a contract, i want if i sign on page 1 and post if i click ok on sign 2, it should move to page 10. pl guide
Usually in a report file you have sheets you don’t need your users to see, like workings and data. You can hide these tabs but it’s a pain to unhide and hide them again when you want to work on them. I prefer to hide the sheet tabs from displaying and give the users their own navigation with hyperlink buttons. I can still navigate to my workings and data sheets easily with CTRL + Page Up/Down keys.
This channel provides the absolute best and most comprehensive excel training on the internet. Absolutely brilliant training. And I cannot believe that it's free.
Thanks so much! Please spread the word 🙏
@@MyOnlineTrainingHub You're very welcome. By the way, do you have a training on how to automatically fill a pdf?
No, sorry.
@alphaxg1 what is your requirement, in my case , I have data in one sheet and I am fetching all the data using lookup and text formula for date and many more, pl see her all the formula videos and let me know if you need any further concerns
Very good video Mynda. I’ve been using the hyperlinks for years but never knew about being able to assign them to buttons. Tabs approach’s very clever.
Glad you liked it, Oliver!
This is fantastic, thank you! Feels like a stepping stone toward "Turn Your Excel Into An App!" and, like you said, would be great if you have other people using your sheets.
Glad you like it :-)
Great video! I like how you used a defined name as the basis for the hyperlinks. I have also found that using VBA as a means to build a navigation system (userform) works well too. I reference the worksheets VBA code name and not the tab name. Of course this takes a bit longer to design, but if you lock down the VBE the end-user is not able to mess things up by deleting a hyperlink, name range or changing the worksheet tab name. Your demonstrated way in the video is really a great way to handle a multi-worksheet workbook. Thank you for another well-produced and informative video!
Cheers, Douglas!
How I love watching your video tutorials with different interesting solutions! Thank you for your hard work! I am Russian-speaking and you are somewhat similar to Nikolai Pavlov from Russia.
So pleased you enjoy my videos 😊
I like the idea of “tabbed” links.
Like other things that directly reference sheets, hyperlinks should update when a sheet name is changed
I agree, Patrick!
Brilliant, Mynda.
"Rinse and Repeat" 😊❤
Glad you liked it, Ian! 😊
Great video. Have long used hyperlinks in Excel and lengthy Word docs to allow users to quickly navigate large documents. Modifying them into buttons just makes it look cooler so will definitel integrate that into my efforts.
Glad it was helpful!
Good teacher 👍 student from India 🇮🇳 love you 🙏
Thanks so much 😊
Loved this. It made a report I designed look much more professional. Thank you!
Great to hear, Tom!
Wonderful video! Explained all uses very well! Thank you!! This is a lifesaver!
Glad it was helpful!
Hi Mynda. Some great tricks and techniques here! Thanks for sharing :)) Thumbs up!!
Cheers, Wayne!
Thank you for the information Mynda. All is perfect, as always!!
Thanks, Ivan 😊
I Have so many videos to see, you are an amazing teacher!
So glad you liked them 🙏
Thanks for all the tutorials and examples.
Glad you like them!
Hi Mynda!Really Like This Super Cool Technique For Hyperlink Buttons...Thank You :)
Cheers, Darryl!
Thanks Mynda, very elegant solution
Thanks, Dianne!
Great stuff. Noticed when I expand columns the tabs stretch out as well. Any way to "lock" the shape/size of the objects?
Yes, you can prevent resizing and moving in the Properties (right-click the object).
Great - your Defined Name has solved a problem I have been struggling with
Great to hear!
Very educative !!. Thank you Mynda
Glad you liked it 😊
Great video! Perfect timing to see this as I am searching for the exact tutorial! Subscribed! Keep it up!
Awesome, thank you!
Very cool idea Mynda! Thanks for sharing! 😊👍
Cheers, Vijay 😊
Incredible that I didn’t think doing this before 😁😁😁. Thanks Mandy 😉
Glad you'll be able to make use of it :-)
Love your videos very user friendly, easy to follow along and incorporate into day to day work activities.
So pleased you like them, Torey!
Thank you. Haven't seen that for awhile. Awesome refresher for me.
Glad you enjoyed it :-)
You are just extra ordinary, fantastic, mind blowing madam.
Thanks for your kind words 😊
This video is just in time 🔥🔥🔥 Thanks 💪🏾💪🏾
Great to hear!
Absolutely great videos. Can I make the cell highlight in wich the the link goes to? Using only in one sheet
Thank you! Not sure I follow exactly what you mean for cell highlighting. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Great video as always :)
Thank you! Cheers!
Very useful, i liked. Thank you Mynda...
You're welcome 😊
Concerning the tabbed approach at the end: what’s the difference with just clicking the actual tabs of the sheets?
I guess you got a point, as in 90% of time it can be done. Though in a few cases like large spreadsheet ie: a lot of sheets, it will be faster for the navigation between sheets. You could make a case that the sheets should be arrange for a better navigation. But you may not able to do so. I found interesting this approach (like an apps) as it is creative and could open some new ideas.
I like to hide the sheet tabs because often there are sheets in the file that contain my workings and data that I don't want my report users navigating to. Having navigation tabs at the top focuses the user's attention on the parts of the file relevant to them.
@@MyOnlineTrainingHub I tend to hide the sheets I want to hide, arrange the sheets in an intuitive way, protect the structure (possibly the sheets, too) and call it a day.
OTOH: if people/users want to lift the protection in Excel, they always find a way to break things wide open,… and then we have a different kind of conversation. (:-|)
:-)
I have workbooks with like 20+ sheets in them. A quick link at the top on each sheet to the 3 or 4 other sheets that I usually refer back to from that sheet (source and outputs of the subject sheet) is intriguing. Navigating all those sheets is time consuming. I would just not want it to show up when I print the sheets. That's doable with print area though. Thanks!
And freeze panes! Good idea on print area.
I think you can also turn off printing for the individual shapes
That's a great idea!
Love the tutorial ❤️ can you have another video concerning icons as hyperlink buttons for dashboard? Thank you!
Great suggestion!
@@MyOnlineTrainingHub Hope your channel to grow further! #Support
Thank you so much. You are a great teacher!
Glad you think so!
Lady, That was fantastic!!!
Thank you so much!!
Those are some sweet tricks thanks!!!
yes
Glad you like them!
@@MyOnlineTrainingHub yes i do thank you!
This is great, thanks Mynda!
Cheers, Chris!
i have been trying to do something similar, but its really difficuly to keep it updated as the excel grows. Hopefully Microsoft implements the Navigation Menu that is in word, which is currently on insider
beta channel
Yep, the navigation pane and much more is available in Excel as shown here in my video on 5 new must have Excel features: ruclips.net/video/LLxRQJlQ8A0/видео.html
Very useful. Thanks sharing this with us.
My pleasure, Patrick!
You are always Amazing!!!
Thanks for your kind words :-)
Many Thanks. Do you have a spreadsheet that includes the data for your charts. Thanks again
The link to the file download is in the video description.
@@MyOnlineTrainingHub Thank you. I downloaded the file but there is no data linked to your charts.
Thanks again
Hi Mynda, this is great but is it possible to HIDE the hyperlink when you hover over the button, so that you don't see the file path. Thanks
Not that I'm aware of.
Liked the video 👏🏻 i have a question Can i add pictures as button instead
Sure, any object can have a hyperlink attached to it.
@@MyOnlineTrainingHub please make a video about that
Thanks for a great video. I need some help regarding the navigation bar. I have bigger sheets with large data so when I scroll down or right my navigation pane also moves and disappears (even freezing panes do not work here), I want my navigation pane to stay on screen always so that I could navigate the other sheets through this pane. (I don't want to use VBA plz). Thanks and regards.
You can use Freeze Panes to pin columns/rows: ruclips.net/video/_ukgNk3-iU4/видео.html
Can you make a video on how to use excel for inventory management for a small business.
I'll keep it in mind :-)
Thanks for great Vid. One question. How do i add links like this on my ipad. I cannot seem to work it like you suggest
They don't work on iPads AFAIK.
A question on the first part of your video, when you edit the hyperlink by using CRTL+Left click and then CTRL+K, is there any reason why you don't use a right click on it and select "edit hyperlink" (may be not the same wording in English)? The video is great and there are a lot of useful information in it, thank you for your work!
Yep, you can right-click. I just prefer the keyboard shortcut...habits :-)
Thank for sharing. It is very useful 👍👌
Wonderful :-)
Nice video thank you for your hard work 👍
My pleasure, Nader!
Can u help with lazy loading web page , as it returns only 20 elements
Thank you for sharing your videos..
My pleasure!
Simply Great !!!!!🙏
Glad you liked it!
Great tutorial. Love your work! I have a question. I share workbooks in the cloud with only read permission for users. Does this work with only read permission or how do you make it work?
Yes, it works with read only permission.
I have added a hyperlink on a sheet to another TAB in my workbook but I cannot used the = to link cells using the hyperlink. I must manually click the tab. Is there a way to do this?
I'm not sure what you mean, Derek. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks! Mynda, this was so lovely but in real life, you don't have 4 to 5 tabs. I have more than 60 tabs so 4 Tabs in Row A look nice but what about 60 tabs. What is your advice for those who have a lot of tabs? Thanks
If you've got 60 tabs, then I'd say you're probably structuring your data wrong. You've probably got it split out by department, product or other attribute, when ideally your data should all be in ONE table and from there you generate the reports that split it out by the attribute your sheets represent, but not necessarily into sheets. You can use Slicers to filter one report to see the data for the attribute you want. I'd be happy to chat with you further about this to see if that's actually the case and what would be the ideal solution if you want to reach out via email: website at MyOnlineTrainingHub.com
@@MyOnlineTrainingHub Thanks, Mynda! Actually, I have used Slicers, Timeline, and Show Report Filter Pages. So When I said 60 tabs I meant that when I used "Show Report Filter Pages", It gave me almost 60 vendor information reports. If I use the same Field in the Filter of the Pivot Table Filed list, I get almost 60 options in that one Field. Thanks
I'd be inclined to just use a Slicer to switch between the views, rather than generating 60 separate sheets. You don't need to put the field in the field list as well as the Slicer. Of course, you'll have 60 items in your Slicer, but this is no different to having a hyperlink to 60 separate sheets, except the Slicer is quicker to set up.
Thanks for the great work
Glad you enjoyed it
Absolute brilliance
Thanks so much, Adeoye!
Thank you) I use buttons with hyperlinks too))
Great to hear, Andrew!
lovely video...like you❤
Glad you enjoyed it! 😀
To create the index button for all tabs, couldn't you select all the tabs you want to create the button for and do it only once?
You can't insert a shape on grouped sheets/tabs.
Mynda,
How can we refer to any table by using hyperlinks?
If you give the table a defined name then you can reference it.
When I am sharing my sheet on a shared drive in office using hyperlink function (not onedrive or 365), my colleague is unable to open the links to view the files. Any help??
I suspect the colleague doesn’t have access to the location you’re linking to.
Greetings. Is there any way to prevent broken links if a list with links is sorted? Thanks.
Not sure exactly what you mean, but maybe you could try using INDEX to find the link you need.
@@MyOnlineTrainingHub For instance, a list of items linked to by hyperlinks. When there's addition to it and re-sorted, the links do not follow the items but remains at their original specific cells.
Help. How to creat a button to work on an iPad. I can do one on my laptop no problem
iPad doesn't support Slicers or form controls, sorry.
Is it possible to create a button that takes you to the "Insert Hyperlink" dialogue box? I want users to have the freedom to insert a document link to anything on our server with the click of a button.
You could record a macro that opens the Insert Hyperlink dialog box and attach it to a button.
Hyperlinks don't seem to work on excel for ios :( Would be so nice to be able to have functioning buttons in excel for ios. Any ideas?
They should work AFAIK, but I don't have an iOS I can test them on, sorry. You can try posting your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
thank you great video
Glad you enjoyed it, George!
Amazing !! thank you
My pleasure 😊
This is brilliant
Glad you'll find it useful, Richard!
EXCELLENT. THANKS
Glad it was helpful 😊
What is the difference between clicking your "tabs" and clicking on the tabbed sheet names themselves? Surely you have just recreated what Excel provides any way i.e. tabbed sheets?
Great question. I like to hide the sheet tabs because often there are sheets in the file that contain my workings and data that I don't want my report users navigating to. Having navigation tabs at the top focuses the user's attention on the parts of the file relevant to them.
as soon as i create pdf file from excel, none of the links work, please guide.
You need to generate the pdf via the File tab > Export > Create PDF/XPS Document.
@@MyOnlineTrainingHub awesome, works, you helped many of us. Thank you again
@@MyOnlineTrainingHub its not working in MAC system. also i am preparing a contract, i want if i sign on page 1 and post if i click ok on sign 2, it should move to page 10. pl guide
Nice Job )
Thanks so much!
Used this trick way back in 2015
Me too! It never gets old 😊
Very useful
Cheers, James 😊
Looks great, but... wouldn't be easier just to click on the sheet page...
Usually in a report file you have sheets you don’t need your users to see, like workings and data. You can hide these tabs but it’s a pain to unhide and hide them again when you want to work on them. I prefer to hide the sheet tabs from displaying and give the users their own navigation with hyperlink buttons. I can still navigate to my workings and data sheets easily with CTRL + Page Up/Down keys.
I just put hyperlinks on the text
Yep, that works too, but it's not as pretty ;-)
@@MyOnlineTrainingHub I agree but it takes a quarter as much time and you can make them dynamic.
#marvellous
Thank you!
Nice
Cheers, Wouter!
Wow
Glad you liked it, Yogesh!