How you can Password Protect Individual Cells in Microsoft Excel

It can be very beneficial to password protect spreadsheets to be sure that other people are unable to accidentally (or even deliberately) over-type valuable data or formulas and functions. Having said that, there are occasions when when you'll want to protect some cells but allow for other users to get into and edit other cells on the same spreadsheet. This does seem to be impossible, but interestingly the technique is actually very easy.

Setting up the Worksheet for Password Encryption

Let's first have a look at exactly what goes on when a worksheet is password protected. When we apply a password to a worksheet, by default all the cells will be locked at the same time. What we have to do before we password encrypt the worksheet 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 don't want to protect, then clicking the right mouse key and selecting the 'Format' option from the menu. After that we select the tab designated 'Protection' which produces a dialogue box containing two tick-boxes named 'hidden' and 'locked'. Automatically, the Locked box always has a tick within. What this means is that when password encryption is applied to the spreadsheet, the cell (or cells) will be locked. So all we have to do then is to remove the tick by clicking in the box, then select ‘OK’ to save the changes. You can now set a password for the worksheet.

How to Apply a Password to the Spreadsheet

To create a password we initially have to choose the 'Review' tab and next click 'Protect Sheet'. What we see now is a popup that has an empty password box. Actions that are allowable once the spreadsheet is locked down may be chosen by checking the tick-boxes that are displayed underneath the password box. By default, the selection of locked and unlocked cells is allowed, but nothing else. Next enter a password into the box and click on OK. Next we are required to re-enter the same password to double-check that it has been typed in correctly. The worksheet will now be locked down, however, the cells that we previously marked as unlocked are still editable. To allow editing of the whole spread sheet again, simply click the ‘Unprotect Sheet’ tool button and type in the password.

How to Unprotect the Spreadsheet if You Forget The Password

If you can't remember your password it might seem initially that you are permanently locked out of your worksheet. In fact, it's possible to get back into the spread sheet, however it depends on which actions were permitted when we typed the password. As mentioned earlier, by default Ms Excel allows you to select both unlocked and locked cells once the spread sheet is protected. With these two selections checked it is really easy to get back into your spreadsheet. The answer is to basically copy the entire spread sheet, then simply paste it into a new workbook. This has the effect of copying all your data and formulas, yet not the password password encryption. The first spreadsheet can be deleted and you can name this new workbook the same as original. Essentially, we now have the original spreadsheet but minus the password encryption! However, if we had un-ticked the boxes allowing the selection of cells before applying your password, you won't be able to select the spreadsheet and hence will not be able to copy it. To stop anyone from copying and pasting we simply have to un-tick the first two options. But, just be very careful you do not forget your password!

For details about Excel training in your area view our latest post