Join Dennis Taylor for an in-depth discussion in this video Unlocking cells and protecting worksheets, part of Excel 2016 Essential Training.
- Excel has any number of different protection schemes. And when you work with Excel sometimes it's just a simple question of hiding data and that's good enough. We're looking at a worksheet called worksheet protection, it's in the workbook 11- Security&Sharing. Let's imagine you're an HR manager and this is a list of all the employees. And you've got a couple of assistants and from time to time some changes need to be made. Maybe you're looking at a sheet of paper and you're making some marks on it. This needs to be changed that does, whatever. Now the people who work for you don't necessarily know Excel that well.
They might not need to know it too well. And you're concerned about some changes to this file. There's a formula in column E that calculates Years, based on the hire date. You may or may not have seen how to use a function called DATEDIF, it's not too critical. But on your sheet of paper, this hire date needs to be changed to 03. Now someone looking at this might say well okay, then that means that this should be an 11. You could imagine a naive user saying okay, I'm going to change that to another three, I have to change this to an 11, I'll just type in the 11 first.
What will that do? That'll wipe out the formula. Now you want to have this information visible but you don't want anybody changing this. So one thought might be, there should be a way, and there is, to say you cannot change this. Another thought, job ratings are pretty sensitive and not everybody should be able to see them. Maybe you don't want these to be seen. We could hide the column and that might be good enough. Would the other person doing the updating here know about to unhide? Well maybe, and probably that person isn't malicious and probably wouldn't be doing that.
But on the other hand, you're concerned about that a little bit. So we could hide column H. Salaries maybe are sensitive too, and we need to see them for reference purposes here. But we want to make sure that whoever is working with this is not changing these. So I've got some different thoughts here. You can probably imagine other little scenarios about how you want data to be seen and not seen. Now in every Excel worksheet, unless you've made changes, every cell has the attribute of being locked.
If you right click on a cell and go to Format Cells or if you press control 1, either way, go to the Protection tab, probably the cell is locked unless you've made changes to it. Now here's what we'd like to do, We'd like to set things up in this worksheet so that whoever's using it, at least for a time, is allowed to make changes pretty much everywhere except no changes in column E, no changes in column G, and we don't want column H even to be visible.
We want to be able to hide it and prevent the other person or other people from unhiding it. So let's begin with the process of unlocking all cells. Click on the upper left corner, we can again press control 1 or right click Format Cells. Now notice the description here, locked means, as it says here, it has no effect until you protect the worksheet. That's what we're on the path to doing. So it doesn't mean anything yet. But we're going to unlock everything at first. All cells are unlocked.
If we somehow turn on protection it wouldn't mean a whole lot. But we're now going to lock column G. We don't want anybody changing anything here. And also column E. Now we do want to allow changes in column D, and that will cause changes to these formulas, in other words the formulas will react with the changes here, but we want to make sure that nobody types over our formulas here. So column E and column G, we don't want to be changed. So we'll select these in either order, with the control key.
And we're going to lock these. Once again, control 1 or right click Format Cells, we want these to be locked, click OK. We don't want column H visible at all, right click and hide. Now we can protect a worksheet, you can also protect a workbook. Two different tasks, two different capabilities. For the moment we're only concerned with this sheet right here, WorksheetProtection. We go to the Review tab in the ribbon and choose Protect Sheet.
Prevent unwanted changes by others by limiting their ability to edit. Now we're going to provide a password but we've got tons of options down here that we might allow the other person to use. Inserting columns, formatting columns, that sort of thing. For the moment, let's leave most of them unlocked. Now at a minimum we must allow the other people who are going to be using this to select unlocked cells, that has to be checked. We might or might not allow them to select locked cells. If we uncheck this, they wont even be able to click, for example, on column E, any cell there.
For the moment let's leave it that way. Let's provide a password, pw I'll use, I use that all the time. Of course you want to remember it. It's going to come up for us again. And of course in real life you're going to use whatever password you want. And need I say, don't forget. So we're now in that mode. And by the way, a visual giveaway if worksheet is protected, go to the Home tab, and most of the features are grayed out. Some other features are going to be grayed out too on other tabs. Like that's the way it looks in general. Now imagine I'm the other user here.
I'm about to adjust this to put in 2003, and while I'm at it I'll just go over here and make this be 11. But I can't click here. Can I tab there? Nope I can't even click there. Lisa's my friend, it would be nice if I could make her salary be 75,000. Can I click there? Nope, and even if I could I couldn't change it. But I can't even click there. And it looks like there's a hidden column here between G and H, what's over there? Highlight this. Right click, I can't even right click. I can't select them both.
Can I somehow click in here, right click? Nothing about unhide. Could I click in the upper left corner here maybe? And select the whole worksheet. Can't even do that. So in all three instances here, I can't change this either by mistake or maliciously, or this, and I can't get to that hidden column. Now to unprotect this, we go to the Review tab, and choose Unprotect. And of course, if there's a password we've got to know it. I do know it, and unprotect it. Now we could make changes.
But you'd have to, of course, know that password. Now I'm going to start this again, this time make a slight change. And this time allow all users to select the locked cells. Provide that password again, enter, provide it again, and enter. Same kind of scenario as before, but this time I can click here. But as I start to type, I can't type anything. The cell you're trying to change is on a protected sheet. Unprotect the sheet. I can't do that though.
This is my friend Lisa, I'm going to change her salary to be 75, woops, can't do that. Anything I type, it's stopped immediately. How about that hidden column? Can we drag across here? Right click, unhide isn't selectable. How about clicking the upper left corner? Right click along here? Can't get to unhide. So here too we've prevented although slightly different in this case we've allowed users to click these cells, but we're not allowing them to make any changes. This is not the entire set of security features in Excel, but it does point out that on a worksheet basis you've got some control over how you can prevent data from being entered.
Remember this is just for the current worksheet. And when we're done or when the other user is done, he or she may be saving the file, that's fine. It's back in your hands, unprotect the sheet, you know the password, and so on. One of many different scenarios you might imagine using these schemes found initially by way of Protect Sheet, But before that remember the whole issue of how to lock cells and unlock cells is important.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros