One of the unsung hero’s in the Microsoft Office suite of applications is in fact Microsoft Excel. It is one of the most widely used applications in business but yet it hardly gets a mention due to its lack of sex appeal compared to other Microsoft Office applications like Microsoft PowerPoint and Microsoft Project. If you are in business you cannot survive without Microsoft Excel as it allows you to model your business and identify when things go wrong. It also allows you to build templates for your team and then have those team members enter data into the templates to be correlated at a later time.
For example, if you are working in a large corporation and you are a Senior Manager, one of the responsibilities you will have is to create budgets each year. Now if you are like me, this can be a major task and a major source of frustration, in particular because you will have some people in your team that will know better and want to modify your templates …
Sound Familiar …
Well, here a couple of things to consider. Why not lock off the cells you do not want your staff to be playing with. That’s right, you can control what people can modify. You can even control which cells they can click on and which ones they can’t. My outline in this article will cover setting up protection for Microsoft Excel 2003. The reason I have chosen this version is that it provides far more security options compared to previous versions.
Okay so let us begin…
The first step you must take in protecting your workbook in Microsoft Excel is in deciding what cells you want to protect. By default, every cell in Microsoft Excel is setup with the cells being protected however, the function is not enabled until you actually protect the worksheet. So, to protect and unprotect cells, the first step is to select the cells that you want to unprotect, then select the Format menu and choose Cells from the drop down menu, alternatively you could have used the shortcut keystroke [Ctrl] + [1].
In the Format Cells dialog box simply choose the Protection tab. You will now see a check box that says Locked and it should have a tick next to it. This tick means that when you set protection for this worksheet it will not be available for editing. That means we need to deselect it by clicking on the check box once. To complete the process simply choose the OK button.
So at this point you need to go through and follow this process for all the cells that you want to have your staff entering data for…
The next step once you have completed this process is to protect the worksheet. Now the interesting thing about this is that to enable protection of the cells you must individually protect each worksheet. So, now choose the worksheet you want to protect then go to the Tools menu and choose Protection from the drop down menu, and then finally choose Protect Sheet. At this point the Protect Sheet dialog box will appear and will ask you to enter a password. Now, this is where you need to be very careful. If you enter a password, make sure that you make a note of it because if you don’t and you forget the password, you will have problems reusing this sheet or in particular changing this sheet in the future.
You also have some other options available in the Protect Sheet dialog box. The main one we are concerned about is deselecting the option Select Locked Cells. If you deselect this option it means only the Unprotected cells can be selected by your users. It also means they can use the tab key to go from one Unprotected Cell to another.
If you have multiple worksheets in your spreadsheet you will have to go to each individual sheet and protect them. The reason that Microsoft has done this is to ensure that you can have different people modifying the data on differing worksheets, rather then each person being able to modify everything.
One of the other elements you can protect is the ability to change the workbook. The previous examples protected the individual worksheet but you also need to be able to protect your workbook. You can do this by going to the Tools menu, choosing Protection and the choosing Protect Workbook. The application will then allow you to protect the structure and windows. You can also enter another password as well. Keep in mind you need to be documenting these passwords in a separate workbook in a secure location on your server. The Password Excel Spreadsheet should be put in a secure location by your IT Department with no password on the individual file. If the IT Department puts it into a secure location without a password, it means if something happens to you in the future, the IT Department will still be able to recover your work.
Finally, the last set of protection we need to consider is whether we need a password to modify the workbook or to open the workbook. To set these parameters we simply go to the the Tools menu and then choose the Options command from the drop down menu. In the Options dialog box choose the Security tab. From here you can now set a password to open the file and to modify the file.
You will in all areas where you are entering passwords be required to enter the password twice. This ensures that you do not incorrectly type the password and cause the file to be inaccessible.
Protecting your workbook, worksheet and cells allows you to take control of your spreadsheet and to prevent people from playing with your data and the structure of your spreadsheet. Keeping control of your spreadsheet can save you hours of rework ensuring that you maintain your efficiency and productivity and the bottom line is that it will save you money.
