Data validation and drop-down lists in Grid by Zaweba

A spreadsheet is only as good as what's typed into it. A date entered as text, a quantity of minus three, a region spelt four different ways: each one quietly breaks a total or a lookup somewhere else. Data validation in Grid by Zaweba stops these problems at the moment of typing, and finds the ones that are already there.

Setting a rule

Select the cells to protect and add a validation rule. Rules restrict what can be entered. Pick a rule that matches what the cell is for: a quantity column should hold sensible numbers, a booking date should hold a date, and a status column should hold one of a few agreed words. The tighter the rule fits the job, the fewer surprises later.

Drop-down lists

A list rule adds an in-cell drop-down, so people pick from the options instead of typing. That's the most useful kind of validation for anything categorical: regions, departments, statuses, product types. It ends spelling variations and makes filters, pivot tables and lookups work properly, because every entry matches exactly.

Keep the list's options in a range on a settings sheet rather than typing them into the rule. Give that range a name with Define Name, then point the rule at the name. Adding an option later is one edit in one place, and every drop-down that uses the name picks it up.

Input messages and alerts

An input message appears when someone selects the cell, explaining what to enter. "Enter the date the order was sent" or "Pick a region from the list" saves guesswork, especially on a sheet that someone else fills in.

When an entry breaks the rule, an alert appears in one of three styles. Stop refuses the entry outright. Warning explains the problem and asks whether to continue. Information mentions it and lets the entry through. Use Stop where a wrong value would break something, such as a code that feeds a lookup, and Warning or Information where unusual values are possible but worth a second look, such as an expense over a certain amount.

Finding bad data that's already there

Rules apply to new entries, not to what was in the cells before. Circle Invalid Data checks the existing contents and marks every entry that breaks the rules, so you can work through and fix them. Run it after adding validation to an old sheet, or after bringing in data from elsewhere, so nothing that predates the rules slips past.

Making it work in practice

Validation is copied along with cells when you copy within Grid, so set it on the first row of a template and fill or copy it down. Combined with a protected sheet, where only the input cells are unlocked, it makes a form that's hard to break: people can only type in the right places, and only the right kinds of thing.

Keep messages short and friendly. An alert that explains the rule, "Quantity must be a whole number of 1 or more", is far more helpful than a bare refusal.

Validation is saved in the workbook, including in .xlsx files, so a form you build in Grid by Zaweba keeps its drop-downs and rules when you send it to someone else.