Data validation rules in Google Sheets - Step-by-Step Guide

Introduction

Data validation rules help you control what kind of information can be entered into a cell. This keeps your data clean and avoids mistakes by limiting inputs to certain types or lists.

When you want to allow only numbers between 1 and 100 in a score column.

When you want users to pick a country from a list instead of typing it.

When you want to prevent empty cells in a required field.

When you want to allow only dates after today in a deadline column.

When you want to restrict entries to whole numbers only.

Steps

Step 1: Select the cell or range where you want to apply data validation

- Google Sheets grid

The selected cells are highlighted

Step 2: Click Data

- top menu bar

The Data menu dropdown opens

Step 3: Select Data validation

- Data menu dropdown

The Data validation pane appears on the right side

Step 4: Choose the criteria type from the Criteria dropdown

- Data validation pane

Options like List from a range, List of items, Number, Text, Date, Checkbox appear for selection

💡 Pick 'List of items' to type your own list separated by commas

Step 5: Set the specific rule details based on your criteria choice

- Data validation pane

Input fields appear to enter numbers, dates, or list items

Step 6: Check or uncheck 'Show dropdown list in cell' if available

- Data validation pane

A dropdown arrow appears in the cell for list selections if checked

Step 7: Choose what happens on invalid data by selecting 'Show warning' or 'Reject input'

- Data validation pane

Invalid entries either show a warning or are blocked

Step 8: Click Save

- Data validation pane

The rule is applied and the pane closes

Before vs After

Before

Column A allows any text or numbers, users enter inconsistent country names like 'USA', 'us', 'United States'

After

Column A only allows selection from a dropdown list with 'USA', 'Canada', 'Mexico', preventing typos

Settings Reference

Criteria

📍 Data validation pane

Defines the type of data allowed in the cell

Default: List from a range

Show dropdown list in cell

📍 Data validation pane

Shows a dropdown arrow for easy selection when using lists

Default: Checked for list criteria

On invalid data

📍 Data validation pane

Controls whether invalid data is blocked or just warned about

Default: Show warning

Appearance

📍 Data validation pane

Displays a message to guide users on what to enter

Default: Unchecked

Common Mistakes

Not selecting the correct cell range before applying validation

The rule applies only to selected cells, so other cells remain unrestricted

Always select the exact cells you want to control before setting data validation

Choosing 'Show warning' but expecting invalid data to be blocked

Show warning lets users enter invalid data but flags it, it does not stop entry

Select 'Reject input' to block invalid entries completely

Typing list items with spaces after commas causing extra spaces in dropdown

Extra spaces become part of the list items and confuse users

Type list items without spaces after commas, like 'Red,Green,Blue'

Summary

Data validation rules control what users can enter in cells to keep data clean.

You can limit entries by number, date, list, or custom formulas.

Choose to warn or block invalid data and optionally show dropdown lists for easy selection.