Cool Tricks with Excel Hyperlink Buttons
Vložit
- čas přidán 1. 08. 2024
- 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 - Věda a technologie
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!
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 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.
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!
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 😊
Loved this. It made a report I designed look much more professional. Thank you!
Great to hear, Tom!
Thank you for the information Mynda. All is perfect, as always!!
Thanks, Ivan 😊
Love your videos very user friendly, easy to follow along and incorporate into day to day work activities.
So pleased you like them, Torey!
Brilliant, Mynda.
"Rinse and Repeat" 😊❤
Glad you liked it, Ian! 😊
Great video! Perfect timing to see this as I am searching for the exact tutorial! Subscribed! Keep it up!
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
I agree, Patrick!
Thanks for all the tutorials and examples.
Glad you like them!
Incredible that I didn’t think doing this before 😁😁😁. Thanks Mandy 😉
Glad you'll be able to make use of it :-)
Thank you. Haven't seen that for awhile. Awesome refresher for me.
Glad you enjoyed it :-)
Very cool idea Mynda! Thanks for sharing! 😊👍
Cheers, Vijay 😊
This is great, thanks Mynda!
Cheers, Chris!
Thank you so much. You are a great teacher!
Glad you think so!
Thanks Mynda, very elegant solution
Thanks, Dianne!
Very educative !!. Thank you Mynda
Glad you liked it 😊
Wonderful video! Explained all uses very well! Thank you!! This is a lifesaver!
Glad it was helpful!
I Have so many videos to see, you are an amazing teacher!
So glad you liked them 🙏
Hi Mynda. Some great tricks and techniques here! Thanks for sharing :)) Thumbs up!!
Cheers, Wayne!
Hi Mynda!Really Like This Super Cool Technique For Hyperlink Buttons...Thank You :)
Cheers, Darryl!
Lady, That was fantastic!!!
Thank you so much!!
Very useful, i liked. Thank you Mynda...
You're welcome 😊
This video is just in time 🔥🔥🔥 Thanks 💪🏾💪🏾
Great to hear!
You are always Amazing!!!
Thanks for your kind words :-)
Absolute brilliance
Thanks so much, Adeoye!
Very useful. Thanks sharing this with us.
My pleasure, Patrick!
Thanks for the great work
Glad you enjoyed it
Thank you for sharing your videos..
My pleasure!
Simply Great !!!!!🙏
Glad you liked it!
Great - your Defined Name has solved a problem I have been struggling with
Great to hear!
thank you great video
Glad you enjoyed it, George!
Amazing !! thank you
My pleasure 😊
Good teacher 👍 student from India 🇮🇳 love you 🙏
Thanks so much 😊
Nice video thank you for your hard work 👍
My pleasure, Nader!
Those are some sweet tricks thanks!!!
yes
Glad you like them!
@@MyOnlineTrainingHub yes i do thank you!
You are just extra ordinary, fantastic, mind blowing madam.
Thanks for your kind words 😊
Thank for sharing. It is very useful 👍👌
Wonderful :-)
EXCELLENT. THANKS
Glad it was helpful 😊
This is brilliant
Glad you'll find it useful, Richard!
Thank you) I use buttons with hyperlinks too))
Great to hear, Andrew!
Great video as always :)
Thank you! Cheers!
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
Nice Job )
Thanks so much!
Very useful
Cheers, James 😊
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
Used this trick way back in 2015
Me too! It never gets old 😊
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: czcams.com/video/LLxRQJlQ8A0/video.html
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!
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 :-)
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.
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 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: czcams.com/video/_ukgNk3-iU4/video.html
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).
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.
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
#marvellous
Thank you!
Nice
Cheers, Wouter!
Can u help with lazy loading web page , as it returns only 20 elements
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. (:-|)
:-)
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
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.
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
Mynda,
How can we refer to any table by using hyperlinks?
If you give the table a defined name then you can reference it.
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.
Wow
Glad you liked it, Yogesh!
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??
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.
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.
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
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.
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.
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.