Find out how to Password Protect Single Cells in Microsoft Excel

It is typically advisable to protect spreadsheets with a password so that other people cannot accidently (or deliberately) delete your data and formulas. It can be useful however to protect only certain cells within the spreadsheet and leave the others accessible. This would seem to be unachievable, however the technique is actually straightforward.

Configuring the Spreadsheet for Encryption

Let's first have a look at what occurs when a spread sheet is password. By default, all cells in Excel are locked when password encryption is applied to the spreadsheet. The default setting in Excel is for all cells to be locked when we protect the spread sheet. What we have to do before we password encrypt the spread sheet is indicate to Excel which cells we do not wish to be locked. This is achieved by choosing the cell or range of cells which we do not want to protect, then clicking the right mouse key and picking the 'Format' option in the menu. Now we select the tab labeled 'Protection' which yields a dialogue box containing two tick-boxes named 'hidden' and 'locked'. The default is for the 'locked' option to be checked automatically, with the 'hidden' option being un-ticked. We can see then that when the spread sheet is locked, every one of the cells are protected and so 'read only'. What we must do is untick the 'locked' box and next click the 'OK' button. The spreadsheet is now ready to be password protected.

How to Password Protect the Spread sheet

To set a password we first need to choose the 'Review' tab and then click 'Protect Sheet'. You will be presented with a small dialogue box containing a password box. It also has a number of tick-boxes which enable us to determine what can and can't be done when the spread sheet is protected. By default, the selection of locked and unlocked cells is permitted, but nothing else. Now key in a password into the box and then click OK. Next we must type in the password again to be certain that that we have typed it in correctly. All of the cells within the worksheet is now read-only, apart from those that we have designated as un-locked. To remove password protection, first select 'Unprotect Sheet' then enter password.

What Can you Do if You Can't Remember the Password?

It might seem at first that if you lose your password that there is no chance to get back in to the spread sheet. However, there is a simple way to get around the password protection, however it does depend on which actions were made permissable when you set up the password. We found that by default, Excel allowed for the selection of both unlocked and locked cells when we encrypted the spreadsheet. If you have left these at the standard settings, it really is really easy to make the spread sheet editable once again. The solution is to merely copy the entire spreadsheet, then simply paste it into a new workbook. Your entire data and formulas will be copied, while the password protection is not. All we have to do now is to delete the original spreadsheet and name your new workbook to the same. We now effectively have the original spread sheet but without the password encryption. If on the other hand we had previously un-ticked the first two permitted actions, you wouldn't be able to select anything and as a consequence wouldn't be able to copy and paste. To stop any one from copying and pasting we just simply have to un-tick the first two options. But, just be very careful you do not forget your  password!