3 Methods to Unhide All Sheets in Excel (& how to hide)
HTML-код
- Опубликовано: 23 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Good news! If you have Office 365 you can now unhide multiple sheets at once! YAY!
How can you un-hide all Excel sheets at once? Hiding many worksheets at once is easy in Excel. Click on a sheet, hold down the control or the shift key, click on the other sheets you'd like to hide, right-mouse click and select hide.
Now what if you wanted to unhide all the sheets in one go? If you right-mouse click on any sheet name and select unhide - a new window opens with a list of hidden sheets. Unfortunately you can't use the control key or the shift key to select multiple sheets to unhide. You have to unhide tabs one by one.
Find out 3 methods you can use to un-hide:
1. Use Custom views to quickly unhide all sheets - no Excel VBA is needed here
2. Use the Immediate Window in VBA - type in or copy and paste a very simple macro
3. Save the macro in your Excel personal macro Workbook. You can add a button for the macro to your Quick Access Toolbar so it is available for ALL your Excel workbooks - also the ones that are not macro-enabled (just xlsx).
⯆ Blog Post: www.xelplus.com/3-methods-to-...
LINKS to related videos:
Excel VBA FOR EACH Loop: • Loop Through Cells Ins...
Excel VBA full playlist: • Excel VBA & Macros Tut...
★ My Online Excel Courses ► courses.xelplus.com
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Good news! If you have Office 365 you can now unhide multiple sheets at once! YAY!
If you don't have Office 365, you can download this Excel Unhide Utility for free from the description of this video: ruclips.net/video/1wcF-qCSgfI/видео.html. In the video, I show you how to install it.
Thank You For that I try to Get this tool
Hello, will the above methods will also apply to windows10?
Its amazing how everything is so simple with you ! Many thanks !!!
I love the simplicity od custom views! Thanks Leila for hide-unhide fun!
Very nice tutorial and clear explanation! I suppose 2nd option, using immediate window would be potentially the simpliest way =)
Excellent tip, loved both options, had used the second one first but plan on setting up a personal worksheet, subscribed
I'd love to hear your suggestions on restricting worksheet access (e.g., if an employee or client should not have access to the raw data of a particular worksheet or group of worksheets in a workbook that is being used by multiple people). I've seen some helpful tutorials for the VeryHidden feature but I wonder if there might be a more secure way with password protection. Currently, password protection doesn't seem to stop people from viewing the data (if they unhide the worksheet tab). Thank you for sharing this, your videos are always so helpful! :D
An excellent video from Leila! The first method is so simple to use. Thanks Leila
What an interesting video. Love the immediate window idea. Always wondered how to send . Makes sense though... I use this syntax in a module like this also... For example...
Dim rng as Range:set rng = sheet1.range("A1:A10")
I like the tip/technique for adding a macro button to the quick toolbar section.
Very handy - thanks
Always enjoy Leila's videos and this one in particular is really useful. I like the macro solution because I don't need to ALT F11 and go to notepad each time - very helpful, so thank you!
Glad it was helpful!
Thank You. Great tutorial as always!
Very nice to see multiple possibilities for a single problem.
Thank you! I am glad you like the tutorial.
Third option opens a world of possibilities for me, thank you very much.
This is good one. Mostly people are wary of using macros; which include me as well. Therefore the one I personally like is custom view; because once the same is defined; Excel takes care of the unhide task by itself. All these years I am habitual using short cut key ALT+O+H+H for hide and ALT+O+H+H+U for unhide. The flip side is you need to logically by-heart the key stokes which can be practiced. Thanks Leila for upload
Thank you Sachin - agree, the custom view can come in really handy. Also thank your for the shortcut tip :)
VBA is the way I do it. Hiding/unhiding all very hidden sheets. Password protected as well. Great work Leila :-)
Literally saved so much of my time, thanks a ton!
Method 3! Wow this saves me so much time! Thank you.
Wonderful video as always.. thank you. btw, the third method is what i'll be using
Nice tips. Thanks!
Excellent! Thanks Leila.
Thank you very much. Very helpful.
No. 3 wins !!! Thanks for sharing.
Thank you so much for you videos:)
Excellent video Leila
Third method is my favorite. Thx. 😀
I like the first option to proactively provide a way to unhide the sheets. However, I'm too deep in this workbook to start over; so, the 3rd option - creating a macro - genius! And your step by step tutorial was easy for me to follow! Thank you!
Glad it was helpful, Beth!
method 2 worked Awesome . thank you
Thank you Leila! The third one is my favorite :-)
Thanks Leila, you helped my again. Your a genius! I prefer the third option.
This is cool and thank you Leila for always putting out content...Thank you really.I know you may not have time to read this ..But i really appreciate the effort yo put into everything you do...to make this channel value-adding. Thanks
Superb. Thanks for teaching.
Great Vid 👍
Third one is my favorite.Thanks for all!!!
Thank you , have learnt a lot from your channel.
This is great. hope to see more sharing on what immediate window can do in excel
Perfect explanation:: can you also help me with this, when i try to unhide the sheet it should ask password for unhiding...
Excellent, as always..
Thanks for the trifecta of unhiding fun : )
You're 3x welcome :)
Wow!!! second option is soooooo cool. Thank you so much!
So really helpful.. thanks alot for Ms Leila Gharani...
Another great video. Thank you 🙏
I used the macros method for a long time and it was very useful. Unfortunately I was facing problems like random crashing of all opened Excels when opening multiple workbooks. I realised it once I reinstalled MS Office. Don't know exactly what caused it though. I'm forced not to use it now. Nevertheless I am grateful to @Laila for sharing her ideas with us. Very inspiring.
Super awesome!! Thank you!!👍👍
The 3rd one is very useful! :)
extremely grateful for this video
Thanks for your making this video. Tip 3 is very useful to me...
Thank you for a great and helpful video. I like method #3 👍
Big help! Appreciate it.
My pleasure, glad to help.
Boy have been waiting for this option. Thanks in million
You're very welcome Mohammed :)
Thanks for the Video, im looking to get alot of dummy data to learn how to use excel more, do you know anywhere to get it?
Hi Leila! How Are you? I wonder if you have any tutorial video on how to auto hide rows (which is not selected) based on users selection using drop down list. Only the selected list will appear. Thanks you so much!
Excellent!
This is super helpful. God bless you!! That's the best.
Nicely explained...
Nice Work
Thanks for this trick.
Oh my gosh - this is so helpful. Thank you !!
You're so welcome, Bill!
Thanks you very much very use full information
Simple and super technique. Thanks a lot.
My preference is for personal macros, but it was interesting to know about Custom views option too.
Hello, awesome video, thanks a lot!!
But what if I want to see the worksheet, but I don't want to see the tabs only, can this be done with vba?
Hi Leila.. thanks for the video on un-hiding sheets. That process is always a chore to do one by one. I wrote a small Sub and put it on my QAT with the UP arrow icon to signify to un-hide all worksheets with a click. I never considered doing it on the fly using the Immediate Window.. what a great tip. In that regard, another issue is when I get a workbook from a client with different zoom levels on the worksheets and also with the cell pointer in different locations on each sheet. Before getting started, I like to set all sheet zooms to 100 and move the pointer to cell A1 on each sheet and then start on Sheet1 as the active sheet. In the spirit of your un-hide lesson, I came up with the following to use in the Immediate Window: For Each ws in Worksheets: ws.Activate: ActiveWindow.Zoom = 100: Range("A1").Select: Next ws: Sheets(1).Select. It even works on hidden sheets. While I could put this in a Sub and on the QAT, you showed that it is quick to open the VBE and IW and then type in what you want to do on the fly. Thanks for that tip and the insight to put it to use in other ways. Thumbs up!
Thank you Wayne. That's a great tip regarding the immediate window to set all sheets to A1 and adjust zoom level accordingly! I will add your tip to the blog post. Thank you very much :)
Thanks Leila.. glad to contribute. Credit to you for teaching me how to think about these solutions and put your lessons to good use in different ways. Thumbs up!
I like your videos so much . really I have learnt alot .. thank you
Thanks for another great video - I have learn’t so much from your tutorials my colleagues think I am an excel expert. I have been using the rank function with some data and then vlookup to sort in into the order I want, but where there are duplicate values I only get the first result returned
Thank you Graham. You can use a unique rank - I show that in this video when I prepare the data for the chart: ruclips.net/video/U3U5A0BCr9A/видео.html minute 12:05 I show the unique rank method.
Hi leila, can you please make a video on how to recognize a table elements in pivot table and fomat them...
legend - you saved me soooo much time - thanks so much
Nice and useful, thank you
Great help maam
You are like awesome! I work for the largest healthcare company in the world and loved this feature for Excel. I chose #3 as my method of opening up all sheets, thank you so much!
That is awesome Mike! Glad it was helpful.
liked the macro on. will give it a try.
wow u r the best , ur tutorials are super helpful, the way u present them is wooooooooooooooooooooooowwwwwwwwwwwwwwwwwwwww
Amazing Staff :) :)
Thank you so much
Superb ❤
Great. Thank you.
Thanks for this, Leila! I liked the third option, creating a custom macro/button to activate it. Not sure why MS wouldn't build in this function into their own software.
True, sure would be useful.
Thanks for the tips. it was very helpful as usual. I'm trying to analyse some data that involve more that one factors to see their effects on each other; I found this Correlation function that looks like will help with this task. would you please, post a video of CORREL function, how and when to use it? Thanks in advance.
Hi Ka Aa - thank you. I haven't used this in Excel - but I'll take a look and see what I can come up with....
Superb mam, thank you
Life saviour. I have a model due tonight and I somehow hid all sheets but could not make them visible. The VBA method worked. Thank you!!!!
This is just great! Thanks a ton!
You're very welcome Ankan. Thank you for your support.
Thx alot for your information
Good to know. Thanks for sharing it.
How to hyperlink a document through excel and if I change the location of the file then the hyperlink shod be automatically updated can you please guide me that.
Thanks.... I love you.... I waiting your next video
Thanks a lot, Mam ! If any tutorial for hide command i.e. formula in columns and rows etc. pl. told me ! i've forgat this command !
Hi Leila, need your help; how can we hide the connections I have created in my report. I don't want receivers to know how my Slicers work?
Great! , I didn't know we can hide worksheets !!
I just found out that there is a visual basic editor feature in exel ... it's really very helpful, because there are dozens of hidden sheets, thank you very much for the knowledge..
Thank you very much from Indonesia 😄🇮🇩🇮🇩🇮🇩
Thanks again Leila, you saved the day!
I'm happy to hear that Hector :)
Yes I'm using unhide one by one..but your VBA method is awesome..that wl help a lot..you are just amazing..thanks a lot..😚😙
That's great that you can apply this!
Thank you! I used the 2nd option and then BOOM! Tadahhhh! Fixed
Glad it helped!
Thank god for this, life saver
I like both methods
I like the 3rd option with the easy access button Thank you, Leila.
Agree - This one comes in really handy if you need it often...
You are wonderful
Never know that I can execute multi-line code in the immediate window.
Colan trick will do wonders for me will debugging VBA.
Thank you!!
This is very useful. I would use this in conjunction with a short macro that I have that deletes hidden worksheets. I can envision two macros in my personal workbook, one to un-hide and also the other to delete all hidden worksheets.
That sounds great!
Thank you
Nice explanation.
Glad you like it.
Thanks Ms. Leila