Set up data validation

Data validation lets you control what goes into a cell. You can add a dropdown list so people pick from a fixed set of options, or write a rule that rejects a value if it falls outside a range, does not match a pattern, or fails another condition.

Open the data validation dialog

1. Select the cell or cells you want to validate.
2. Open the Data menu and click Data validation.

The dialog shows the current selection in the Cell range field. You can type a different range there, or click the grid icon next to it to select a range with the mouse. When you confirm the range, the dialog comes back with everything you had filled in.

When you click OK, Sheets checks the rule first. If something can't be used, such as a range that names no cells or a list with no options, the dialog stays open and a message under the field says what is wrong.

If you selected a whole column by clicking its header letter, the field shows it as B:B, and the rule covers every row of that column, including the rows below your data. A whole row shows as 2:2.

Add a dropdown list

1. Under Validation condition, choose Drop-down list from the menu.
2. In the field below, type the options separated by commas, for example Yes,No,Maybe. To use values from existing cells instead, click the grid icon next to the field and select the range.
3. Tick Allow multiple selection if you want people to be able to pick more than one option from the list.
4. Click OK.

A cell with a dropdown shows a small arrow at its right edge. Click it to open the list of options, or select the cell and press Alt+Down.

Add a checkbox

1. Under Validation condition, choose Checkbox.
2. Optionally, enter the values that represent the Selected (checked) and Not selected (unchecked) states.
3. Click OK.

The cell displays a checkbox that toggles between the two values when clicked.

Add a number rule

1. Under Validation condition, choose Number, Whole number, or Decimal number, depending on what you need.
2. Choose a condition from the second menu: Between, Not between, Equal to, Not equal to, Greater than, Less than, Greater than or equal to, or Less than or equal to.
3. Enter the value or values the rule compares against.
4. Click OK.

Text length follows the same steps and conditions, but checks the number of characters in the cell rather than a numeric value.

Add a text rule

1. Under Validation condition, choose Text.
2. Choose a condition: Includes, Does not include, or Equal to.
3. Type the text the rule checks against.
4. Click OK.

Add a date rule

1. Under Validation condition, choose Date.
2. Choose a condition: Between, Not between, Equal to, Not equal to, Earlier than, Not earlier than, Later than, or Not later than.
3. Enter the date or dates the rule compares against.
4. Click OK.

Options that apply to all rules

Two checkboxes at the bottom of the dialog apply to every validation type:

• Prohibit input when input data is invalid: blocks the entry and shows a failure notice if someone types a value that fails the rule.
• Show prompt when the cell is selected: displays a hint message when the cell is selected. When this is ticked, a text field appears where you can write the hint.

Without Prohibit input when input data is invalid, a value that fails the rule is kept, and its cell shows a red triangle in the top-left corner. This goes for values already in the cells when you add the rule, too. Select the cell to see a card that starts with "Invalid:" and says what the rule expects. The card opens under the cell, or above it near the bottom of the sheet.

Remove a rule

1. Select the cells whose rule you want to remove.
2. Open Data → Data validation.
3. Click Delete validation.

The rule is removed from those cells.

Rules when you paste or insert

When you copy or cut cells and paste them in the same browser tab, their rules come along. They replace the rules of the cells you paste over, and a copied cell without a rule removes the rule where it lands. Filling cells works the same way. A sort leaves rules where they are.

A row or column you insert inside a rule's range gets the rule. One you insert above or right below the range doesn't.

A rule can't be broken into too many separate pieces. Pasting a cell with a rule and one without down a long column can reach that limit, since the rule would land on every other row. The cells still land, but past the limit they get no rule. An edit that would cut an existing rule into too many pieces removes that rule. The same goes for a fill, a move and the format painter.