Data Validation

Using data validation doesn't rely on worksheet protection at all, and instead simply prevents accidental overtyping of formula cells. Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.

To see what we mean, select any single cell, press Ctrl-G or select Home → Find & Select → Go to Special, or press F5 and click Special (pre-2007, Edit → Go To... → Special). Nowselect Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.

With only the Formula cells selected, select Data → Data Validation, under Data Tools options (pre-2007, Data → Validation). Then, select the Settings page tab, choose Custom from the Allow: box, and in the Formula box, enter ="". Click OK.

This method will prevent a user from accidentally overtyping into any formula cells, although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel's features are still usable on the worksheet.