Latest posts by techwriter (see all)
- Test Your Knowledge of 4 Basic Fonts – Drag & Drop - January 27, 2017
- How NOT to Design a Web Site - January 25, 2017
- Hazards of Poorly Written Technical Documentation - December 26, 2016
By Todd Waltman
Microsoft Office Excel 2007 provides many ways to secure and protect your work. For optimal security, you should protect your entire workbook file with a strong password. Excel passwords can be up to 255 letters, numbers, spaces, and symbols and are CaSe SeNsItIvE. For additional protection of data inside your workbook, you can protect specific worksheet or workbook elements, with or without a password. Protecting worksheet or workbook elements may help prevent users from accidentally or deliberately changing, moving, or deleting important data.
In this Microsoft Excel 2007 Tutorial, I’ll show you how to create a password to protect your workbook and how to protect some workbook elements. There are many Excel Tips and Tricks to guide to being an expert Excel user. I have other Excel Tips as part of my Microsoft Excel 2007 Tutorial series that I hope you find useful.
To provide security to your entire workbook, you can specify two separate passwords:
- Open and view the workbook. This is an encrypted password that prevents unauthorized access to your workbook. You can also give users the option to open in read-only mode if they are just viewing data. This can help prevent accidental changes from being saved.
- Modify the workbook. This is an unencrypted password that is only meant to give specific users permission to edit the workbook.
These passwords apply to the entire workbook and they don’t have to be the same password. In fact, it is much safer to use different passwords. To provide strong security, you may want to utilize both features. Speaking of strong, strong passwords combine uppercase and lowercase letters, numbers, and symbols. sweetheartone is not strong, but Sw33tH3ArT0nE is strong.
To secure your workbook with a password:
- Click the Microsoft Office Button, then click Save As, then choose Excel Workbook. Yes, do this for an existing workbook.
- On the bottom left of the Save As dialog box, click the Tools button and choose General Options.
- To require users to enter a password upon opening the file, type a password in the Password to open box.
- To require users to enter a password that allows them to make and save changes, type a password in the Password to modify box.
- To protect from users accidentally modifying the file, select the Read-only recommended check box. Users will be asked whether or not they want to open the file as read-only. NOTE! If you created a Password to modify, the user, when prompted to enter this password, will have the option to open as read-only. Therefore, this option is not necessary when using Password to modify.
- Click OK.
- You’ll be prompted to retype your passwords to confirm them. Click OK after confirming.
- Click the Save button.
- If this is an existing workbook and you’re using the same file-name, you’ll be prompted to click Yes to replace the existing workbook.
Microsoft Office Excel 2007 provides options to protect your data from being changed or deleted by using worksheet element security and workbook element security. For other Microsoft Office Excel Tips and Tricks, see my other articles in the Microsoft Excel 2007 Tutorials series.
WORKSHEET ELEMENT SECURITY
- On the worksheet that you want to protect, unlock any cells or ranges that you want other users to be able to change:
- Select each cell that you want to unlock or select a whole range of cells.
- On the Home tab, in the Cells group, click the Format button, then click Format Cells.
- On the Protection tab, clear the Locked check box.
- You can hide formulas that you don’t want users to see:
- Select the cells that contain the formulas you want to hide.
- On the Home tab, in the Cells group, click Format, then click Format Cells.
- On the Protection tab, select the Hidden check box, then click OK.
- If you want to unlock objects like pictures, clip art, or shapes do the following:
- Hold down [CTRL] while clicking each object that you want to unlock. The Picture Tools or Drawing Tools will be displayed, adding a Format tab. NOTE! Don’t select objects of different types as the Dialog Box Launcher won’t initialize.
- On the Format tab, in the Size group, click the Dialog Box Launcher next to Size.
- On the Properties tab, clear the Locked check box. If present, clear the Lock text check box.
- Click the Close button.
- On the Review tab, in the Changes group, click Protect Sheet.
- In the Allow all users of this worksheet to list, select the elements that you want to allow users to change.
- In the Password to unprotect sheet box, type a password for the sheet, click OK, then confirm the password. This is an optional password. If you don’t use it, then any user can unprotect the sheet and change the protected elements.
WORKBOOK ELEMENT SECURITY
You use workbook element security to prevent users from, among others:
- Viewing worksheets that you have hidden
- Moving, deleting, hiding, or changing the names of worksheets
- Inserting new worksheets or chart sheets
- Moving or copying worksheets to another workbook
- In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets
- Recording new macros
- On the Review tab, in the Changes group, click Protect Workbook and select the Protect Structure and Windows option.
- To protect the structure of a workbook, select the Structure check box.
- To keep workbook windows in the same size and position each time the workbook is opened, select the Windows check box.
- To prevent users from removing this workbook protection, in the Password (optional) box, type a password, click OK, then confirm it. This is an optional password. If you don’t supply a password, then any user can unprotect the workbook and change the protected elements.
Excel security is an important feature that can help protect your data.
This is just one of many Excel Tips and Tricks that can help you be more efficient in your job. There are many more features from which you can benefit. For a list of Excel “things to know”, check out this Microsoft Office 2007 Tutorial article. There are also some links to online tutorials with free samples.
Todd Waltman is an IT Professional with many years of experience as a business analyst, project manager, and IT architect.