Administrative Sparkle Blog

Problem. You have created a fabulous spreadsheet. You’ve entered text and formulas and all that is required to update the spreadsheet is the data. The user input is entered by others than yourself and you are discovering that certain cells are being modified, such as “Sum” formula cells. The individual doesn’t even have to enter anything because the formula will automatically update that cell. But, no, you’re dealing with a person who thinks they need to update that field with manual input.

Answer. To maintain the integrity of the sheet and its corresponding formulas and links, you are going to want to lock them out. Not the entire sheet of course. After all, you need them to enter their data. You can lock out individual cells. This is what you can do to keep sticky fingers out of your cells.

1. First, unlock all the cells on the sheet so you can pick and choose which ones you want to protect. Click on the left corner (see picture below) or press Ctrl-A to select the entire sheet.

2. Right-click your mouse and select Format Cells. Select the tab Protection and clear the check box next to Locked. It will look like this:

3. Click OK.

4. Back at your spreadsheet, select the cells you want others to stay out of. You can select multiple cells by holding down the Ctrl key and left-clicking your mouse on the cells you want to lock up. You don’t have to do one cell at a time, you can do them all at once. If your cells are consecutive, next to each other, hold down the Shift key and drag your mouse through all the cells you want.

5. Now that you have made your selection, go back into the Protection tab. Right-click in the spreadsheet, select Format Cells, then left-click the Protection tab, and put the checkmark in the box Locked. So now it looks like this:

6. Lock and Load. You have primed your document for selective lockout. You’re going to like this, I promise. In Excel 2007, you will want to select Review in the menu, then select Protect Sheet. You will see three boxes checked (see picture below). You can leave those checked and enter a password in the section that says, “Password to unprotect sheet:,” and click on OK. A dialog box will prompt you to confirm the password. Re-enter the password and click OK.

That’s it. You’re done. You can test this by attempting to type in the cell. A warning dialog box will stop your unsuspecting violator in their tracks. JUST REMEMBER YOUR PASSWORD.

If you need to make changes to your protected cells, just follow the same procedure in line item #6. Instead you will see Unprotect Sheet instead of Protect Sheet. You only have to put your password in once here. Remember, passwords are also case sensitive. So if you have Caps Lock on, that’s what you’ll get. I’ve done that, so if you feel like you’ve been locked out and you know for a fact that you know the password, put your Caps Lock on and that should get you in.

I hope this tip helps keep your vagrants at bay and the integrity of your worksheet intact. If you have any questions, feel free to post a comment and I will respond within 24 hours. I have some rather elaborate spreadsheets and a lot of users who input data. This has saved me immense time and frustration in maintaining a lot of associated links located on additional spreadsheet tabs and workbooks that reference those cells where I will receive value error in the field if the cell is modified.

Let me know if you find this tip helpful, if you have any suggestions, or would like to elaborate.  Also, let me know if you find the graphics helpful.  Share with your fellow colleagues and let them know where they can find a great resource for information by visiting this website. Share the knowledge is what I always say!

Related posts:

1. Word: Restrict Formatting & Editing Most people aren’t aware that you do not have to...
2. Ctrl+A & Other Selection Techniques There are some really fabulous techniques that will simplify selecting...
3. Hot-Key Tip of the Week: Ctrl-S, Ctrl-P, & Alt-F4 Hot-Key combination tip for the week. You’ll notice that there...
4. WinZip Procedures Zipping programs have theri advantages and are really fast, easy,...

Related posts brought to you by Yet Another Related Posts Plugin.