Protecting Data Validation cells

A downside of Data Validation is that users can purposefully or inadvertently remove rules from cells by copying and pasting. Excel doesn’t ask, are you sure?; it simply erases any Data Validation settings. The following steps show you how to protect your Data Validation rules:

1. Select any cell on the Data Validation worksheet, and then choose Home | Find & Select | Data Validation to select all cells that have Data Validation rules assigned.
2. Press Ctrl+1 (+1) or choose Home | Format | Format Cells | Protection.
3. As shown in Figure 5.28, clear the Locked checkbox and then click OK.

Figure 5.28 – Unlocking cells

4. Choose Review | Protect Sheet, optionally enter and confirm a password, and then click OK.

Tip

You must use the Protect Sheet command to protect your Data Validation settings. Unlocking the cells is only a precursor step that on its own does not offer any protection...