Learn how to Password Protect Single Cells in Microsoft Excel

Published: 10th May 2013
Views: N/A

It can be very beneficial to password protect spreadsheets to make sure that other people can not accidentally (or even deliberately) overtype valuable data or formulas and functions. In many instances though it would be more advantageous to protect only some cells and leave others open. Initially this would look like an unlikely thing to be able to do, but it's in fact really easy to achieve.

First Set up The Worksheet

Initially we must understand what occurs when we password protect a spreadsheet in Microsoft Excel. Let us begin by getting an understanding of what occurs when we password protect a worksheet. By default, all cells in Excel are locked when password protection is applied to the spreadsheet. The default setting in Ms excel is for all cells to be locked when we encrypt the spreadsheet. What we have to do first is indicate any cells which we do not propose to protect. This is established by first choosing the cell or range of cells which are to be left un-protected, then right-click with the mouse to show the Format dialogue box. Select the 'Protection' tab and you will see two tick-boxes marked 'locked' and 'hidden'. By default, the Locked box will have a tick inside. The effect of this is that if we apply a password to the worksheet, all of the cells are going to be locked at the same time. We simply just need to un-tick the box then save the changes by clicking on 'OK'. The worksheet has now been prepared for password protection.

How to Password Protect the Spreadsheet

To set a password we first need to choose the 'Review' tab and next click 'Protect Sheet'. A popup now appears having a box in which we will key in a password. Actions that are allowable when the spreadsheet is encrypted can be chosen by checking the tick-boxes which are shown under the password field. You'll notice that the first two boxes are checked which permits the selection of locked and unlocked cells. Now key in a password into the field and then click OK. Now we are expected to re-enter the same password to double-check that it has been typed in correctly. All of the cells within the worksheet will now be read-only, apart from those which we have marked as un-locked. To remove password protection, just click 'Unprotect Sheet' then type in your password.

How to Unprotect the Spread Sheet if You Forget Your Password

It might seem initially that if you lose your password then there is no chance to get back in to your spreadsheet. There is however a technique to bypass the password protection, however it does depend on which actions you made permissable when we set up the password. If you recollect, the locked and unlocked boxes were ticked by default when we entered our password. If you have left these at the normal settings, it is really easy to make the worksheet editable again. All that you need to do is to select the entire worksheet, then copy and paste it into a new blank workbook. This will have the effect of copying all your data and formulas, but not the password password protection. All we need to do now is to delete the original spreadsheet and then name your new workbook to the same. Effectively, we have now got the original worksheet but it is no longer password-protected. But, if we had un-ticked the boxes enabling the selection of cells before applying your password, you will not be able to select the spreadsheet and so won't be able to copy it. This is beneficial if you wish to stop other people who are aware of the strategy from doing the same thing. However, just be careful you don't forget your password!

The author is the Managing Director of the European training Company Paul Brown Associates Ltd She offers over nine years practical experience in training organisations large and small across the Country. For more details visit their website at www.paulbrowntraining.org.

Report this article Ask About This Article

More to Explore