Protecting worksheets and workbooks

- [Teacher] If you wanna provide better security for your data, and possibly if you've got multiple users using the same workbook, you wanna be aware of two commands. One for protecting the workbook, one for protecting the worksheet. We're looking at the file 11-Protection, and the very first sheet is called Worksheet Protection. Let's first talk about protecting a workbook. Both of the commands that I mentioned are on the review tab in the ribbon. Protect workbook. And this is primarily about the structure of a workbook. Moving, deleting, and adding sheets. If we protect a workbook, you cannot insert a new sheet, move a sheet, delete a sheet, and as we'll see in a moment, practically all commands available as we right-click a tab are going to be inhibited.

Protect workbook, need to provide a password. I know it, I might tell certain others, not others, and if I save the workbook it stays in this state. Right now I'm about to right-click on the current sheet tab. Notice how many of the commands are grayed out. Can't use them. And even if I were curious and thought, I wonder if there's a hidden sheet? I don't even know that necessarily, because I can't get to those commands. So while it's in this state, can't do this. If I click protect workbook right now, well if I don't know the password, I can't change that status.

But of course, I do right now, and some point I will un-protect the workbook. Now, protecting a worksheet probably is the command that you're more likely to want to use. Gives us control in a lot of different ways. And it might begin with something that doesn't seem related at first. If you right-click any cell, in Excel, and go to format cells, if you go to the protection tab, and by the way it's not always selected but, we see here the cell is locked. Locking cells has no effect until you protect the worksheet.

So probably all the cells in this worksheet, as in most worksheets, it's locked. I'm gonna begin by saying the following. Let's unlock everything. Because let's say we've got a situation where we want others to be able to make some editing changes to clean up this file, but we wanna be careful about not letting them make changes in certain areas. So let's begin by clicking the upper left corner, that selects the entire worksheet, let's right-click format cells, go to protection, unlock. Sometimes, by the way, you'll see that this box is checked, or blank as it is now, sometimes it'll be black, meaning some are locked, some are not.

We wanna make sure that's empty looking right now. We have unlocked all cells. Now we're going to lock some. Meaning we don't want any changes in the following columns. Column E has formulas. You don't need to know the details right now, but it's based on data in column D. We're gonna be locking column E. And now I'm holding down the control key. We're also gonna be locking column G. We don't want anybody typing any salary changes there. And let's say column H is maybe even more sensitive, we don't want that visible. So we've got these two columns selected, right-click, format cells, let's lock these.

But for column H, I'm just gonna right=click on column H alone, and hide it. Now, if we turn on protection, then the idea of a cell being locked or unlocked has some real meaning. Those locked cells, we cannot change. Alright, ready to try the feature. Protect sheet. And look what else pops up? A whole series of potential questions and answers about do we want to allow inserting and deleting of rows. Well we would think that out a little bit, and perhaps experiment a little bit. At a minimum, if we want other users to be able to make some changes in the appropriate locations, you must allow select unlock cells to be selected.

Let's provide a password, click okay, provide the password again, click okay. It's now in this state. And by the way, a visual giveaway if you happen to be on the home tab, or if you jumped over there, most of these formatting features are grayed out. So, imagine this little scenario. The other user is here, so this cell needs to be changed to 2004, this is years of service, you can imagine somebody saying, well, if it's 2004 I'll just make this be a 14. Woops! Can't do that. We can't type there. That cell is locked.

But we can change this to be 2004, enter. This is my friend, Jim, I think Jim deserves 60,000. Well I start to type, we can't type there. And I'm wondering what's in that hidden column? I drag across here, there's a hidden column H out there, right-click, let's un-hide it, well we can't un-hide it. So you've seen various ways to protect the data here. When this is back in my hands, or if that other curious user says, well I wonder if I could un-protect this somehow, back to review, un-protect the sheet, well of course you've gotta know the password.

And I do. And now it's back in it's so called normal state. So you've seen different reasons for wanting to protect a worksheet, and prior to that, to protect an entire workbook. Both viable tools when you're concerned about the security of your data.