Use workbook protection to prevent use of sheet commands

- [Instructor] Excel has a number of commands related to the worksheets within a workbook. If you right-click on a worksheet tab, you see quite a few choices here. Now we're not going to go through every one of these, but there certainly could be times when you say, I don't want all those commands to be visible. Maybe it's a situation where this workbook is used by multiple people, not at the same time, but at different times. Possibly this next worksheet over is something you're experimenting with. That data might be confidential, semi-confidential, maybe you don't want that to be visible. Lot of people are familiar with the idea that you can right-click a sheet tab and hide the sheet.

Okay, well couldn't someone else who's looking at this, right-click and unhide? Sure could. Well, maybe you want to inhibit that command as well as some others. On the review tab on the ribbon, there's a choice for protecting the sheet and another one called protect workbook, as the pop-up tip suggests. Let's choose it. And let's provide a password. Now maybe I'm the person that's in charge, so-to-speak, of the workbook, I'm allowed to do this, I've given instructions to others who work with this that they're not allowed to use this feature.

But I'm going to use it. And I'll put in a password. And I probably won't tell anybody unless there's one other person I might. But you know how that works. So that feature is in effect now. If I go to different tabs, and at a later time, come back to the review tab, I might be reminded because that icon is on a gray background. So I could be the other person, I'm going to right-click on one of these sheet tabs. And look what happens. Most of these features are inhibited. I can't get to them. Now I wouldn't even know if there is a hidden sheet or not because the hide feature is not selectable.

And neither is unhide. I can't change the tab color, I can't insert or delete, rename, any of the others that are gray. Now there is another way, of course, to insert and delete sheets. We could go to the home tab, far-right button, and we have a choice here for insert and what do we see? Insert sheet is inhibited. And how about delete? As you would expect, same idea here. Now, unbeknownst to you, another feature too, that's inhibited when this is done, and you may or may not like this, but this will set the stage for other things you might want to consider doing, if we go to the insert tab, notice that we don't have the feature called recommended pivot tables.

That's kind of strange. Why wouldn't we get some recommendations? Perhaps it's related to the next thing I'm going to show, let's say that we do want to create a pivot table based on this data, I'm going to click pivot table and I want to put this on a new worksheet and that is the default setting. And that's what normally we see here. That's the automatic selection. What can't we do here? We cannot put this on a new worksheet. It's got to go on the existing worksheet. So that's something too that we want to be thinking about. Maybe I did want this on the worksheet, going to go way off to the right here where I've got some empty space but I can't put it on a different worksheet.

So I'm going to put it in cell AH, click OK, and scroll to the right and build the pivot table real quickly with a few features that I want. And just to make sure that it's gotten started, I'll just double-click department, and maybe we'll just track compensation, that's a numeric field. Those are the salaries. And although we're not seeing much now, I'll put status here. Anyway, we've got a pivot table on this worksheet, maybe to make this more complete, I'll put in department there, there we go. So that looks okay. We'd format it a bit later and so on.

Could we move this to another sheet? Could we select the pivot table? How about going to the analyze tab and off to the right here, we can select the entire pivot table, that's one way, how about Control + C? Now we can put this on another worksheet but can we insert it on a new worksheet? Well, we can't get a new worksheet here. And something else we can't do. If you recall, you can insert a worksheet normally by right-clicking, and going to insert, or possibly on the home tab we can go to insert.

But there are two other ways that we can insert and how are they going to work now? There's a button to the right of the last sheet tab, now you can see here, it's grayed out too, that's the plus button. Now that too would insert a new sheet, just like the other two options but they're all inhibited, they're all shut down. And they all, if they were working, would put a new sheet to the right of the current one. There's one more way to insert a sheet, we can't do it either. It's Shift + F11. That will normally allow us to put a sheet to the left of the current sheet.

So on the lower left corner, we see HR. If I press Shift + F11, what happens? Nothing. I think you can see, you might or might not want this feature but the way we inhibit most of these choices, in effect, turn them off, is by going to that command found on the review tab called protect workbook. I applied it earlier with a password. I want to remove it now. There's the password, I know it. And now we could, for example, press Shift + F11, I'll do it now, a new sheet will appear to the left of HR, we see that at the bottom of the screen.

Or if I wanted a new sheet to the right, I'd press the plus button we see off to the right. So quick ways. So different approaches here as to how we want to control the creation of new sheets or prevent the creation of new sheets and using other features related to sheet tabs in our workbooks.