Google Sheets Data Validation: How to Set It Up Easily

Yes. In Google Sheets in a web browser, select the cells, choose Data > Data validation, then click Add rule to set limits such as a dropdown, date, or number requirement. You can choose whether invalid entries are rejected or merely flagged.

What data validation does

Data validation defines what values are acceptable in selected cells. It can guide people toward consistent entries, catch mistakes as they enter information, and make a shared sheet easier to fill out. For example, a status column can offer a fixed set of choices rather than letting each person type a different version of the same status.

Validation is a rule attached to a cell or range, not a change to the underlying data itself. A rule might require a number above zero, a valid date, text in a specified format, or a selection from a list. When someone enters a value that does not meet the rule, Sheets can reject it or display a warning, depending on the rule’s settings.

Rules are useful for routine data entry, but they do not guarantee that a spreadsheet is correct. A valid-looking entry can still be factually wrong: a date can be valid but refer to the wrong day, for instance. Validation works best alongside clear column labels, instructions, and occasional review.

How to apply a validation rule

Start by selecting the cells that should follow the rule. You can select a single cell, a range such as B2:B100, or a whole column if you want the rule to apply to future entries there. Then use this path in the Google Sheets menu:

Select the target cell or range.
Choose Data > Data validation.
In the panel that opens, click Add rule.
Check the range shown and choose a condition under Criteria.
Set the condition’s options, such as the allowed values or date range.
Choose whether to reject invalid input or show a warning. Add help text if it would clarify what users should enter.
Click Done to save the rule.

For example, to restrict a quantity column to positive numbers, select the quantity cells, add a rule, and choose a number condition such as greater than zero. For a deadline column, choose a date condition. The available options depend on the criterion you select, so check the settings displayed in the panel before saving.

Choose the right criteria

The best criterion depends on the kind of information the column is meant to hold. A dropdown is appropriate when the choices are known in advance. A date or number condition is better when the value can vary but must fall within a meaningful type or range. Text conditions can help enforce simple requirements, and a custom formula can express logic that the standard options do not cover.

Dropdown: Choose among values you enter directly, such as Open, In progress, and Complete.
Dropdown from a range: Use values already listed in cells, which is useful when a list needs updating in one place.
Date or number: Require a valid date, a date before or after a limit, or a number that meets a comparison.
Text: Apply a condition such as valid email or text length, when that option fits the information you collect.
Custom formula: Test a condition using a formula that returns TRUE for permitted entries.

Avoid making a rule stricter than the real process requires. For instance, if a task can legitimately be unassigned, a required dropdown may frustrate users. Decide whether blanks should be acceptable and communicate any exceptions in the column heading or help text.

Create a dropdown for consistent entries

A practical example is a project tracker with a Status column. Select the cells where statuses will be entered, open Data > Data validation, and add a Dropdown rule. Enter the approved choices, such as Not started, In progress, and Done, then save. Users can choose from the list instead of inventing variations such as Finished, Complete, or Done.

If the choices are maintained elsewhere in the spreadsheet, use Dropdown (from a range) and specify the cells containing the list. For example, a separate tab named Lists could hold approved department names. This makes list maintenance easier: updating the source values can be simpler than editing rules in many places. Keep the source range tidy, with one choice per cell and no accidental blank rows in the middle.

Google Sheets also offers an Insert > Dropdown route for creating a dropdown. The Data > Data validation panel is particularly useful when you need to inspect or change an existing rule, set its invalid-entry behavior, or use a range-based list. Dropdown display choices can affect how the options appear in the sheet, but they do not change the basic purpose of the rule: limiting or guiding cell entries.

Use custom formulas for more specific checks

Custom formula criteria are useful when a standard rule cannot describe the requirement. For example, suppose A2:A100 is a list of ID values, and each nonblank ID must be unique. Select A2:A100, add a custom formula rule, and use this formula:

=OR(A2="",COUNTIF($A$2:$A$100,A2)=1)

The formula allows a blank cell or a value that occurs only once in the specified range. The reference A2 is relative, so Sheets evaluates the formula in relation to each cell in the selected range; the dollar signs keep the COUNTIF search range fixed. If your range starts on a different row or uses another column, adjust those references to match. Test a rule on a small sample first, especially when it depends on relative references or several conditions.

Edit, remove, and troubleshoot rules

To review rules, select a cell in the affected area and choose Data > Data validation. The panel shows the rule or rules that apply to that selection. Change the condition or range and save, or use the option to remove the rule if it is no longer needed. If you are working with several separate ranges, select the relevant cells rather than assuming a rule applies to the entire column.

When a rule does not behave as expected, check these common causes:

The range is wrong: A rule meant for B2:B100 may have been applied only to B2, or may include a header cell.
The condition is too narrow: A date cutoff or dropdown list may exclude a legitimate option.
The range source is incomplete: A dropdown from a range cannot offer values that fall outside its specified source cells.
The warning is mistaken for a block: Show a warning allows an invalid value with a notice; Reject input is the setting intended to prevent ordinary invalid entry.
The rule is being checked in the wrong place: Select a cell that should have the rule and reopen the validation panel to inspect its settings.

For a dropdown that appears blank or incomplete, inspect both the selected cell range and the source list. For a date rule, confirm that the entry is recognized as a date rather than text. A value that looks like a date can be treated as text if its format or input is inconsistent.

Limits, collaboration, and data protection

Validation improves consistency, but it is not a security feature or a substitute for access controls. People with editing access may be able to change spreadsheet rules, and data brought in through copying, imports, or automation should be checked rather than assumed to have passed every intended check. For important records, consider reviewing the resulting data and limiting who can edit sensitive parts of the sheet.

Google Sheets has a separate feature for checkboxes: select cells and choose Insert > Checkbox. A checkbox is a convenient way to record a yes-or-no choice, but it is not the same as protecting a range. Likewise, protecting a sheet or range controls who can edit cells; it does not validate whether a value is correct. Choose validation to guide allowed entries, and protection to manage editing permissions.

For a collaborative form-like table, combine tools thoughtfully. A dropdown can standardize a category, help text can explain what the category means, and protected cells can preserve formulas or headings. Each solves a different problem, and none removes the need to review data that matters.

How Google Sheets compares with Excel

Excel also has a Data Validation feature, so the general idea—restricting or guiding cell entries—is shared between the two spreadsheet programs. The interface and some available options differ, however. This article’s instructions use Google Sheets in a web browser; follow its Data > Data validation menu path rather than assuming that an Excel ribbon instruction will match.

Google Sheets is designed for browser-based collaboration, and its dropdown rules can be convenient in shared sheets. If a spreadsheet is opened or edited in another program, do not assume that every visual treatment or behavior will be identical. When exchanging files, check the validation rules in the program people will use, especially if consistent entry is important.

Frequently Asked Questions

Can I apply data validation to an entire column?
Select the column or the intended entry range before adding the rule. Applying it to a range below the header, such as B2:B, can avoid putting a dropdown or input restriction in the heading cell.

What is the difference between Reject input and Show a warning?
Reject input blocks an invalid entry through normal editing, while Show a warning lets the entry remain and displays a notice. Use a warning when exceptions are acceptable and a rejection when entries should follow the rule.

Can a Google Sheets dropdown use values from another sheet?
Yes. Choose Dropdown (from a range) and select or enter the cells containing the choices, including a range on another tab. Confirm that the specified range covers all the options you want users to see.

How do I remove data validation from cells?
Select the cells, choose Data > Data validation, select the relevant rule in the panel, and remove it. If only part of a larger range should lose the rule, select that part and check the affected range carefully.

Does data validation stop every incorrect value?
No. It helps control ordinary entry but cannot determine whether a valid value is truthful or appropriate, and it should not be treated as a security boundary. Review important data and manage edit access separately.

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.