NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.
Now, by default all cells on a worksheet are set to “Locked” status. This means that if you were to apply worksheet protection, you wouldn’t be able to interact with any of the cells on the sheet. So once you have your worksheet designed and set up the way that you want it you can select all of the cells with which you want users to interact. (Note that you can select multiple, non-contiguous cells by using CTRL+Left-Click. The caveat here is that if you accidentally select a cell that you don’t want to be in that series you’ll need to start all over again, as you can’t de-select a cell that’s been selected in this manner).
Once you have your cells selected, on the Ribbon you can goto Home–>Format–>Deactivate the “Lock Cell” button by clicking on it (/hol). You can also select the Format Cells button (CTRL+1), which will bring up this dialog:
The Number tab is the default, so you’ll want to click on the Protection tab, where you’ll get this:
As with the Ribbon option, “Locked” is the default, so you’ll want to uncheck it. Now here you have an additional option called “Hidden”. This is an additonal feature that, when a sheet is protected, will not show anything in the formula bar when a cell is selected, as opposed the formula itself. This feature should be used on Locked, not Unlocked cells, that you don’t want users to access. I’ve found it helpful in the past with distributed workbooks that might have complex formulas in them, as by hiding formulas you keep users who don’t understand them from getting distracted and wondering what the heck the formula is or does. Conversely, if you want to share your brilliance with people, then by all means don’t hide your formulas (the downside is that if you’re not very good with formulas you let everyone know it…)
Protection Options/Applying Protection
Expanded protection functionality has been available since Excel 2000, and it gives you a lot of options that before could only be accomplished via VBA (Visual Basic for Applications) programming behind the scenes.
When you protect a worksheet you can goto Review–>Protect Sheet (/rps) and you’ll see the following dialog:
By default the Protect Worskheet option is checked, and since you’re there to protect the sheet anyway, the option is pretty much irrelevant (in fact, if you uncheck it, you lose the ability to protect the sheet, so it’s more than irrelevant). Next you have the option to add a password. I generally add a password for distributed workbooks, but if I’m testing a worksheet or if I’ve protected one of my own worksheets, I don’t add one (it can be a pain to have to enter the password to unprotect, then enter it twice to reprotect it ad nauseum).
- Now here’s a note on Excel’s security: it’s notoriously weak. But then again, so are locks on doors, and they both serve somewhat the same function in that they’ll keep 99.9% of people honest. If someone really wants to get past your password, or door/window lock, it can be done in about 2 seconds (but I won’t tell you how, neither will Microsoft).
You can now enter a password, which you’ll be prompted to enter again to confirm. Press OK and your sheet is now protected. Before you go any farther, we also need to talk about the protection options you have (maybe the password input box should be at the bottom of the dialog box, as it’s the last step?)
By default the Select locked/unlocked cells options are enabled. In some cases you’ll want to prevent users from selecting locked cells, although I can’t really think of a good reason for not selecting unlocked cells. Microsoft probably put that there just to give you the option, even though you’ll probably never us it.
Following are the additonal features that you can enable/disable by choice and what they mean:
- Format Cells/Columns/Rows: if you have users who like playing with their colors and fonts, then you can let them. Although I generally don’t make this available to them in order to preserve the integrity of distributed workbooks. I’ve seen too many users “do their own thing” with workbooks, and then you end up with a horrid mish-mash of workbooks that all essentially (maybe) do the same thing, but look different. It’s not a good idea if you want to have a coordinated presentation, especially with customers.
- Insert Columns/Rows: another iffy option, as users can manipulate workbook integrity, but you may have some cases where users need to ability to add rows or columns in order to add data.
- Insert Hyperlinks: this one is good if you want users to be able to add e-mail addresses or links to websites, e.g. customer sites, internal sites, etc.
- Delete Rows/Columns: another iffy one here. In a distributed workbook, you need to be pretty careful about what you let users delete, lest they delete functionality. I’ve had too many users call me to say that “it stopped working” only to find out that they deleted a row(s) of dependent formulas.
- Sort: very handy, and probably one of the biggest reasons why Microsoft added the increased Protection options. Prior to this the only way to sort on a protected sheet was via VBA code.
- Use AutoFilter: ditto.
- Use Pivot Table Reports: unless you have some high-end users, this feature will probably be unnecessary. And if you do have users savvy enough to be using Pivot Tables, you probably won’t want them to limit them with protection, unless it’s purely for data entry purposes to prevent accidents (which are all too common - I have had more than my share of experience accidentally deleting some essential cell(s) that drives a data series and had to redo my work).
- Edit Objects: probably not very necessary, unless you have a pretty object oriented worksheet/book (lots of buttons, logos, etc).
- Edit Scenarios: this goes back to using Pivot Tables. Unless you’ve got some high-end users you probably won’t need to worry about enabling this one. You’ll know if you do.
The Next Step: Workbook Protection
Unfortunately, worksheet protection won’t protect you from users deleting your well set up and protected worksheet, but workbook protection will. No matter how much work you put into an individual or multiple worksheet(s), you’re going to run into an instance where someone deletes the entire worksheet. In some cases that’s not too bad as you can just send them a new workbook, but sometimes, especially in collaborative workbooks, it can mean a lot of work to recreate what was lost, so you’re better off protecting yourself in the first place.
Workbook protection is easy to enable by going to Review–>Protect Workbook (/tpw), where you’ll see the following options:
You’ll want to check the Protect Structure and Windows option, after which you’ll get the following dialog box:
Protecting for Structure: this is the default option, and it will primarily keep users from unhiding hidden sheets (like those that may contain Data Validation lists), actually hiding, moving or renaming worksheets, inserting new ones, or copying sheets to a new workbook (a note to this is that there’s nothing to keep users from e-mailing or copying the entire workbook itself). There are some other options that will be disabled, but they are more for high-end users. If you’re going to take this step, then I would also add a password, otherwise your workbook is essentially just a closed door with the door unlocked.
File Protection Options