It can be very useful to password protect spreadsheets to ensure that other people can’t accidentally (or even deliberately) over-write important data or formulas and functions. It actually is handy however to protect only certain cells inside a spreadsheet leaving the rest accessible. It could seem that this is something that may be difficult to achieve, but in point of fact it is so simple to do
Configuring the Spreadsheet for Encryption
Let us first have a look at exactly what goes on when a spreadsheet is password secured. By default, all cells in MS Excel are secured when password encryption is applied to the worksheet. The default setting in Microsoft excel is for all cells to be locked when we protect the spread sheet. What we need to do first is indicate any cells which we do not need to protect. We can accomplish this by clicking inside the cell, or cell range that is not to be encrypted, then clicking the right mouse key and selecting ‘Format’. Now select the tab labeled ‘Protection’ and you will observe that there are two tick-boxes, ‘Locked’ and ‘Hidden’. By default, the Locked box will have a tick within. This means that when password protection is applied to the spread sheet, the cell (or cells) will be locked. So all we have to do then is remove the tick by clicking within the box, then choose ‘OK’ to save the changes. The worksheet has now been set up for password protection.
How to Encrypt the SpreadsheetUsing a Password
Your next step is to select the ‘Review’ tab on the Ribbon then click the ‘Protect Sheet’ tool button. You’ll be presented with a small dialogue box which has a password field. Actions that are permissable when the worksheet is locked can be decided by checking the tick-boxes that are displayed directly below the password box. The default position permits the selection of locked and unlocked cells, but nothing else can be done when the spread sheet is password-protected. Now we type any chosen password into the box and simply click the ‘OK’ button. Next we are expected to re-enter the same password to double-check that it has been typed correctly. The worksheet will now be protected, but you will still be able to modify the cells that we choose to be unlocked To remove password protection, just click ‘Unprotect Sheet’ then key in your password.
How can you Unprotect the Spread Sheet if You’ve Forgotten Your Password
You could think initially that if you lose your password then there is no chance to get back in to the spreadsheet. In fact, it’s possible to get back into the spreadsheet, but it depends on what actions were granted when you typed our password. We observed that by default, Excel allowed for the selection of both unlocked and locked cells when we protected the spread sheet. With these two options ticked it’s actually easy to get back into your spreadsheet. The solution is to just copy the whole spreadsheet, then paste it into a new workbook. All of your data and formulas will be copied, however, the password protection is not. You can now save the workbook, delete the original then rename the new workbook appropriately. We now effectively have the original spread sheet but with no password protection. However, if we had un-ticked the boxes enabling the selection of cells before applying your password, you’ll not be able to select the spreadsheet and hence will not be able to copy it. To prevent anyone from copying and pasting we simply need to un-tick the first two options. But, just be careful that you don’t forget your password!