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
No comments:
Post a Comment