Google Sheets Data Validation: Rules, Lists, and Tips

Google Sheets data validation lets you control what people can enter in selected cells, using dropdowns, checkboxes, or rules for numbers, dates, text, and custom formulas. In a browser, select the cells and choose Data > Data validation > Add rule; then set the criteria and decide whether invalid entries should be rejected or merely flagged.

What data validation does

A validation rule defines acceptable input for a cell or range. It can standardize a shared spreadsheet—for example, making every person choose a department from the same list—or reduce errors in a form-like worksheet. Depending on the rule, Sheets can show a dropdown, display a checkbox, or check an entry against a condition.

Validation is a guardrail, not a complete data-cleaning or access-control system. It can guide ordinary editing, but it does not prove that an entry is true, and it should not be treated as protection for sensitive information. For instance, a rule can require an email-shaped value, but that does not verify that the address exists or belongs to the person entering it.

Create a rule in Google Sheets

To add a rule in Google Sheets in a web browser:

Select one cell or the range where entries should be controlled.
Choose Data > Data validation, then select Add rule if the rules panel opens without a new rule.
Confirm the cell range and choose a criterion, such as Dropdown, Dropdown (from a range), a number condition, or a date condition.
Set the options for that criterion. For a dropdown, enter the choices or provide the range containing them.
Under advanced options, choose whether to reject invalid data or show a warning. You can also add help text for editors.
Select Done. Try a valid and an invalid entry to confirm the rule behaves as intended.

To change a rule later, select a cell it applies to and open Data > Data validation. The panel lets you edit or remove the rule. If several rules apply to a sheet, check the selected range carefully: a rule intended for one column can cause confusion if it is accidentally applied across a larger range.

Dropdown lists and their sources

Dropdowns are useful when a field should use a small, known set of labels. Choose Dropdown to type options directly into the rule, such as Open, In progress, and Complete. Choose Dropdown (from a range) when the options already live in cells, such as a maintained list on a separate sheet. A range-based list is easier to update centrally; a manually entered list is convenient for a short set that rarely changes.

For example, a project tracker might have status options in Lists!A2:A4 and use that range as the source for cells in the Status column. Keep the source list free of accidental blank cells or duplicate labels, and protect or clearly label the list area if other editors should not change the options. If the dropdown’s source changes, verify the rule still points to the intended range and that newly added choices are included.

Sheets can display dropdown choices in different styles, including a colored chip or an arrow, depending on the rule’s display options. Colors can make a status easier to scan, but do not rely on color alone to communicate meaning. Use distinct, readable labels as well. If users need a choice that is not listed, decide whether the rule should reject it or allow it with a warning; that decision affects how useful a controlled list will be.

Other criteria: numbers, dates, text, and checkboxes

Not every controlled field needs a dropdown. Google Sheets offers criteria for several common kinds of data:

Numbers: Require a value to be greater than, less than, equal to, or within a specified range. A quantity field, for example, could accept whole-number quantities from 1 through 50.
Dates: Check for a valid date or set a condition such as before, after, or within a date range. This can help keep an event-date column consistent.
Text: Require text to contain or not contain particular content, or check whether it is a valid email address or URL. These checks test format, not whether the address or link works.
Checkboxes: Use a checkbox for a simple yes/no or complete/not-complete field. Checkbox values can also be customized in the rule, which is useful when formulas need values other than the usual TRUE and FALSE.
Custom formula: Apply a formula that evaluates to TRUE for an acceptable entry. This supports conditions that built-in criteria do not express directly.

Use the simplest criterion that expresses the real requirement. A date rule can make sure an entry is a date, but a separate rule or process may be needed if the date must also fall on a working day. Similarly, a number range can block an implausible quantity but cannot establish whether the quantity was reported correctly.

Rejecting entries versus showing a warning

When an entry does not satisfy a rule, Sheets can reject it or show a warning. Rejection is appropriate when consistency is essential—for example, a category column used by a summary report should not accept improvised spellings. A warning is less restrictive: it alerts an editor but allows an exception. That can suit a planning sheet where unusual values are sometimes legitimate.

Before choosing rejection, consider the people and workflows that will use the sheet. If an unexpected value must be recorded, a strict dropdown may encourage users to choose a misleading option just to proceed. A warning, an explicit Other option, or a separate notes column may be better. Help text can explain the expected format, such as “Enter a date as a date value, not as a sentence.”

Validation rules do not automatically repair old entries. After adding or changing a rule, review existing data for values that do not meet it. A rule may also be less dependable as a control if people paste data into the sheet or use other tools to update it. Treat validation as an input aid, and use checks, protected ranges, or a review process where the data’s accuracy matters.

Custom formulas for specific conditions

A custom-formula rule is useful when a built-in criterion is not enough. The formula must evaluate to TRUE for the value being entered. References should be written with the rule’s applied range in mind: a relative reference can shift for each row, while dollar signs can lock a row, column, or range.

For example, suppose identifiers go in A2:A100 and each nonblank identifier must be unique within that range. Apply a custom formula rule to A2:A100 using =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). The formula allows a blank cell or an identifier that appears exactly once in the range; a repeated identifier fails the test. The reference A2 is relative so the check follows the row, while the full COUNTIF range stays fixed. This check is not case-sensitive, so values that differ only by letter case are treated as duplicates.

Test custom formulas with examples that should pass and fail before relying on them across a large range. A formula can be logically sound but misapplied if its starting cell does not match the first cell in the selected range. Also remember that formulas can make a rule harder for collaborators to understand; add help text or document the rule where the workbook is maintained.

Common problems and practical fixes

The dropdown arrow is missing: Confirm that the selected cell is inside the rule’s range and that the rule still exists. Open Data > Data validation to inspect the range and display settings.
A valid-looking entry is rejected: Check the actual criterion and formatting. A date-looking string may be stored as text, and a number criterion will not accept text that merely looks numeric.
Unexpected values remain in the sheet: Validation does not clean up existing entries. Review the range, correct old data, and check how the rule handles pasted or imported values.
A range-based dropdown is outdated: Inspect the source range and extend it if the list has grown. Keep the source list in a clearly maintained area so editors know where to add approved choices.
Rules behave differently across a range: Check the applied range and any relative references in custom formulas. A formula designed for row 2 may need to start with the first row of the selected range.

For a shared sheet, test the rule using a normal editor account if possible, and explain what users should do when none of the choices fits. Avoid making the instructions depend on color alone. Clear column headings, examples, and short help text often prevent more errors than a complicated rule.

Google Sheets versus Excel validation

Google Sheets and Microsoft Excel both support dropdowns and validation rules, but their menus and interfaces are not identical. In Sheets, use Data > Data validation; in Microsoft Excel for Microsoft 365 on Windows, the corresponding command is Data > Data Validation. Rule types, editing panels, and some behaviors can differ, so instructions or formulas written for one application should not be assumed to transfer unchanged to the other.

Sheets is browser-based and makes it straightforward to share a live spreadsheet with collaborators. Excel’s validation feature is part of its desktop and web spreadsheet apps, with interface details that vary by platform. If a workbook is exported or converted between the two, review its validation rules and test representative entries rather than assuming every rule will behave identically. For work that depends on complex validation, keep the file in the application where the rules are authored and maintained.

Frequently Asked Questions

How do I add a dropdown in Google Sheets?
Select the cells, choose Data > Data validation > Add rule, and select Dropdown or Dropdown (from a range). Enter the choices or select the source range, choose how invalid entries should be handled, and select Done.

Can Google Sheets reject an invalid value?
Yes. In the validation rule’s advanced options, select the setting to reject input rather than show a warning. Use rejection when entries must follow the rule, and consider a warning when legitimate exceptions need to be recorded.

Can I make a dropdown list from another sheet?
Yes. Choose Dropdown (from a range) and specify the cells containing the approved choices. Keep that source range maintained, and check the rule if you add options beyond the range it currently covers.

Does data validation remove entries that are already invalid?
No. Adding a rule does not automatically correct existing values. Review the cells separately and fix any entries that do not meet the new requirement.

Can I use a formula to validate entries?
Yes. Choose the custom-formula criterion and use a formula that returns TRUE for acceptable values. Match its relative references to the first cell in the applied range, then test both valid and invalid examples.

Is Google Sheets data validation a security feature?
No. It helps guide data entry, but it is not a substitute for access controls, protected ranges, or a proper review process. Do not use it alone to protect confidential information or guarantee that data is accurate.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.