Protecting Single Cells in Excel for Security and Flexibility

It is typically advisable to protect spreadsheets using a password to make sure other people aren't able to accidently (or deliberately) delete your data and formulas. It is often helpful however to protect only certain cells within a spreadsheet leaving the rest open. It might seem that this is something that may could well be tough to achieve, however it is very simple to do

Preparing the Spreadsheet for Encryption

We'll first examine what occurs when a worksheet is password. Automatically, all cells in Microsoft Excel are locked when protection is applied to the worksheet. The default setting in Microsoft excel is for all cells to be locked when we protect the spreadsheet. What we must do first is indicate any cells which we do not wish to protect. This is done by selecting the cell or range of cells which we don't want to protect, then clicking the right mouse key and choosing the 'Format' option in the menu. Next we choose the tab designated '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. What this means is that when password encryption is applied to the worksheet, the cell (or cells) will be locked. We simply need to untick the box then save the changes by clicking 'OK'. The spread sheet has now been set up for password protection.

How to Password Protect the Spreadsheet

Your next step is to choose the ‘Review’ tab on the Ribbon and next choose the ‘Protect Sheet’ tool button. A pop-up now appears with a field in which we can key in a password. Actions that are permissable once the spreadsheet is locked can be chosen by checking the tick-boxes that are detailed directly below the password box. Automatically, the selection of locked and unlocked cells is granted, but nothing else. Now enter a password into the field and just click OK. Next we're required to type in the password again to make certain that we have entered it in correctly. Every cell in the spreadsheet is now read-only, other than those which we have marked as un-locked. To unprotect the worksheet, just click the 'Unprotect Sheet' button and key in the password.

What Do You Do if you Forget Your Password?!

You may think that if can't remember the password that you're locked out of the spread sheet for good. However, there is a technique to circumvent the password protection, however it does depend on which actions you've made allowable when we created the password. As mentioned earlier, by default Microsoft Excel will permit you to select both unlocked and locked cells when the worksheet is protected. So long as this has been allowed, the answer is actually quite easy. What we need to do is copy and paste the entire worksheet straight into a new blank workbook. The data along with formulas will be copied into the workbook, however it won't be password protected. Now you may save the workbook, delete the original and then rename the new workbook as necessary. Effectively, we have now got the original worksheet but it is no longer encrypted. Just be careful though, as if we had un-ticked the first two permissable actions you would not be able to copy or paste the data. If you would like prevent anyone from copying and pasting, just de-select these options. But be sure that you don’t forget the password!

For details about excel training courses go to the website