Join Dennis Taylor for an in-depth discussion in this video Protecting workbooks, part of Excel 2016 Essential Training.
- We're looking at the workbook 11 - Security&Sharing. Makes no difference what sheet we're working on. But let's imagine we've made some changes to this workbook and we want to save it. And we're also thinking along the lines of some protection. We only want certain people in our organization to be able to open this file from now on. They can make changes to it, that's not the issue. We simply want to restrict who can open this file. It's stored in a folder where people have access to other files and some of those might be protected in a similar way.
So, we want to put a password protection on this file. When we go to the command File>Save As, and by the way, the keystroke shortcut for that is the F12 key. Go to This PC and this example here. Now, depending upon which version of Windows you're using, your screen is likely to look something like this. And near the Save and Cancel buttons in the lower-right corner you'll see an option, Tools. Drop arrow, General Options...
Now, there are essentially three major scenarios here. We could put a password to open and not to modify, meaning only certain people can open this, whoever can open it can modify it. A second scenario would be something along the lines of, well anybody in our group can open this, anybody who has access to that folder can open this, but only some can modify it. And the third option might be, passwords for both of these. Only some people can open this, and of those who can open this file, only some of them can modify this.
So, as you're thinking this out you might be sketching it out on a whiteboard or a sheet of paper or something but you can imagine different approaches to how you might want to protect this information. So I'm going to provide a password to open here. And possibly I can also check this box, Read only recommended. Maybe a lot of the people who open this really don't have a need to modify it but it certainly wouldn't hurt or they might have the occasional use to do that. If we choose Read only recommended, there will be a prompt reminding users that they could choose that option, for the moment I'll leave it off.
So click Okay. Remember, password to open but none to modify. We have to re-enter the password again, click Okay. We'll save the file. Replacing the previous version of it. And let's say we close the file. You can do that pretty quickly with CTRL+W. Okay, the file is closed. At a later time, someone else is opening this. We can go to File>Open or CTRL+O. If it's in a Recent Files list, it is here, I'll just click it this way.
We're opening the file and there's a prompt. We need the password. If I don't know the password, can't do much here. I do know it, okay. The file is open, I can make changes to it. Maybe I'll close it with some changes, fine. Now, second scenario might be something like this. We go to File>Save As again, remember F12 is the shortcut. This time again we're going to click the Tools option. Then General Options and instead of a password to open, let's take that off, we're saying now, anybody who can get to this folder can open the file but only certain people can modify it.
So we'll put in our password here and Enter. Put it in again and Enter. Save the file. Overwrite. Close it at some point. CTRL+W, fast way. At a later time we're going to open it. Or someone else is. CTRL+O. There's the file, we open it and password for write access or possibly open read-only. Now, if I don't know the password, but remember we said anybody can open it now.
I can simply click read-only. If I do know the password and I do intend on making some changes, probably, I'll put in the password. So I can make changes here and there, I could save it. Not a problem. Third scenario would be, and again we're going back to File>Save As, F12 key. Click that Tools option. General Options. A password for both, meaning, only certain people can open this and of those who can open it only certain ones can modify it and presumably these are different passwords, although they don't necessarily have to be.
They could be the same one. And again, you just have to explore this a little bit. And you might try this sitting down in a room either with another person or possibly with two computers jumping back and forth a little bit, testing this out to see how it might play out. Think of some of the different options we have here. I'll click Okay and put in the password again. And Save. Overwriting the prior version. We'll close this. Remember, CTRL+W, relatively fast. At a later time we're going to open this or someone else is.
Open this file. Here's the password to open it. If we want to make changes we'll put in the password. Now, we also have this option here, choosing read-only. This is someone who does have write access but doesn't need it at the moment. That person might choose to read-only and then can open a file and not make changes. But, if you do want to make changes, PW or whatever that password is, put it in. So think that out a little bit. Remember, when we're saving a file or anytime you want to make changes to this concept, File>Save As or F12 and as you're saving this, in this Save As dialog box, the Tools options, General Options and we see our choices there.
And experiment, again, with some of these other choices as well, there's so many different combinations, we can't explore them all but I think you get the general idea. It's just one more level of protection that you might consider as you work with Excel.
- 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