How to Use Data Validation in Google Sheets (Dropdown Lists & Input Rules)

When multiple teammates collaborate on a single Google Spreadsheet, inconsistent data entry can quickly break formulas, ruin summary dashboards, and distort reports. Common issues like typos (e.g., "Complete" vs "Complted"), unexpected text in numeric columns, and incorrect date entries can cause hours of cleanup work.

The solution is Data Validation. Data validation enforces strict entry rules on selected cells, preventing invalid inputs and providing clean, clickable dropdown lists. In this guide, you will learn how to create color-coded dropdown chips, set custom validation rules, and reject bad data before it enters your sheet.

1. What Is Data Validation in Google Sheets?

Data validation is a native feature that restricts what a user can type or select inside a cell. If someone attempts to enter data that violates your rule, Google Sheets can either display an informational warning or reject the input entirely.

You can apply data validation to restrict entries based on:

• Dropdown lists: Choose from fixed options or dynamically from a cell range.
• Numbers: Require a value between two amounts, greater than zero, or equal to a target.
• Text: Enforce valid email addresses, URLs, or required substrings.
• Dates: Ensure valid calendar dates within a defined project window.
• Custom Formulas: Apply complex boolean logic (such as disallowing duplicates).

2. How to Create a Modern Dropdown List (Dropdown Chips)

Google Sheets features visual "chip" dropdown menus that can be color-coded for instant status tracking.

Method 1: Fixed Manual List

1. Select the cells where you want the dropdowns to appear (for example, E2:E50).
2. Click Data in the top menu and select Data validation.
3. In the right sidebar panel, click + Add rule.
4. Under Criteria, keep the default option set to Dropdown.
5. Type your options into the text boxes (e.g., Not Started, In Progress, Done).
6. Click the circle next to each option to customize its badge color.
7. Click Done.

Method 2: Dropdown from a Cell Range (Dynamic Updates)

If your list of options changes frequently (such as a list of employees or inventory SKUs), reference a dedicated range:

1. Open the Data validation sidebar and select Dropdown (from a range) under Criteria.
2. Click the grid icon and select your source list (for example, Lists!A2:A20).
3. Click Done. Whenever you add or edit a name in the source list, the dropdown menus update automatically across the entire sheet.

Display Style Tip: Under Advanced options in the sidebar, you can switch the visual display from modern Chip style to standard Arrow or plain Plain text.

3. Restricting Inputs: Rejecting Invalid Data

By default, Google Sheets will show a tiny orange flag in the cell corner if someone types an invalid value, but it still allows the bad data to remain. To lock cells down completely:

1. In the Data validation sidebar, expand Advanced options.
2. Under If data is invalid, select Reject the input.
3. Check the box for Show help text for a selected cell.
4. Enter a clear explanation (for example: "Please pick a valid status from the dropdown list.").

When this setting is active, any attempt to paste or type an unlisted value triggers an immediate popup dialog, keeping your dataset 100% clean.

4. Restricting Numbers, Dates, and Text Formats

Data validation extends far beyond dropdown menus. You can control the exact shape of your raw data:

5. How to Remove Data Validation Rules

If you need to clear rules and dropdown chips from an existing table:

1. Select the target cells.
2. Go to Data > Data validation.
3. In the right sidebar panel, click the rule matching your selection.
4. Click Remove rule at the bottom of the panel.

Recommended Next Steps

Setting up clean input validation is the foundation of dependable data entry and reporting. Continue refining your spreadsheet workflows with these practical tutorials:

• Build Multi-Level Menus: Need second dropdowns that automatically adapt based on the first selection? Read our guide on how to create dynamic dependent dropdown lists in Google Sheets.
• Highlight Key Statuses: Make your selected dropdown options pop visually with our tutorial on how to highlight a row in Google Sheets based on another cell value.
• Protect Input Columns: Prevent team members from accidentally modifying rules or templates using our guide on how to lock and protect specific cells in Google Sheets.
• Automate Billing: Connect your validated product dropdowns to automated billing sheets in our tutorial on building an automated invoice generator in Google Sheets.