Google Sheets data validation and protection

Data validation checks an entry when it is made. It can reject a value, warn about it, or offer a dropdown. Protection limits who can change cells, ranges, or sheets. Validation is about the content of an input; protection is about the surface around it.

Neither is a security control. A user with edit access can paste over a validated cell, and an editor can remove protection from a range. Use these features to make the correct action easy and the accidental action hard, not to conceal data.

Start by defining the input surface: the cells a user is expected to complete, the cells that are calculated, and the cells that are reference data. Every control should map to one of those categories.

Dropdowns are the most useful rule for categories, owners, and status. Point the rule at a range on a reference sheet rather than typing a list of items. The range can grow without editing every rule, and a named range keeps the reference stable when columns move.

Numbers need a type and a bound: whole numbers for quantities, decimals for rates and amounts, and a sensible minimum. Dates need a real date rule, not a text rule, or sorting and filtering will fail later. Add help text with the expected unit or format.

Custom formulas cover the rules the menu does not. The formula is written for the top-left cell of the selected range and uses relative references to apply to each row. =COUNTIF($B$2:$B, B2) = 1 rejects a duplicate key. =AND(ISNUMBER(C2), C2 > 0) rejects blanks, text, zero, and negatives in one test.

Reject input stops an invalid entry. Use it for keys, dates, and categories that feed formulas, where a wrong value corrupts a total. Warning allows the entry but marks it. Use warnings for soft rules where an exception is legitimate, such as a discount above the standard limit.

Write a message that tells the user what to do. Rejected input is a dead end; a message that names the allowed format and gives an example turns it into a correction. The message is part of the control, not decoration.

Adding validation does not clean existing data. Audit the current values first, fix or flag them, and then apply the rule. A sheet with a validation rule and a column of legacy exceptions is worse than no rule, because it looks controlled.

Protect calculation and output sheets, and leave the input cells editable. In Sheets, sheet protection can exclude specific ranges, or you can protect selected ranges and leave the rest open. Both work; the exclusion approach is easier to explain to users.

Choose between a warning and a restriction. A warning lets trusted editors proceed and suits a small team. A restriction limits editing to named people and suits a published report. Neither stops a viewer from copying the file, and an owner can always change the protection.

Scripts need thought. An installable trigger runs as its creator, so a protected range may block a write if the creator lacks access. Test the script against the protected sheet, and give it a narrow admin range rather than unprotecting the whole sheet.

Keep reference lists on one sheet, give them a name, and point every dropdown at that name. When a new category is approved, add it once. Scattered lists of items are how two dropdowns end up with different spellings of the same category.

For a period close, protect the closed sheet and add a small adjustment area for authorised changes. Log each adjustment with a reason and an owner. The alternative, unprotecting the sheet at month end, removes the control exactly when it matters.

Review protection after staff changes. A departed editor may still have access through a group, and a new analyst may be unable to update a list they own. Access reviews are part of the control, not an annual chore.

Test with the behaviour you are trying to stop: paste a block into the input area, type a duplicate key, enter a date as text, and run the script that writes to a protected sheet. A control that has never been tested is a hope.

Check the user path as well. A validation message should appear where the user is looking, a protected cell should still allow copying, and a legitimate exception should be possible without removing protection.