Validate Data in Google Sheets: Rules and Drop-Downs

To validate data in Google Sheets, select the cells, choose Data > Data validation, then add a rule that limits entries to permitted values, dates, numbers, or a custom condition. Choose whether invalid entries should be rejected or merely flagged; for a simple controlled list, use a drop-down rule.

What data validation does

Data validation is a set of rules attached to cells or a range. It helps guide people toward consistent entries and can prevent or flag values that do not meet the rule. For example, a project tracker can restrict a Status column to Not started, In progress, and Complete. A survey sheet might allow only dates in a specified period, while an expense log can require amounts to be numeric and within a sensible range.

Validation is useful when multiple people enter information, when later formulas depend on standardized categories, or when a spreadsheet is acting as a lightweight form. Consistent entries make filters, charts, summaries, and lookups more dependable. A rule does not, however, establish that an entry is true: a valid-looking date can still be the wrong date, and a permitted category can still be selected by mistake.

How to add a validation rule

In Google Sheets in a web browser, select the cell or range to control, then open Data > Data validation. Use the data-validation panel to add or edit a rule. The exact controls shown depend on the criterion you choose.

Select the target cells. For example, select D2:D200 if column D will contain task statuses and row 1 contains the heading.
Open Data > Data validation and add a rule for the selected range. Check that the range shown in the panel is the intended one.
Choose a criterion, such as a drop-down, a drop-down based on a range, a number condition, or a date condition.
Enter the permitted choices or the required condition. If you use a drop-down, consider whether each option should have a clear label and, where useful, a color.
Set how Sheets should handle invalid entries: reject the input or show a warning. Save the rule, then try a permitted value and an invalid one to confirm the result.

To change a rule later, select a cell covered by it and return to Data > Data validation. Review the applied range before editing: a rule can cover more cells than the one currently selected. You can also remove a rule from the panel if it is no longer needed.

Choose the right kind of rule

The right criterion depends on what the cell is meant to contain. A drop-down is generally best for a small, known set of categories. A range-based drop-down is more convenient when the choices are maintained elsewhere in the workbook. For quantities or scores, a number condition can set a minimum, maximum, or interval. Date criteria can constrain entries to valid dates or to a date relationship, such as a date before or after a chosen point.

Fixed drop-down: Use for stable choices such as Yes, No, and Not applicable.
Drop-down from a range: Use when options live in a list on another sheet or in a dedicated area that may change.
Number or date rule: Use to control numeric or date entries, such as a rating from 1 to 5 or a date within a reporting period.
Checkbox: Use for a simple checked/unchecked field. In Sheets, select the cells and choose Insert > Checkbox; checkboxes are useful for completion flags, not for a list of several categories.
Custom formula: Use when a built-in condition is not enough, and test the formula against both acceptable and unacceptable examples.

Not every range needs a strict rule. If people must enter unusual but legitimate values, a warning may be better than rejection. Conversely, if downstream calculations rely on exact categories, rejecting unrecognized entries can prevent subtle inconsistencies such as “In progress,” “In Progress,” and “In-progress” being treated as different text.

Create and maintain dependable drop-downs

For a fixed list, choose the drop-down criterion and enter the choices in the rule editor. For a list maintained in cells, choose the option to use a drop-down from a range and specify the source range. Keeping choices in one clearly labeled place makes them easier to update than editing many separate rules. If the source list is on another tab, make sure the range reference points to the intended cells.

Think about what happens as the list grows. A source range that covers only the current options may omit new entries added below it. You can select a suitably sized range or update the rule when the list changes. Avoid including headings or blank cells in the source range unless you want them to appear as choices. If an option is renamed or removed, inspect existing entries too: changing a source list does not necessarily rewrite values already entered in the data column.

Drop-downs can make a sheet easier to use, but the labels still need to be understandable. Prefer concise choices that users can distinguish. If two options have similar meanings, explain the difference in a nearby note or instructions rather than expecting users to guess. A drop-down is a selection aid, not a substitute for documenting the workflow.

Use custom formulas for more specific checks

When the built-in criteria do not express the rule you need, a custom-formula criterion can test whether an entry meets a logical condition. For example, suppose cells A2:A100 should contain numeric scores from 0 through 100. Select that range, create a custom-formula rule, and use:

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

The reference A2 is relative: Sheets evaluates the condition for each row in the selected range, so A3 is checked for the next cell, and so on. The formula returns TRUE for a number within the limits and FALSE otherwise. Choose the invalid-data behavior that fits your workflow. Test boundary cases such as 0, 100, -1, 101, and text before applying the rule to a busy sheet.

A common mistake is writing a custom formula as if it were being evaluated only for the top-left cell, then using absolute references that lock every row to the same cell. Another is forgetting that the formula should express whether the value is acceptable. For a more complicated test, build and check the formula in an unused cell first, then apply it as validation. Custom formulas can be powerful, but they are harder for other editors to maintain than a straightforward built-in criterion.

Reject input or show a warning?

In the rule settings, rejecting invalid input is the stricter choice: an editor cannot submit a value that violates the rule through the normal entry flow. A warning is more flexible. Sheets can show that an entry does not match while allowing the editor to proceed. Use rejection for controlled fields where consistency matters, and a warning when exceptions are legitimate or users need to record information before someone reviews it.

Neither choice replaces good sheet design. A rejection message is most useful when the correct options are visible and the rule is easy to understand. If exceptions are common, consider adding an “Other” choice and a separate explanation column, or use a warning with a review process. This keeps exceptional cases visible instead of encouraging users to work around the rule.

Common problems and how to fix them

The drop-down is missing: Confirm that the cell is within the rule’s applied range. Select the cell and check the rule panel for the range and criterion.
A choice is absent: Check the source range for the rule. The new option may have been added outside it, or a blank or heading may be included by mistake.
Values look the same but are treated differently: Check for extra spaces, punctuation, capitalization, or different date and number formats. A standardized drop-down often avoids text variations.
An old entry is still present: A new rule does not necessarily clean up existing data. Review the column for values that no longer meet the current rule and correct them deliberately.
Rules seem to change after a bulk edit: Review both the cell contents and the validation rules after pasting or importing data. Bulk operations can affect cells differently from typing into them, so verify important ranges rather than assuming the rule is intact.
Users cannot make a needed exception: Decide whether to allow warnings, add a documented exception option, or route exceptions to a separate field. Avoid weakening a rule without considering how reports and formulas use the data.

Also distinguish a validation problem from a permission problem. If a user cannot edit a cell at all, check whether the sheet or range is protected and whether that person has editing access. Validation controls acceptable entries; protection controls who can change cells. They solve different problems.

Validation, formatting, and protection are different

Conditional formatting changes how cells look when a condition is met. It can highlight a suspicious value, but it does not by itself prevent someone from entering that value. Data validation sets the entry rule and can reject or warn about an invalid value. Protected sheets or ranges restrict editing access. A reliable workflow may use all three: validation for entry, conditional formatting to make exceptions easy to spot, and protection for formulas or reference lists that should not be altered.

Validation is also not a complete auditing or data-cleaning system. It will not determine whether the person entering a record chose the correct customer, whether a date is supported by a source document, or whether every required row has been filled. For important datasets, combine rules with review, clear ownership, and periodic checks. If data arrives from connected forms, imports, scripts, or other tools, verify that the resulting values and rules behave as intended.

A practical example: a project status column

Imagine a team tracker with task names in column A, owners in column B, due dates in column C, and statuses in column D. Select D2:D200 and create a fixed drop-down containing Not started, In progress, Blocked, and Complete. Rejecting unlisted entries keeps status summaries consistent. If the team changes its vocabulary, update the list and communicate the change; then review older rows for values that need to be brought into line.

For column C, select the date range and apply an appropriate date criterion. A rule can check that entries are dates, but a date rule alone cannot establish that a due date is realistic or that it falls within the project schedule. For column B, a range-based drop-down can use a maintained list of team members. When someone joins or leaves, update that source list and check the tracker for old assignments. This small example shows why good validation begins with deciding what the data means, not just choosing a menu option.

Google Sheets compared with Excel

Google Sheets and Microsoft Excel both offer data-validation features such as lists and numeric or date restrictions, but the interfaces and some details differ. The steps here use the Google Sheets web interface and its Data > Data validation menu. In Excel for Microsoft 365 on Windows, validation is generally managed through Data > Data Validation on the ribbon. Do not assume a Sheets rule, formula, or sharing workflow will transfer identically to an Excel workbook; verify the rules after converting or exporting a file.

Sheets is especially convenient for collaborative editing in a browser, while spreadsheet compatibility can matter when a team relies on desktop Excel features or established workbook processes. Choose based on where the workbook will be maintained and which tools collaborators use. If the sheet will move between programs, keep rules simple where possible and test representative cells in the destination application.

Frequently Asked Questions

Can Google Sheets require someone to choose from a list?
Yes. Apply a drop-down rule to the cells and set invalid data to be rejected. This helps enforce the listed choices during ordinary editing, but still review imported or bulk-edited data when accuracy is important.

How do I make a drop-down list from another sheet?
Put the choices in a source range, then select the cells that need the menu and choose Data > Data validation. Select the drop-down-from-a-range criterion and provide the source range. Check the range again if you later add options.

Can I allow exceptions to a validation rule?
Yes. Set the rule to show a warning rather than reject invalid input, if that option is available for the chosen criterion. You can also include an explicit exception choice and a separate field for the explanation.

Does validation fix data already in the cells?
No. A rule guides or checks future entries; it does not automatically standardize old values. Review existing data separately, especially after changing a list or tightening a rule.

Why can someone edit a cell but not enter the value they want?
The cell may have a validation rule that rejects the value. Check the rule and its allowed choices. If the person cannot edit the cell at all, check sharing permissions and protected ranges instead.

Can I use validation on a whole column?
Yes, a rule can apply to a range that includes many rows. Applying it to a practical range such as D2:D1000 can avoid including a heading, while a whole-column range may be useful when the sheet will keep growing. Make sure the chosen range matches the sheet’s layout.

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.