Cool Tricks with Excel Hyperlink Buttons
Ғылым және технология
Excel hyperlink buttons make your reports look slick and allow users to navigate workbooks in an intuitive way. Download the example file here: www.myonlinetraininghub.com/e...
0:23 Creating Buttons
1:20 Inserting Hyperlinks
2:00 Preventing Hyperlinks Breaking
4:35 Tabbed Hyperlinks
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
Пікірлер: 172
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!
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Douglas!
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.
@MyOnlineTrainingHub
3 жыл бұрын
Glad you like it :-)
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.
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Oliver!
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.
@MyOnlineTrainingHub
3 жыл бұрын
Glad it was helpful!
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.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks so much! Please spread the word 🙏
@alphaxg1
2 жыл бұрын
@@MyOnlineTrainingHub You're very welcome. By the way, do you have a training on how to automatically fill a pdf?
@MyOnlineTrainingHub
2 жыл бұрын
No, sorry.
Thank you for the information Mynda. All is perfect, as always!!
@MyOnlineTrainingHub
3 жыл бұрын
Thanks, Ivan 😊
Love your videos very user friendly, easy to follow along and incorporate into day to day work activities.
@MyOnlineTrainingHub
3 жыл бұрын
So pleased you like them, Torey!
Loved this. It made a report I designed look much more professional. Thank you!
@MyOnlineTrainingHub
2 жыл бұрын
Great to hear, Tom!
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.
@MyOnlineTrainingHub
3 жыл бұрын
So pleased you enjoy my videos 😊
Great video! Perfect timing to see this as I am searching for the exact tutorial! Subscribed! Keep it up!
@MyOnlineTrainingHub
3 жыл бұрын
Awesome, thank you!
I like the idea of “tabbed” links. Like other things that directly reference sheets, hyperlinks should update when a sheet name is changed
@MyOnlineTrainingHub
3 жыл бұрын
I agree, Patrick!
Very cool idea Mynda! Thanks for sharing! 😊👍
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Vijay 😊
This video is just in time 🔥🔥🔥 Thanks 💪🏾💪🏾
@MyOnlineTrainingHub
3 жыл бұрын
Great to hear!
This is great, thanks Mynda!
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Chris!
Thank you. Haven't seen that for awhile. Awesome refresher for me.
@MyOnlineTrainingHub
3 жыл бұрын
Glad you enjoyed it :-)
Very educative !!. Thank you Mynda
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it 😊
Thanks for all the tutorials and examples.
@MyOnlineTrainingHub
2 жыл бұрын
Glad you like them!
Brilliant, Mynda. "Rinse and Repeat" 😊❤
@MyOnlineTrainingHub
2 жыл бұрын
Glad you liked it, Ian! 😊
Hi Mynda. Some great tricks and techniques here! Thanks for sharing :)) Thumbs up!!
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Wayne!
Incredible that I didn’t think doing this before 😁😁😁. Thanks Mandy 😉
@MyOnlineTrainingHub
3 жыл бұрын
Glad you'll be able to make use of it :-)
Hi Mynda!Really Like This Super Cool Technique For Hyperlink Buttons...Thank You :)
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Darryl!
Thank you so much. You are a great teacher!
@MyOnlineTrainingHub
3 жыл бұрын
Glad you think so!
Wonderful video! Explained all uses very well! Thank you!! This is a lifesaver!
@MyOnlineTrainingHub
Жыл бұрын
Glad it was helpful!
Very useful, i liked. Thank you Mynda...
@MyOnlineTrainingHub
3 жыл бұрын
You're welcome 😊
I Have so many videos to see, you are an amazing teacher!
@MyOnlineTrainingHub
Жыл бұрын
So glad you liked them 🙏
You are always Amazing!!!
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for your kind words :-)
Lady, That was fantastic!!!
@MyOnlineTrainingHub
2 жыл бұрын
Thank you so much!!
Thank you for sharing your videos..
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure!
Those are some sweet tricks thanks!!!
@updite739
3 жыл бұрын
yes
@MyOnlineTrainingHub
3 жыл бұрын
Glad you like them!
@taahatube1
3 жыл бұрын
@@MyOnlineTrainingHub yes i do thank you!
Very useful. Thanks sharing this with us.
@MyOnlineTrainingHub
2 жыл бұрын
My pleasure, Patrick!
Thanks for the great work
@MyOnlineTrainingHub
3 жыл бұрын
Glad you enjoyed it
Thanks Mynda, very elegant solution
@MyOnlineTrainingHub
3 жыл бұрын
Thanks, Dianne!
Simply Great !!!!!🙏
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it!
Absolute brilliance
@MyOnlineTrainingHub
3 жыл бұрын
Thanks so much, Adeoye!
Great video as always :)
@MyOnlineTrainingHub
Жыл бұрын
Thank you! Cheers!
Good teacher 👍 student from India 🇮🇳 love you 🙏
@MyOnlineTrainingHub
3 жыл бұрын
Thanks so much 😊
Great - your Defined Name has solved a problem I have been struggling with
@MyOnlineTrainingHub
Жыл бұрын
Great to hear!
Amazing !! thank you
@MyOnlineTrainingHub
3 жыл бұрын
My pleasure 😊
Nice video thank you for your hard work 👍
@MyOnlineTrainingHub
3 жыл бұрын
My pleasure, Nader!
You are just extra ordinary, fantastic, mind blowing madam.
@MyOnlineTrainingHub
2 жыл бұрын
Thanks for your kind words 😊
thank you great video
@MyOnlineTrainingHub
2 жыл бұрын
Glad you enjoyed it, George!
EXCELLENT. THANKS
@MyOnlineTrainingHub
3 ай бұрын
Glad it was helpful 😊
Thank for sharing. It is very useful 👍👌
@MyOnlineTrainingHub
3 жыл бұрын
Wonderful :-)
This is brilliant
@MyOnlineTrainingHub
2 жыл бұрын
Glad you'll find it useful, Richard!
Thank you) I use buttons with hyperlinks too))
@MyOnlineTrainingHub
3 жыл бұрын
Great to hear, Andrew!
Nice Job )
@MyOnlineTrainingHub
3 жыл бұрын
Thanks so much!
Very useful
@MyOnlineTrainingHub
2 жыл бұрын
Cheers, James 😊
Love the tutorial ❤️ can you have another video concerning icons as hyperlink buttons for dashboard? Thank you!
@MyOnlineTrainingHub
2 жыл бұрын
Great suggestion!
@shawnchristophermalig4339
2 жыл бұрын
@@MyOnlineTrainingHub Hope your channel to grow further! #Support
Absolutely great videos. Can I make the cell highlight in wich the the link goes to? Using only in one sheet
@MyOnlineTrainingHub
3 ай бұрын
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 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!
@bc4198
3 жыл бұрын
And freeze panes! Good idea on print area.
@anshulnarula738
3 жыл бұрын
I think you can also turn off printing for the individual shapes
@MyOnlineTrainingHub
3 жыл бұрын
That's a great idea!
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?
@MyOnlineTrainingHub
3 жыл бұрын
Yes, it works with read only permission.
Used this trick way back in 2015
@MyOnlineTrainingHub
3 жыл бұрын
Me too! It never gets old 😊
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!
@MyOnlineTrainingHub
3 жыл бұрын
Yep, you can right-click. I just prefer the keyboard shortcut...habits :-)
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
@MyOnlineTrainingHub
2 жыл бұрын
Yep, the navigation pane and much more is available in Excel as shown here in my video on 5 new must have Excel features: kzread.info/dash/bejne/foCstLODnLSecZM.html
#marvellous
@MyOnlineTrainingHub
3 жыл бұрын
Thank you!
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.
@MyOnlineTrainingHub
2 жыл бұрын
You can use Freeze Panes to pin columns/rows: kzread.info/dash/bejne/kamfybCkY5DPhZc.html
Nice
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Wouter!
Wow
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it, Yogesh!
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
@MyOnlineTrainingHub
2 жыл бұрын
They don't work on iPads AFAIK.
Can you make a video on how to use excel for inventory management for a small business.
@MyOnlineTrainingHub
2 жыл бұрын
I'll keep it in mind :-)
Many Thanks. Do you have a spreadsheet that includes the data for your charts. Thanks again
@MyOnlineTrainingHub
2 жыл бұрын
The link to the file download is in the video description.
@davepatterson3990
2 жыл бұрын
@@MyOnlineTrainingHub Thank you. I downloaded the file but there is no data linked to your charts. Thanks again
Liked the video 👏🏻 i have a question Can i add pictures as button instead
@MyOnlineTrainingHub
2 жыл бұрын
Sure, any object can have a hyperlink attached to it.
@safranayippuzha5608
2 жыл бұрын
@@MyOnlineTrainingHub please make a video about that
Great stuff. Noticed when I expand columns the tabs stretch out as well. Any way to "lock" the shape/size of the objects?
@MyOnlineTrainingHub
10 күн бұрын
Yes, you can prevent resizing and moving in the Properties (right-click the object).
Concerning the tabbed approach at the end: what’s the difference with just clicking the actual tabs of the sheets?
@merciexl21
3 жыл бұрын
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.
@MyOnlineTrainingHub
3 жыл бұрын
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.
@GeertDelmulle
3 жыл бұрын
@@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. (:-|) :-)
Can u help with lazy loading web page , as it returns only 20 elements
Mynda, How can we refer to any table by using hyperlinks?
@MyOnlineTrainingHub
3 жыл бұрын
If you give the table a defined name then you can reference it.
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?
@MyOnlineTrainingHub
Жыл бұрын
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
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
@MyOnlineTrainingHub
5 күн бұрын
Not that I'm aware of.
Help. How to creat a button to work on an iPad. I can do one on my laptop no problem
@MyOnlineTrainingHub
Жыл бұрын
iPad doesn't support Slicers or form controls, sorry.
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
@MyOnlineTrainingHub
4 ай бұрын
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
@shoppersdream
4 ай бұрын
@@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
@MyOnlineTrainingHub
4 ай бұрын
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.
Greetings. Is there any way to prevent broken links if a list with links is sorted? Thanks.
@MyOnlineTrainingHub
2 жыл бұрын
Not sure exactly what you mean, but maybe you could try using INDEX to find the link you need.
@saimunngai6392
2 жыл бұрын
@@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.
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
2 жыл бұрын
I suspect the colleague doesn’t have access to the location you’re linking to.
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?
@MyOnlineTrainingHub
Жыл бұрын
You can't insert a shape on grouped sheets/tabs.
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?
@MyOnlineTrainingHub
Жыл бұрын
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?
@MyOnlineTrainingHub
3 жыл бұрын
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.
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.
@MyOnlineTrainingHub
Ай бұрын
You could record a macro that opens the Insert Hyperlink dialog box and attach it to a button.
I just put hyperlinks on the text
@MyOnlineTrainingHub
3 жыл бұрын
Yep, that works too, but it's not as pretty ;-)
@matthewv4170
3 жыл бұрын
@@MyOnlineTrainingHub I agree but it takes a quarter as much time and you can make them dynamic.
Looks great, but... wouldn't be easier just to click on the sheet page...
@MyOnlineTrainingHub
3 жыл бұрын
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.