Data validation

What it is

Rules that limit what can be entered into cells: a dropdown list with coloured options, a checkbox, a number, a date, text, or a custom formula. Rules are set up in the Data validation side panel to the right of the sheet. The panel is opened by the list button on the toolbar (on a narrow screen, under More tools). How to choose values in cells with a list or a checkbox is described in Dropdown lists and checkboxes in cells.

How to use it

1. Select cells and click Data validation. A panel opens to the right of the sheet, and the button stays pressed. The sheet does not close: while the panel is open, you can select cells and enter values. If the active cell already has a rule, that rule opens; otherwise the form is ready for a new rule for the selection.
2. At the top of the panel is the list of the sheet’s Rules (when empty, No rules yet) and the button Add rule. A rule from the list opens in the form, and its first range is selected on the sheet.
3. Ranges: the cursor is already in this field, and the field listens to the sheet. Outline the cells with the mouse, and the address is entered by itself. A new area replaces all the text in the field. While the field waits for a selection, it has a coloured frame. The field stops listening to the sheet when you move to another field of the panel, or press Escape or Enter in it (Enter also saves the rule). To enter several ranges, separate them with commas, for example A1:A20, C1:C20.
4. Validation type: List, Checkbox, Number, Date, Text or Custom formula.
   • List: fill in Options. For each option, set the colour, and use the buttons Move up, Move down and Delete option. A new option is added with the Option button. The Multiple values checkbox allows choosing several values. Display: Chips, Arrow or Text. In Text there is no arrow; the list opens by clicking the value or pressing Enter.
   • Checkbox: by default it stores TRUE/FALSE. Use custom values instead of TRUE/FALSE sets what is written into the cell when it is ticked (Checked) and when it is unticked (Unchecked).
   • Number: between, not between, greater than, greater than or equal to, less than, less than or equal to, equal to, not equal to.
   • Date: valid date, before, after, on, between, not between.
   • Text: contains, does not contain, email address, website address, length no more than, length between.
   • Custom formula: a condition such as =A1>0, evaluated relative to the first cell of the range.
5. On invalid input: Reject means the wrong value is not saved. Allow means it is saved, but the text of the cell is underlined with a wavy line.
6. Help text when selecting a cell: a short text that appears next to the selected cell.
7. Click Save: the panel stays open, and the rule is selected in the list. Delete (the bin at the bottom of the panel, only for a saved rule) removes the rule, and the form is ready for a new one. Cancel returns the form to the rule of the active cell, or to a new rule for the selection.
8. To close the panel, click Close in its header, press Escape, or click Data validation once more. While the Ranges field is listening to the sheet, the first Escape only stops the listening, and the second closes the panel. The width of the panel can be changed by dragging its left edge. If you switch to another sheet, the form starts over from that sheet’s selection.
9. Only one workbook panel is open on the right at a time. If you open Comments, Conditional formatting, Agent or another panel, Data validation closes, and vice versa (for details, see Spreadsheets (workbooks)).

Limits

• Only those who can edit the spreadsheet can create and change rules.
• A sheet can have up to 1000 rules. A rule can have up to 100 ranges, and they must not overlap.
• A list can have up to 500 options, each up to 500 characters, without duplicates.
• The help text can be up to 500 characters. The condition value for a number or a date can be up to 200 characters, and for text up to 500.
• A rule does not erase what is already in the cells: values that do not fit remain visible.
• On a summary sheet, in a version view and in a spreadsheet opened for viewing only, there is no panel. An open panel closes when you go there, and does not come back by itself.
• On a phone, the panel stands under the sheet at full width and takes up to 45% of the screen height; the Rules list takes up to 20%. The list and the fields scroll together, and Save is always visible at the bottom. Selecting cells on a phone is awkward, so type the address instead, for example A1:A20.

If something goes wrong

What you see on the screen	Why	What to do
Check the ranges	A range is written incorrectly, or goes beyond the sheet	Fix it, for example A1:A20
Check the options	An empty or duplicate option	Fill in the option, or delete the extra one
Fill in the condition value, or Enter both condition values	The numbers or dates for the condition are missing	Enter the values
Enter a condition formula	Custom formula is selected, and the field is empty	Enter the formula
The “checked” and “unchecked” values must differ	Both checkbox values are the same	Set different values
Check the rule	The rule did not pass the check as a whole (for example, the ranges overlap)	Check the ranges and the condition
When typing: Choose a value from the list, Number from 1 to 10, Enter a date, and so on (in the interface language; the limits are as the rule’s author wrote them)	The value does not pass a rule set to Reject	Enter a suitable value, or change On invalid input to Allow
The text in the cell is underlined with a wavy line	The value does not pass a rule set to Allow	Correct the value, if needed
The address in Ranges does not change when you select cells	The field stopped listening to the sheet: you moved to another field, or pressed Escape or Enter in it	Click the field again and outline the cells
The panel closed by itself	Another workbook panel opened, you moved to a summary sheet, or you opened a version from the history	Click Data validation again