After developing a really useful Excel Spreadsheet, you may want to Protect all that hard work from falling into the wrong hands. The spreadsheet may have confidential financial data that cannot be seen by others. Excel offers a number of features for the protection of Workbooks and Worksheets to provide some level of security for your Spreadsheets.
Note on Excel Security: None of the features offered through Excel provide strong security for your Spreadsheets. Workbook Protection measures can be overcome by IT skilled users or a quick search on the internet for “password” breaking sofftware. Other security measures such as restrictions in access to the file locations and network security are far more likely to effectively protect your valuable Spreadsheets.
In this post we will demonstrate Workbook Protection and how it can be used to restrict access to your Spreadsheets. This can be combined with Worksheet Protection for further control access to specific information within your workbooks. Refer to our recent post on Protecting Cell Data in Excel for more details on Worksheet Protection.
Step 1 – Set Workbook Protection
Open a new Workbook and click on the Review Tab (Excel 2007). The following dialog box will be displayed. For those still using Excel 2003 select Tools > Protection > Protect Workbook.
Step 2 – Options for Protection
The default selection is to protect the “Structure” of the workbook. With this set other users will not be able move or change worksheets within the workbook. The other option is to protect “Windows” that you have created in the workbook. A Window is another view of the same worksheet that can be created and saved with workbook. For this post we will leave that option unchecked.
Step 3 – Specify the Password
Now type in your password and select OK. You will be presented with a verification dialog box. Simply repeat the password and the Workbook Protection will be set.
Step 4 – Protection in Place
Now try and change a worksheet name or move a worksheet around in the Workbook and you will be presented with the following warning.
Step 5 – Workbook Access Protection
To complete the Workbook Protection we can also specify a password that is required on open. This will require the user to provide a password when opening the workbook. Select ‘Save As’ and then next to the Save button (Excel 2007) select the Tools drop down and select ‘General Options’.
Here we specify a password for Opening the workbook or to allow Modification of the workbook. The workbook can also be set to open in ‘Read Only’ mode as recommended. With this set the user will be recommended to open the workbook in ‘Read Only’ mode unless they choose to supply the password to modify the workbook.
Step 6 – Set the Workbook Password
Now set the password for Open and Modify, in each case you will be requested to verify the password by repeating. Once complete the workbook needs to be saved and then the Workbook Protection is in place. Now whenever you open the workbook the following dialog will be presented.
Simply provide the password and the workbook will be opened. If you supply the wrong password, a warning message will be displayed advising that the wrong password has been entered and to check if “CAPS LOCK” is selected on your keyboard.
There is no restriction on the number of attempts for the opening of a workbook, this is one of the reasons that Excel does not provide strong protection for your Spreadsheets.
Step 7 – Remember your Passwords !!
Now that you have specified a password for Worbook Protection and also to restrict who can Open your workbook, you need to make sure that you remember the password. If the password is lost you won’t be able to open your own workbook. So keep your passwords in a safe location in case you forget the password you used on a workbook.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment