Software

Office challenge: How do you password-protect an Excel workbook?

This week's Office challenge tests your knowledge of Excel's protection features and reveals the answer to last week's challenge on printing a PowerPoint slide in black and white.
Protecting data is probably an important part of your job. There's a ton of software (and some of it is expensive) and more policies and guidelines than you can shake a stick at -- all designed to help you protect your data. However, it's often the simplest efforts that keep your work safe. Password-protecting an application isn't the only thing you can or should do, but it's often the first line of defense for keeping people who have access to your system out of a confidential file. So this week, tell us how to password-protect an Excel workbook. Last week we asked… How do you print color slides in black and white? This trick is easy, but you might not know where to look for it. You can change the print default by choosing Color/Grayscale from the View menu. Then, choose Black And White from the resulting submenu. In PowerPoint 2007, click the View tab and then click Pure Black And White in the Color/Grayscale group. You can also choose this setting just before printing. From the File menu, choose Print. Then select Pure Black And White from the Color/Grayscale control in the Print What section. In PowerPoint 2007, you'll find the Print options by clicking the Office button Darryl, SheRex, .Martin, and futureking all mentioned one or both solutions. Chris.elvidge offered a unique solution: Print the presentation to a black-and-white printer. That wasn't the answer I was looking for, but whatever works is okay with me. Cabmdbase has found a similar hardware solution. As always, thanks for playing.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
Darryl~
Darryl~

To password-protect the currently-open workbook: 1. Click "Tools" - "Options". 2. When the "Options" multi-tabbed dialog box appears, click the "Security" tab. 3. Next to "Password to open" enter the required password to open the current workbook. If you do not enter a password in the "Password to modify" section below, if someone enters the password they can read and modify the file. If you do enter a password in the "Password to modify" section below, entering this password only provides the user read-access to the workbook. 4. Click the "Advanced" button if you wish to change the encryption type. 5. To differentiate between a read-only and a read-write access level, enter a password next to "Password to modify". Now, if someone wants to modify a document, they must enter both passwords. 6. If desired, click "Read-only recommended". This will cause Excel 2003 to recommend, upon opening the document, that it should be opened in read-only mode. 7. Click "OK" to close the dialog boxes. 8. Resave the spreadsheet to apply your changes. Sometimes I take this a step further & lock the cells on each worksheet also to prevent anyone but myself from altering formulas, etc. 1. By clicking the block in the upper left corner (between the 1 & A) you can select all cells in the worksheet. 2. Right click in the middle of the "selected" cells & select Format Cells from the dropdown list. 3. Click the Protection tab and check the Locked box and click OK. 4. Select any cell you wish a user to be able to enter data into & right click it, select Format Cells, Protection, and uncheck the Locked box. 5. Click Format on the menu bar & select protect sheet. 6. Check the box "Protect worksheet and contents of locked cells" and select the desired restrictions from the checkboxes below. 7. Enter a unique password and click OK. Confirm the password. 8. Repeat this process for other worksheets in the workbook. Now when the workbook is saved, you have 3 sets of restrictions. With the password protection on the workbook, you can have some users that can read the workbook but not add anything, the users you gave the ability to change the workbook when password protecting it can only enter data into the cells you designated as unlocked, and you have the ability to alter formulas, etc. on the locked cells.

Neon Samurai
Neon Samurai

To protect the workbook from access: File -> Save As -> Tools -> General Options To protect worksheets from modification: Tools -> Protection -> Protect Sheet Neither of these should be relied on as methods of restricting access to the contents of the file though. A password protected XLS adds about an hour's worth of complexity for anyone trying to get into it. It may keep Bob in the cubicle beside you out of the file but proper file permissions on the network should do that already. Eve who is a little more motivated will still get through your password though. Worksheet protection is worth using if your only concern is keeping formatted sheets from being modified. For that, I frequently allow only specific cells to have values changed or some auto-filtering done. I leave the password field blank though as my interest is the "read only" effect on parts of the worksheet and a password adds no value to that. Print the XLS sheets to PDF if your intent is to protect the formula and formatting when sending to a recipient who will only be viewing it. If you really want to protect your XLS from unauthorized viewing, do it right and use a strong encryption option. Truecrypt, PGP; something more than the weak password mechanism built into the XLS file. Note; the newer 2007 file format may include a stronger protection mechanism and will keep older Office versions from being able to open it. Everything previous to that breaks easily though. In those cases, the workbook password is simply a warm fuzzy false sense of security where a proper mechanism should have been used.

Super_sonix
Super_sonix

Go to TOOLS > Protection > Protect Sheet. input the password you want the workbook / speadsheet to have and choose what within the workbook should be protected.

LAWRENCE KINUTHIA
LAWRENCE KINUTHIA

from office 2007 click on the office buton then save as. From the save as dialog box click on the tools command then general options. Type in the password to open the file. you could also type in a password to prevent users from modifying the contents of the workbook. retype the passwords to confirm then click ok. finally click save. The workbook is now protected. Lawrence Kinuthia - kinuthialawrence@yahoo.com

PlexusSage
PlexusSage

I thought the target demo of TR was techies and tech leaders, not data entry clerks. Basic computer tips such as how to password protect and print in greyscale are helpful info, but maybe not to the folks for whom I thought this site was intended. When I need this kind of help I hit F1. Charlie http://www.plexuscommunications.com

ThumbsUp2
ThumbsUp2

Best to edit your post and remove your email address. SPAM BOTS crawl this site pretty heavily and spot that kind of thing quickly. So, unless you enjoy tons of SPAM in your inbox, remove the email address.

OurITLady
OurITLady

As a support pro I don't often get to use the apps I have to support to the same in-depth level that the end users have to, so when I get questions like this it can take me a few minutes to figure out. I was actually asked this last week, it only took me 5 minutes to work out where the setting was but if this had been published then I could have saved 4 minutes (or 80% of the time, which is fairly major for me at the moment)! BTW - Answer is Tools/Options/Security - enter passwords you want as required (Open and/or Modify), save workbook (using office 2003).

ssharkins
ssharkins

TR's audience does have a high level of technical expertise. MS Office is a bit different, because not only do IT personnel support it, readers without a lot of technical experience, use it and often struggle in that endeavor. A recent poll did show that (at least for the people who took part in the poll) that a large percentage of this blog's audience have a great deal of technical experience. We'll be including more VBA tips and code as a direct result of that poll. On the other hand, if you follow the tips and threads, you may have noticed that the tips that get the most responses -- the "thank you, that's great!" are the convenience tips -- not the tips that solve complex problems. It's a broad audience and we'll continue to support all of that audience. BTW, let me take this opportunity to invite all readers to submit tips and ideas. If I print your solution or idea, I'll give you full credit.

ThumbsUp2
ThumbsUp2

This site is intended for all readers of all skill levels, period. While you might be bored with this type of 'challenge', others might not be. I noticed you didn't 'play' the challenge though! ;) You might also be surprised at how many people don't know how to use the Help system because they don't know what the 'feature' is called so they don't know what to search for. ;)

edward.j.obrien
edward.j.obrien

Good point, Thumbsup2. However, if you do want people in the forum to quickly contact you, then enter your e-mail address with an obvious disguise like "name@DELETEmyisp.com".

Darryl~
Darryl~

I have engineers, Directers, etc. that are very good at "their" job....they rely on us to help them do their job better. If I can spend a couple minutes showing someone how to properly lock down a file, hopefully that person will show others in their department how "easy" it is & I don't get called by 20 or 30 others.....not only that...but they come off as being "proficient" and it boosts their ego....it's a win win situation.

Neon Samurai
Neon Samurai

TR provides private messaging through the user profile. No reason at all to have your email in a forum post. Actually, email and website addresses look more like forum spam unless the website link is relevant to the discussion.

Editor's Picks