How to stop people entering the wrong value

Give the cells a data validation rule and set its alert to Stop. In Grid by Zaweba, data validation restricts what can be typed, whether that's a whole number in a range, a date after today or an item from a list, and a Stop alert refuses anything that breaks the rule. An input message can explain what is expected before anyone types.

Deciding what counts as right

Start by writing down, in plain words, what each column should hold. Quantity is a whole number from 1 to 100. Delivery date is today or later. Status is one of four words. Email must contain an @. Each of those becomes a rule.

Validation can check numbers, decimals, dates, times, text length, a list of allowed values, or a custom formula. The custom formula is the most flexible: any formula that returns TRUE for a good entry and FALSE for a bad one will do. REGEXTEST is handy here for anything with a pattern, such as a postcode or a product code.

Setting up a rule step by step

Select the cells, open data validation, and choose the kind of rule and its limits. Then set the alert. Stop refuses the entry and makes the person try again. Warning asks whether they're sure and lets them carry on. Information shows a note and accepts the value. Write your own title and message for the alert, something that says what to do rather than only that it's wrong.

Add an input message too. It appears when the cell is selected, such as Enter a quantity from 1 to 100, and it heads off most mistakes before they happen.

Catching bad data that's already there

Rules apply to new typing, not to values already in the cells. Circle Invalid Data marks every existing entry that breaks the rules, so after adding validation to an old sheet you can see straight away what needs fixing. Conditional formatting with a formula rule is another way to keep problems visible over time.

Going further with protection

Validation controls what goes into a cell. Protection controls which cells can be changed at all. In Grid, sheets can be protected, and then locked cells refuse edits. Format Cells has a Protection tab where you choose which cells stay locked. The usual pattern is to clear the Locked setting on the input cells, leave formulas and headings locked, then protect the sheet. People can type only where they're meant to, and only values your rules allow.

Be aware that protection is at sheet level; workbook-level protection isn't built in Grid, so it won't stop someone adding or deleting whole sheets.

Limits worth knowing

Pasting can get round validation. A value pasted over a validated cell may arrive without being checked. In a sheet shared with many people, run Circle Invalid Data now and then as a check.

A rule that's too strict causes its own problems. If people keep hitting the Stop alert for a legitimate value, they'll find workarounds. Use Warning for borderline cases, such as an unusually large order.

Rules are saved in .xlsx and .zagr files, and they work in other spreadsheet programs that open them. They're not written to .ods files.

Most spreadsheet errors start with one bad entry. A few validation rules in Grid by Zaweba, with a clear message beside each, stop them at the door.