Controlling worksheet security by allowing selected users to edit specific cell ranges

- Excel has a number of different security features that allow you to control what's going into a worksheet or what's being changed in a worksheet. If you go to the review tab, you'll see protect sheet and the description says prevent unwanted changes from others by limiting their ability to edit. There's also a feature here called allow users to edit ranges. We can setup password protection on certain ranges. In other words, in a worksheet like this, for example, we could allow some users to make changes to column J but not others, and other users to make changes to column K but not column J.

This all begins with the idea that we might want to protect the sheet. We could certainly use this feature by itself and achieve a lot. When you protect a sheet, in effect you're saying, changes can only be made in certain cells. Anybody who can open this file- because when we protect a sheet we usually provide a password- anybody who can open this file can make changes to certain cells. If you click on any cell in an Excel workbook, and go to format cells- get there by way of right click, that's one way- go to format cells, the protection tab isn't always the one that's active, but if it is, or if you click protection, you will see the cell is locked.

Locking cells has no effect until you protect the worksheet. At the moment that might seem a little bit strange. The native state of all Excel cells is they are locked. Let's unlock all of them. Click in the upper left hand corner. You can also get to format cells by way of control 1, there it is. Let's unlock all cells. Click okay. If we were to protect the sheet right now, in effect we would be doing nothing. With all the cells unlocked, you can make changes anywhere.

But let's say, in general, we want people who are using this worksheet to be allowed to make changes to these cells as needed. There are formulas in column H. We don't want them to be making changes there. In fact, maybe I, the person in charge, is saying I don't want anybody touching column H. For the moment let's say we don't want any changes to be allowed out here in these cells. So once again, we'll go to format cells, control 1, lock those cells, click okay.

Another option could have been to hide these columns but maybe it's necessary to have that data visible so when we're making changes here we make sure we're looking at the right particular row or person. So, let's protect the sheet now. For the moment we're not talking about allowing users to edit ranges. Let's protect the sheet, provide a password, and what does this mean, only certain people can unprotect this. At certain times you might want to consider allowing people to insert columns and rows.

We're not allowing that here unless we check these boxes. If we don't check format cells, columns, and rows here, people will not be able to add colors, make cells bold, make columns wider, any of those kinds of things. We're simply going to click okay here and provide the password again. There we are, it's now in a protected state and here's the phrase unprotect sheet that we see at the top. So, anybody can make changes over here. Maybe Tito here has been offered a full-time position or contract position maybe instead. We'll just copy that up here.

We made a change there. Could have typed it, copied it, that's fine. If I somehow think I could change that number by typing, well, can't do it. I tried to type a nine, it's on a protected sheet. You've got to unprotect it. I don't know the password. Let's say I'm a different user. If I attempt to unprotect the sheet, I must know the password. Same thing with compensation here or job rating. Change the compensation to be 80 thousand. As soon as I start typing something, I can't do that. So we see what's in effect right now, but let's go back and unprotect the sheet, and you must know the password, and now set up a scenario whereby we will, selecting column J first, allow certain users to edit this range.

Set up password protection on ranges, no, and I'll just give it a name, why not compensation. Refers to cells in column J. The password for that is going to be pw-comp. Now only certain people will be told that, maybe only a couple of people who will be allowed to make changes to those compensation levels. Click okay and that password was pw-comp, click okay. Let's set up another one first by closing this or clicking okay, then select column K.

We could imagine doing this possibly with other columns, column I maybe. But for column K, allow users to edit ranges. Now, the people we're going to tell this password to are probably going to be different than the ones who know the password for column J. Set up a new one here. I'll simply call it JR, job rating. Refers to cells in column K and the password is going to be pw-jr. Click okay, provide that password again, pw-jr, click okay.

We can protect the sheet starting from right here or go close this dialogue box and go back up into the ribbon menu system to use it up there, either way. Protect sheet, and again, password for unprotecting the sheet is there. Okay, so it's now in this state. Now, maybe I'm one of those users who's allowed to make changes to the compensation. I can make changes over here like anybody. I can't make changes in column H here.

Just like before, I'm going to try and change this to be 80 thousand, I'll type an eight, and the pop-up dialogue box unlock range, a cell you are trying to change is password protected. Enter the password to change this cell. I know the password, pw-comp, okay, I can go ahead with my changes and I can continue to make changes in column J without being prompted again. Now, I'm a little bit curious, I'm saying, gee, I know Tim Short, his job rating shouldn't be a one, it should be a five.

Well, I don't know the password for that so I can't make the change there. If I did of course, if I were the person allowed to do that, I would know the password but I wouldn't be told the password for column J. That's assuming you've got a scenario where different people are allowed to make changes in different locations. Now, maybe I'm a sly type and I'm saying, well, why don't I just unprotect the sheet here. I'm going to go to unprotect. But the person in charge didn't tell me how to unprotect the entire sheet so I just can't get into that data.

Now, of course, I'm sort of making fun of the scenario that suggests that people are being devious about this. I think for the most part, we're talking about the idea that we want inadvertent changes to be eliminated. We're only allowing certain people to make changes in column J. Those people know the password for column J. Certain other people can make changes here. They know the password for that column. So, using the two features together, protecting the sheet- right now it is protected so we see unprotect here- or using the other feature, allow users to edit ranges, we can come up with different scenarios for providing security for a worksheet like we're seeing here.