How you can Secure Individual Excel Cells using a Password

Password protecting spreadsheets can be beneficial since it stops other people from overtyping data, either accidently or deliberately. In certain cases though it would be more effective to secure only some cells leaving others editable. This does seem to be unachievable, but interestingly the process is actually easy.

Configuring the Spreadsheet for Protection

We'll first examine what happens when a spread sheet is password protected. When ever we apply a password to a worksheet, all the cells will be locked simultaneously. What we have to do first is mark any cells which we do not wish to protect. This is accomplished by first choosing the cell or range of cells which are to be left unprotected, then right-click using the mouse to show the Format dialogue box. So now choose the tab labeled ‘Protection’ and you'll observe that there are two tick-boxes, ‘Locked’ and ‘Hidden’. You'll see that the 'locked box already has a tick inside, and the 'hidden' box is unticked. This indicates that when password encryption is applied to the worksheet, the cell (or cells) will be locked. What we have to do is un-tick the 'locked' box and then click the 'OK' button. We will now set a password for the spreadsheet.

How to Encrypt the Worksheetwith a Password

To create a password we first have to select the 'Review' tab and then click 'Protect Sheet'. What we see now is a popup with an empty password field. The popup box also provides a selection of extra options which allows us to choose what actions can be applied when the spread sheet is encrypted. The default position provides for the selection of locked and unlocked cells, but nothing else can be carried out when the worksheet is encrypted. Now we enter any chosen password into the box and just click the 'OK' button. Next we're required to input the password again to be sure that we have entered it in correctly. The worksheet will now be protected, while the cells which we previously marked as unlocked will still be open. To unprotect the worksheet, just click the 'Unprotect Sheet' button and key in your password.

What Can you Do if You Cannot Remember the Password?

You may think initially that if you lose password that there is no chance to get back in to the worksheet. There is however one way to circumvent the password protection, but it does depend upon which actions were made allowable when we set the password. We discovered that by default, Excel allowed for the selection of both unlocked and locked cells when we protected the spread sheet. So long as this has been permitted, the answer is actually very simple. All that you have to do is to select the whole of the spreadsheet, then copy and paste it into a new blank workbook. This has the effect of copying all of the data and formulas, yet not the password password protection. All we have to do now is to delete the initial spreadsheet and then name your new workbook to the same. Effectively, we've now got the original worksheet however it's no longer password-protected. But very careful , for if we had un-ticked the first two allowable actions we wouldn't be able to copy or paste the data. To prevent any one from copying and pasting we just simply need to un-tick the first two options. Be really careful though that you don't lose your own password

For information on Excel courses in your area go to the website