Every spreadsheet veteran knows the failure mode: one mistyped region name, one unit price entered as 1,200 instead of 12.00, and every SUMIFS, pivot and chart downstream quietly lies. Data validation in RixSheets closes that door. You define what a cell accepts — a choice from a list, a number inside limits, a date within a window — and the grid rejects anything else with a clear message. Clean data in means trustworthy analysis out.
The rule types
The validation dialog offers a rule for every common mistake:
| Rule type | Restricts the cell to | Typical use |
|---|---|---|
| List | Values you define or a source range | Region names, status codes, category picks |
| Whole number | Integers within limits | Quantities, headcount |
| Decimal | Numbers within limits | Prices, rates, percentages |
| Date | Dates within a window | Order dates, deadline fields |
| Custom | A condition you write | Cross-field rules and formats |
| Text length | Strings up to a length | Codes, account numbers |
The list type is the workhorse. Point it at a range on a setup sheet — the master list of regions, products or employees — and the cells become dropdowns that can only contain values from that range.
Designing lists that stay maintained
The list rule is only as good as the range behind it, and three practices keep it healthy:
- Keep master lists on a dedicated setup sheet, one named range per list. When a new product line launches, you extend one named range and every dropdown in the workbook offers it immediately.
- Sort the list so dropdown options are findable; an unsorted dropdown teaches users to type instead, which defeats the rule.
- Never let validation lists and entry data share a sheet in a workbook that travels — a deleted row in an entry area must not take a master value with it.
Name each list in the Name Manager and the validation source becomes self-documenting: the rule reads "source: regions" rather than "source: Lists sheet, C2 through C19, hopefully".
Add a dropdown list
- Select the cells that should use the list — for example C2:C500 on an orders sheet.
- Open Data, Data Validation.
- Choose List as the rule type.
- Enter the allowed values directly, or point the source at a range such as the Region column on a Lists sheet.
- Add an input message: "Choose a region from the list" — it appears when the cell is selected.
- Set the error alert: stop style, with a message such as "This region is not in the master list."
- Confirm and test: the cells now show a dropdown arrow, and a typed value outside the list is refused.
Number, date and custom rules
For quantities and money, whole-number and decimal rules with sensible minimums stop sign errors and typos before they spread. Date rules keep an aging report honest by refusing dates outside the fiscal year. The custom rule type accepts a condition you write, which covers everything else — format patterns, cross-field checks such as "end date must follow start date", and referential rules across sheets.
Validation rules copy with the cells: copy a validated cell and paste its formatting or contents to a column, and the rule travels. You can also select a whole column before opening the dialog so the rule covers every future row.
Messages people actually read
The input message and the error alert are part of the feature, not decoration — they decide whether users experience validation as help or as an obstacle:
- Input messages should say what to do, briefly: "Choose a region from the list" or "Enter the date the order shipped, not the date it was placed."
- Error alerts should say what went wrong and what to do instead: "This region is not in the master list. Pick one from the dropdown or ask for the list to be updated."
- Reserve the stop style for rules that protect money and compliance — amounts, dates, mandatory fields. For preferences, a warning style that allows an override with confirmation is friendlier and still catches typos.
An error message that explains itself eliminates the support conversation; one that merely says "invalid entry" generates three emails a week.
Pair validation with the rest of the toolkit
Validation prevents bad data; these features catch and present what slips through or already exists:
- Conditional formatting highlights values that violate expectations — negatives, blanks, out-of-range numbers — even before validation existed on the sheet; see conditional formatting.
- Sort and filter then groups the oddities for correction, and duplicate removal finishes the job; see sort, filter and dedupe.
- Pivot tables are the reason all this matters: a validated category column means every pivot row is real; see pivot tables.
The strongest habit is a dedicated setup sheet: master lists on the left, validation pointing at them, and the entry sheet clean by construction.
What to explore next
- Sort, filter and dedupe — clean up data that predates your rules.
- Conditional formatting — flag suspicious values visually.
- Pivot tables in RixSheets — the payoff of trustworthy categories.
- Format Cells dialog — lock formula cells while inputs stay editable.