How to Password Protect Your Excel Spreadsheets

It can be very useful to password protect spreadsheets to make certain that other people aren’t able to accidentally (or even deliberately) over-type important data or formulas and functions. Having said that, there are occasions when when you will need to protect some cells but permit other users to gain access to and edit other cells on the same spreadsheet. It could seem that this is something that may could be hard to achieve, but in fact it is not difficult to do

First Set up The Spread sheet
First of all we must understand what happens when we password protect a spread sheet in MS Excel. Let us begin by getting an understanding of what takes place when we password protect a spread sheet. Automatically, all cells in Excel are locked when protection is applied to the spreadsheet. The default setting in Ms excel is for all cells to be locked when we encrypt the spread sheet. What we must do first is tag any cells which we don’t intend to protect. We do this by clicking in the cell, or cell range which is not to be encrypted, then clicking the right mouse key and picking ‘Format’. Now we choose the tab called ‘Protection’ which produces a dialogue box containing two tick-boxes named ‘hidden’ and ‘locked’. The default is for the ‘locked’ option to be ticked automatically, with the ‘hidden’ option being un-ticked. The effect of this is that if we apply a password to the spreadsheet, every one of the cells are going to be locked at the same time. What we need to do is un-tick the ‘locked’ box and then click the ‘OK’ button. Your spreadsheet is now ready to be password protected.

How to Password Protect the Spreadsheet
The next stage is to select the ‘Review’ tab on the Ribbon and click the ‘Protect Sheet’ tool button. What we see now is a popup with an empty password field. It also has a number of tick-boxes which allow you to choose what can and can not be done after the spreadsheet is encrypted. The default position allows for the selection of locked and unlocked cells, but nothing else can be carried out when the spread sheet is locked. Next we type in any chosen password into the box and press the ‘OK’ button. Now we’re expected to re-enter the same password to double-check that it has been typed in correctly. Your spread sheet will now be password-protected, but you can still modify the cells that you choose to be unlocked To remove password protection, click on ‘Unprotect Sheet’ then enter the password.

How can you Unprotect the Spreadsheet if You’ve Forgotten The Password
You could think initially that if you lose password then there is no way to get back in to your worksheet. However, there’s a simple way round the protection, depending on what actions you have allowed before inputting your password. We found that by default, Ms excel allowed for the selection of both unlocked and locked cells when we protected the worksheet. So long as this has been allowed, the answer is in fact quite simple. The solution is to basically copy the entire spread sheet, and then paste it into a new workbook. The data as well as formulas will be copied into the workbook, however it will not be password encrypted. The first spreadsheet can be deleted and you can name this new workbook the same as original. Effectively, we have now got the original spreadsheet yet it’s no longer password-protected. If on the other hand we had previously un-ticked the first two allowed actions, we wouldn’t be able to select any cells and hence would not be able to copy and paste. To stop any one from copying and pasting we simply have to un-tick the first two options. Be very careful though that you do not lose your own password