3.4 Data validation and protecting a model

What you'll be able to do

Hand a workbook to someone else and get it back unbroken. Dropdown lists instead of free typing, rules that refuse impossible entries politely, and formulas locked where fingers cannot reach them.

Before you start

Any workbook of yours that other people type into. The formatting and tables workbook will stand in for one.

The work

1. No documented keyboard shortcut for this — use the menu: select the cells that should offer a fixed choice and go to Data → Data Validation. On the Settings tab choose List. Point Source at a range holding the options, such as a Table column or a spill like E2#. The list then keeps itself up to date. Those cells now offer a dropdown, and typing anything else is refused.
2. Same either way: put the options on a sheet, never typed into the dialog. Options that live in cells can be added to by anyone, without opening the dialog again. A Table or a spill grows on its own.
3. No documented keyboard shortcut for this — use the menu: the same dialog holds rules beyond lists. Whole Number between 0 and 100 for marks. Date greater than a start date for bookings. Custom takes any formula that returns TRUE when the entry is valid, so =LEN(A2)=10 holds phone numbers to ten digits.
4. Same either way: fill in the dialog's other two tabs, because a rule that refuses in silence is hostile. Input Message shows a hint when the cell is selected, such as "10-digit mobile, no spaces". Error Alert says what to do instead. The Stop style refuses the entry, and the Warning style allows it with a question. Use Stop for the impossible and Warning for the unusual but possible.
5. Same either way: now the other half, which is protecting the formulas. Every cell is marked Locked to begin with, so first free the cells people type into. Select them, open Format Cells with Ctrl+1, and on the Protection tab untick Locked.
6. No documented keyboard shortcut for this — use the menu: then switch protection on. Go to Review → Protect Sheet and press Enter. Typing cells now accept entries, and formula cells refuse edits with a message. The password is optional and mostly for show, because protection is a guard against accidents, not a lock on a safe.
7. Same either way: walk the sheet as its user for one minute. Tab moves between the unlocked cells only, dropdowns offer their choices, and wrong entries get polite refusals. That walk is the test of whether the work is done.

Try it

Take one sheet that others fill in. Give the category column a dropdown from a Table source. Hold marks to 0 to 100 with a Stop alert that explains itself. Add a hint to the phone column and rule it to ten digits. Unlock exactly the cells people type into, protect the sheet, and then try to break your own work.

Check yourself