Google Sheets Cell Validation: Rules, Lists, and Tips

Google Sheets cell validation lets you control what people can enter in selected cells, using rules such as a dropdown list, a number range, a date requirement, or a custom formula. In a browser, select the cells and choose Data > Data validation, then add a rule and decide whether invalid entries should be rejected or merely flagged with a warning.

What cell validation does

Validation is a set of input rules attached to cells. It can guide someone toward an approved choice, prevent a value outside an allowed range, or alert them that an entry does not meet your requirements. For example, a shared expense sheet might offer a dropdown for payment method, allow only positive amounts, and require a date in the transaction-date column.

Rules apply to a specified range, and a cell can be configured with a particular validation criterion. When a person selects a cell with a dropdown rule, Sheets presents the permitted choices. With other criteria, the person can enter a value and Sheets checks it against the rule. Depending on the rule’s invalid-data setting, the value is either blocked or accepted with a warning.

Validation is useful for consistency, but it is not a substitute for clear instructions or access controls. It does not prove that an entry is true, and it is not a security barrier: people with editing access may be able to change rules or alter the sheet. Treat it as a practical way to reduce routine mistakes.

How to add a validation rule

In Google Sheets on the web, set up a rule from the Data menu. A typical workflow is:

Select the cell or range you want to control, such as C2:C100.
Choose Data > Data validation. The validation controls appear in a side panel.
Choose Add rule if needed, and check the range shown under Apply to range.
Choose a criterion, such as Dropdown, Date, or Number, and configure its choices or limits.
Choose whether invalid data should be rejected or shown with a warning. Add help text if it would clarify what belongs in the cell.
Save the rule, then test it by entering an acceptable value and an unacceptable one.

The range matters: a rule intended for C2:C100 should not accidentally include a heading or unrelated notes. You can edit or remove a rule later by selecting a cell it applies to and returning to Data > Data validation. If several cells need identical behavior, select the full range before creating the rule rather than setting up each cell separately.

Common criteria and when to use them

The best criterion depends on the kind of information the column should contain. A dropdown is effective for a short, fixed set of categories; a range rule suits quantities; and date criteria are appropriate for scheduling or reporting fields. The available criteria include options for numbers, text, dates, checkboxes, dropdowns, and custom formulas. The exact choices shown depend on the criterion you select.

Dropdown: Enter choices directly, such as Pending, In progress, and Complete. This is convenient when the list is short and rarely changes.
Dropdown from a range: Use cells containing the allowed values. This is easier to maintain when the list is long or updated periodically.
Number: Set a condition such as greater than zero or between two limits. It can help keep quantities and scores within a sensible range.
Date: Require a valid date or set a date condition, such as a date before or after a chosen point.
Text: Apply a text-related condition where appropriate, for example requiring text to contain a particular string.
Checkbox: Provide a simple checked or unchecked choice, useful for completion flags or yes/no fields.
Custom formula: Define a rule using a formula that evaluates to TRUE for an allowed entry.

Validation does not automatically make a date column display dates in a particular format. Set the display separately with Format > Number. Similarly, number validation controls what is accepted, while number formatting controls how an accepted value appears. Those are related but distinct tasks.

Build a dropdown that is easy to maintain

For a small list, select the target cells, open Data > Data validation, choose Dropdown, and enter each choice. In a task tracker, for instance, the Status column might offer Not started, Working, and Done. Decide whether the choices should appear as colored chips, an arrow, or plain text using the appearance options available in the rule. Color can make the choices easier to scan, but it does not replace meaningful labels.

If the list is likely to grow, keep the options in a separate area of the spreadsheet and choose Dropdown from a range. This keeps the source list visible and makes updates easier than editing the rule each time. For example, a product-category dropdown can draw from a maintained category list on another sheet tab. When the source list changes, review the rule and test the dropdown to confirm the intended options are available.

Dropdown choices are useful for consistent spelling, but they do not resolve what the categories mean. If two options are easy to confuse, add a note or brief instruction near the column heading. Avoid making a list unnecessarily long: a dropdown with dozens of similar choices can be harder to use than a well-organized input process.

Use a custom formula for more specific rules

A custom formula can validate a value against a condition that the standard criteria do not express conveniently. For example, imagine that scores must be numeric and fall between 0 and 100. With the score cells in B2:B100, apply the rule to that range and choose the custom-formula criterion. The formula below is written for B2, the first cell in the applied range:

=AND(ISNUMBER(B2),B2>=0,B2<=100)

The formula returns TRUE for a number from 0 through 100, including both endpoints, and FALSE for a text entry or a number outside that range. When the rule is applied to B2:B100, Sheets evaluates the relative B2 reference for each row. If the range begins at a different cell, adjust the reference to its top-left cell. A reference anchored with dollar signs can behave differently when a rule is applied across multiple rows or columns, so use relative references deliberately.

Custom formulas should be tested on both valid and invalid examples. Also consider whether blank cells should be permitted. In the example above, a blank is not a number, so it fails the formula; if blank entries are allowed, the formula needs an explicit condition for blanks. A custom rule can become difficult to maintain if it is long, so prefer a built-in criterion when it handles the requirement clearly.

Reject input or show a warning?

When a value fails a rule, Sheets lets you choose between rejecting the input and showing a warning. Reject input is appropriate when a column must contain only approved values, such as a fixed set of workflow statuses. The person entering data must correct the value before it is accepted through normal entry.

A warning is better when unusual values may still be legitimate. For example, a travel-expense sheet might flag an unusually high amount without preventing a supervisor from recording a genuine exception. A warning preserves flexibility, but it relies on users noticing and responding to the message. Help text can explain the expected format or the reason for a restriction; concise guidance such as Enter a whole number from 1 to 20 is more useful than a vague instruction like Invalid value.

Neither setting guarantees that every value in the sheet complies forever. Users can change rules if they have sufficient editing permissions, and copying or importing content can create cases that deserve a review. For a critical workflow, periodically inspect the data and protect the relevant ranges or limit editing access as appropriate.

Common problems and how to fix them

If a dropdown does not appear, first select a cell you believe is covered by the rule and open Data > Data validation. Check Apply to range for a typo, an omitted row, or a range on the wrong sheet. Also check that the selected criterion is actually a dropdown and that it has choices or a valid source range.

Valid entries are rejected: Review the criterion and the exact value being entered. A number stored as text, a date interpreted in an unexpected format, or extra spaces can produce a mismatch.
Unexpected values remain in the cells: The rule may be set to show a warning rather than reject input. Existing data may also need to be checked; creating a rule is not the same as cleaning every old entry.
A dropdown list is out of date: Check the source range and confirm that the intended options are in that range. If the list has expanded, ensure the rule covers the new cells.
The rule works in one row but not another: For a custom formula, check its cell references and the first cell in Apply to range. Relative references shift as the rule is evaluated across the range.
Dates or decimals behave unexpectedly: Check the spreadsheet’s locale and the cell’s number format. Different locales can use different date conventions and decimal separators.

When troubleshooting, test one cell with a clearly valid entry and one with a clearly invalid entry. That helps distinguish a rule problem from a display issue or a mistaken assumption about what the criterion permits.

Google Sheets validation compared with Excel

Google Sheets and Microsoft Excel both support dropdowns and rules that restrict entries, but the interfaces and rule details are not identical. This article’s steps use Google Sheets in a web browser: its path is Data > Data validation, with rules managed in a side panel. In Excel for Microsoft 365 on Windows, the comparable feature is generally opened through Data > Data Validation on the ribbon. Do not assume that a Sheets rule, appearance choice, or custom formula will transfer perfectly when a workbook is converted or opened in another spreadsheet program.

If a file is shared with people using Excel, test the converted file in the program they will use. Check both the allowed values and the user-facing behavior, rather than assuming that a visible dropdown proves every validation setting was preserved. For a team working entirely in Sheets, keep the rule source lists and instructions in the same spreadsheet where collaborators can maintain them.

Frequently Asked Questions

Can Google Sheets cell validation create a dropdown?
Yes. Select the target cells, choose Data > Data validation, and select Dropdown or Dropdown from a range. Add the allowed choices or point to the cells containing them, then save the rule.

How do I allow only numbers in a Google Sheets cell?
Apply a Number criterion to the target range and choose the condition you need, such as greater than zero or between two values. Select Reject input if entries outside the condition should be blocked, and test the rule with representative values.

Can I make a dropdown list from another sheet?
Yes. Put the choices in a range in the spreadsheet and use Dropdown from a range as the criterion. Check the source range when options are added or changed so the rule reflects the list you intend to offer.

Does validation fix data already in the cells?
No. A rule governs entries going forward; it does not automatically correct existing mistakes. Review current values separately, and use the rule’s warning or rejection behavior to help prevent future inconsistent entries.

Can I use a formula for Google Sheets validation?
Yes. Choose the custom-formula criterion and enter a formula that evaluates to TRUE for allowed entries. Set the correct applied range, use references that align with its first cell, and test valid, invalid, and blank cases.

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.