Join Dennis Taylor for an in-depth discussion in this video Hide and unhide worksheets securely, part of Excel Tips Weekly.
- [Voiceover] There are times when you might want to hide a worksheet. One rationale could be, this is a workbook that different people use. I've been doing some experimentation on this sheet, or that sheet, I don't want others to see this. I'll simply right-click the sheet, and choose hide, and it's no longer visible. I might save the workbook. When I open it, it's gonna look exactly the way it looks right now. There's still that hidden sheet. I might want to hide multiple sheets. Maybe I want to hide all these sheets from random right here, all the way over to concat, so I'll click the first one, with the shift key I'll lick the last one.
All of those are selected. I'll right-click on the first one, and hide all of these. I'm hiding five sheets at once here. At a later time, I might want to bring back those sheets. So, right-click on any sheet tab, unhide, and although you can hide multiple sheets at the same time, you can only unhide one sheet at a time. So, I'll choose randbetween, click okay, right-click on hide, and so on, and so on, and so on, to get these back one by one. That's a little tedious if you've got a lot of hidden sheets here, and sometimes that could be the case.
Again, think of different rationales, different reasons for why you might want to hide a worksheet. Now, there's another thought here. You can hide these, but others can get them back, and maybe you don't want others to necessarily get them back. So, another person using this might say, gee I wonder if my friend Bill has hidden a sheet here, so I'll right-click and unhide, and there are the sheets that are hidden. Hmm, I guess I'll open those, and see what's in those. So, you could say, well I want to make sure that table data never gets seen by anybody else.
Now, right now, it's visible by way of the unhide button. So, by pressing alt + F11, you enter the world of visual basic, and this is a toggle keystroke shortcut, it'll take us back into Excel as well, alt + F11. Right now, I've got one workbook open. Here it is. Here are the names of all the sheets. So, as I click on a sheet here, notice the lower portion of the screen, we've got a Properties window. If you're not seeing this Properties window below, go to the View command in the menu above, and choose Properties Window, or press F4.
So, as I click this, for example, first sheet called Gant1, bottom of the screen says sheet visible, in the Properties window, the last attribute here. As I click the next one, or simply move with the down arrow, look at the bottom of the screen. Keep your eye on this portion that I'm circling, as I'm pressing the down arrow to go to the next sheet. Sheet visible, sheet visible, sheet visible, and here's one sheet hidden. That's with the alt shortcuts. Now, in the lower portion of the screen, in the Properties window, here's the drop arrow for sheet hidden, and here's a choice called sheet very hidden.
So, we've made a change there, on that tab right there, alt shortcuts. I'll press the down arrow, the other sheets, sheet visible, sheet hidden, that's the table data, sheet visible, that's it. Alt + F11, we're back in here. Someone else might be using this. Someone else right-clicks on a tab, unhide, table data. Now, there's another sheet hidden, but it doesn't turn up at all. So, we've got another layer of protection there. Now, if the other person happens to know that capability, of course, he or she could press alt F11, and make the change, in other words, reverse the change that I just made, but there's another approach as well too, which has other implications too.
If you protect a workbook, and this is found on the Review tab in the ribbon. When you protect a workbook, you have the option of providing a password. Protecting workbook, for the most part, has to do with the ability to manipulate sheets, click okay, not about content. Put in that password again, click okay. Now, imagine another person is using this. That person right-clicks on one of the sheet tabs, both hide and unhide are unselectable, and notice also that you cannot insert delete, rename, move, or copy sheets on a protected workbook.
You wouldn't even know, if you were another person, whether a worksheet is hidden or not. What if you are wise to the other capability of very hidden? Press alt + F11, we're back here, and we're looking at the sheet names here, and pressing the down arrow, looking at the bottom of the screen. Now, we're likely to see a sheet name in here. If any sheet name is hidden, we'll be seeing it in the list here, and we'll say, wait a minute here, I didn't see that. I didn't even know there was a sheet called alt shortcuts. So, the other person who might be looking here, at least, could discover there's a shortcut, and at the bottom of the screen, he or she might see very hidden.
So, that person might say, well, I want to see this sheet. Click the drop arrow. Let's make it visible, and what pops up? Unable to set the visible property of the worksheet class. In other words, the other person cannot get to it. What we've shown here is the idea that the other person who's looking around could, at least, know that you have a hidden sheet, but cannot get to it. Pressing alt + F11, we're back here again. So, right now, I'm a regular user of this. Right-click, I can't get to unhide at all. I'll unprotect the workbook, and of course, I would have to know the password.
Click okay, and now if I right-click, what do we see? Unhide, table data. Where's that other sheet? Is it still out there hidden? Well, I'll press alt + F11, alt shortcuts says very hidden. Now, if I make it just hidden, instead of very hidden, and then jump back into Excel, alt + F11, right-click, unhide, now we see it. Remember, you can only unhide one sheet at a time. Of course, it has to appear on the list here. You can hide many sheets at once, and keep in mind that idea of using the feature called very hidden.
Remember, alt + F11 takes us into the world of visual basic, or back into Excel. In two different techniques here, we've seen for hiding worksheets, and you might want to experiment with this a little bit. Consider the different options as to why you might want a sheet to be hidden or possibly very hidden, using that technique that we just saw. So, different ways to establish a certain security level to your worksheets.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Excel Tips - New This Week
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.