Protecting the Worksheet Against Unwanted Changes

Even spreadsheets that need to be updated on a regular basis (and therefore are not candidates for passwords to open or modify the workbooks) often need protection. This is especially true with spreadsheets where routine data entry is entrusted to users with little or no understanding of Excel's workings who, if they inadvertently replaced a formula with an entry, would have no idea how to restore it. To keep the formulas and standard text in a spreadsheet safe from such unwarranted changes, you need to protect its worksheet.

Before you rush off to take me at my word and turn protection on in all the worksheets you entrust to this type of data-entry person, you need to have a rudimentary understanding of how worksheet protection operates in Excel.

First off, be aware that any cell in any Excel workbook can have one of two different protection formats assigned to them: either locked or unlocked, and hidden or unhidden. Whenever you begin a new spreadsheet, all the cells in the workbook have the locked and unhidden protection formats assigned to them. However, this particular formatting doesn't come into play until you turn on protection in the worksheet by choosing Tools → Protection → Protect Sheet. At that time, you are then prevented from making any editing changes to all locked cells and from viewing the contents of all hidden cells on the Formula bar when they contain the cell pointer.

Practically speaking, this means that you need to go through the sheet removing the Locked protection format from all the cell ranges where you or your users still need to be able to do data entry and editing even when protection is turned on in the worksheet.

When setting up spreadsheet templates, unlock all the cells where users need to input new data and keep locked all the cells that contain headings and formulas that never change. Then turn on worksheet protection prior to saving the file in the template file format. Formulas and stock text in all spreadsheets generated from the template will automatically be protected but still give users access to the areas that require data entry.