ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Password Protection

In order to prevent others from altering your spreadsheet or to keep your precious formulas secret it is possible to protect a worksheet or a whole workbook from being changed.  By locking  individual cells it is also possible to prevent others from altering values on your worksheet.  In the interest rate example on the slider bar page we may want to allow others to change the Amount Borrowed and Months but prevent changes to the formulas in the Interest Rate and Payment cells and hide these formulas from others.

Before applying the password protection we need to ensure that the Amount Borrowed and Months cells will be able to be changed by others.  It is also important to allow the hidden adjustment cell in A3 to be changed or our slider bar won't work.  Highlight these cells and click on the Format drop-down menu and choose Cells.  Click on the Protection tab in the window that appears as shown below and then click to remove the tick beside Locked for these cells.

When you click OK these cells will now be unlocked.  The next step is to select the Interest Rate and Payment cells (these can be selected at the same time by holding down the CTRL key while clicking them).  Click on the Format drop-down menu again and choose Cells.  Set the Protection section of the window to both Locked and Hidden as shown below .  This will prevent others from changing the values in these cells and hide  the formulas from them.  However, it is still possible to copy values from hidden cells in a protected workbook but only the values, not the underlying formulas, will be copied.

Click OK and you are now ready to protect your workbook or worksheet.  Click on the Tools drop-down menu, select Protection and then Protect Sheet or Protect Workbook depending on what you want to protect.

You can tick the boxes shown above to choose what you want to protect:

Contents will prevent changes to locked cells and viewing of hidden rows, columns and formulas.

Objects will prevent changes to graphics on worksheets or charts.

Scenarios will prevent changes to scenario definitions.

If you want to totally prevent others from changing the protected items you will have to enter a password.  If you do set a password, make sure you don?t lose or forget it as you will not be able to access the protected items without it.

If you want to prevent others from changing the structure of the whole workbook you can click on the Tools drop-down menu and choose Protection and then Protect Workbook.  A window appears giving you the option to protect the structure and to force the workbook to always open in the same-sized window.  It is not a good idea to set the window option unless you are sure that you always want to force the spreadsheet to open in a fixed-size window.  To totally prevent others from altering the workbook you can set a password but, again, be careful not to lose it.

None of these protection methods actually prevent people from opening your spreadsheet and looking at it, they just prevent them from changing the data and/or structure.  To require a password when your spreadsheet is opened you need to click on the File drop-down menu and choose Save As.  Click on the Tools icon at the top of the Save As window and choose General Options as shown below .

Enter a password in the Password to open box shown below .  You can also enter a different password in the Password to modify box to allow others to change your spreadsheet if they are given that password.

Click OK and you will be asked to confirm the password for opening the workbook as below .

Click OK when you have confirmed the opening password and you will be asked to confirm the password for modifying the workbook as below .

Click OK and save your workbook, it will now be password protected.  When you next open the file you will be asked for the opening password.

When you have entered the correct password and click OK you will be asked for the write access (modify) password.  If you do not know this password you can click the Read Only button shown below to open the workbook in read-only mode.

To remove password protection you will need to open the workbook with write access and then click on the File drop-down menu and choose Save As.  Click on the Tools icon again to access the General Options and double-click on each of the passwords in turn to highlight and then delete them.  You can also change the passwords by double-clicking them and altering them to something else.  When you save your workbook again it will be saved with the amended or removed passwords.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk