Learn how to use Excel workbook protection to keep unexpected or unwanted changes from happening.
- [Instructor] On this worksheet called FindFormulas, I haven't finished it just yet, and it's in disarray. But it's some semi-confidential data I'm working on regarding pricing techniques. Now, maybe this is a workbook that others use. You can hide a worksheet. In fact, if you right click any sheet tab, as I'm doing right now, you will see you can Hide, and if Unhide is selectable, one or more other worksheets are hidden. So, I'm going to click Unhide. There's another worksheet out there already that we're not seeing. I can click OK to unhide it, and there it is.
Now, here too, these are projections for the following year. Maybe we don't want this to be visible, and yet we're in an environment where different people can access this. Now, I think you know, people who understand Excel and are familiar with hiding and unhiding can get to locations like this simply by right clicking a tab and unhiding. So, we're not talking about heavy duty protection, here. But you can protect a workbook. If you go to the Review tab in the ribbon, and particularly if you've seen the prior movie, you know about how you can protect data on the current worksheet by way of a feature called Protect Sheet.
But the other feature to the right is called Protect Workbook, and you see the description. Keep others from making structural changes to your workbook such as moving, deleting, or adding sheets. So, in this example here what we'd like to be able to do is to hide this FindFormulas sheet, and maybe that Budget Projection sheet that I just did an unhide of. Now, you can unhide only one sheet at a time, but you can hide multiple sheets at the same time. So, let's hide these sheets. Budget Projection is one of them, and the other one I want to hide is called FindFormulas.
So, I've got the control key held down. I'll click on that sheet tab as well, and then right click either of those tabs and hide them both. Now, if I save the workbook and others have access to this workbook, couldn't one of them just right click, gee, something is hidden, I think I'll unhide it. Surely, someone could do that. If you don't want them to see that data, you can protect the workbook. And this protects the workbook for structure, let's give it a password here.
And you'll be prompted to provide it again, which you'll do. You'd save the workbook. When it's in this state, if you happen to right click any sheet tab, you'll see nearly every feature here, not all, but nearly every feature here is unavailable. And you don't even know, necessarily, whether there is a worksheet hidden or not, because Unhide is not selectable. And so, you can also see while we're here that you cannot insert, or delete, or rename sheets, you can't move sheets, you can't copy sheets.
So, the Protect Workbook command is really about the structure of a workbook. And the other person who might be a little bit curious and clicks this button would, of course, have to know the password to unprotect the workbook. So, it's not clear necessarily to the other user if there are any hidden sheets or not. The user can only get to these two sheets, can't create new ones, can't move these around, and so on. When the workbook is under your control, of course, you can come back to Protect Workbook. And if you need to get to the data to make changes, you'll provide the password, click OK, and now by right clicking a sheet tab, you will be able to unhide.
Remember, you can only unhide one sheet at a time. Maybe it's the Budget Projection sheet, this one right here. So, that one is now unhidden. So, protecting workbook has a role to play in the way you work with files and the way you prevent others from making changes to particular worksheets simply by hiding them, because they can't get to them.