Excel Worksheet Protection
How to protect a Worksheet
Click on Tools-Protection-Protect Sheet – enter password (optional)
By default this will: - Protect all cell contents from changes or overwriting
- Prevent addition and deletion of columns and rows
- Prevent sorting,cutting and pasting of cell contents
It will not prevent:- Copying data from the worksheet to another worksheet program
- Deletion or renaming of the protected sheet (see WorkBook Protection)
Locking / Protecting Cell Contents
Cell contents are protected or ‘Locked’ by default which means that clicking Tools-Protection-Protect Sheet will automatically protect the cell contents.
This default can be turned off by
Selecting a cell or range of cells then clicking- Clicking Format-Cells-Protection and unchecking the ‘Locked’ property.
- Clicking Tools-Protection-Protect Sheet to protect the ‘Locked’ cells on the sheet.
This is useful when there is requirement for unprotected user-input cells and protected formulas cells.
Hiding Formulas
Formulas are displayed by default in the formula bar. Users can see both the formula and the results of the formula.
Formulas can be hidden by: - Selecting a cell or range of cells
- Clicking Format-Cells-Protection and checking the ‘Hidden’ property.
- Clicking Tools-Protection-Protect Sheet to hide the formula calculations on the selected cells displaying only the formula results.
Protecting objects and scenarios
Objects (images, autoshapes, controls) and scenarios are protected by default when worksheet protection is enabled.
To leave objects and/or scenarios unprotected click on Tools-Protection-Protect Sheet and uncheck the Object and/ or Scenario checkboxes.
Improved Protection in Excel 2002 +
“To protect your worksheet in Microsoft Excel prior to the 2002 version, you would lock the cells that you wanted to prevent users from changing. Unfortunately, your users could not perform basic tasks such as inserting rows or columns, or formatting the cells of that same worksheet.
Thankfully, the worksheet protection model in Excel 2002 is much richer than earlier versions. You can now allow users to perform basic worksheet tasks such as inserting and deleting cells while the worksheet is protected. You can assign permissions to specific cell ranges, allowing only certain users to edit specific portions of your worksheets. ”
More details at The Microsoft Developer Network Library
|
