Google Sheets Conditional Data Validation Explained

Yes. In Google Sheets, conditional data validation can use a custom formula to allow or reject an entry based on other cells; for choices that change with another cell, you can use a helper range to feed a dependent dropdown. Set rules with Data > Data validation, and choose whether invalid entries are rejected or merely flagged.

What conditional data validation means

Data validation controls what people can enter in a cell. A basic rule might allow only dates, numbers within a range, or choices from a dropdown. A conditional rule adds a relationship: the acceptable entry depends on another value, such as a department, status, or category selected elsewhere in the row.

For example, a request form might have a category in column A and a related item in column B. If A2 says “Fruit,” B2 should accept “Apple” or “Pear,” but not “Hammer.” Or a delivery date might be allowed only when a status cell says “Scheduled.” These rules reduce inconsistent entries and make shared sheets easier to use.

Sheets does not have a single control labeled “conditional data validation.” Instead, you create a rule using the available criteria, including Custom formula is, or create a dropdown whose source is a range that changes according to your setup. The right method depends on whether you need to enforce a condition, offer a changing menu, or do both.

How rules are evaluated

A custom-formula rule evaluates a formula for each cell in the selected range. The formula must return TRUE for an entry to pass. Relative references shift as the rule is applied down a range, while dollar signs hold a row or column fixed. This is why a formula written for the first row of a range can often work for every row below it.

When a rule is violated, Sheets can reject the entry or show a warning. Rejection is appropriate when incorrect values would break a workflow; a warning is useful when exceptions are legitimate but should be visible. A validation rule is not the same as cell protection: it checks entries, but it does not prevent an editor from changing other parts of the sheet or modifying the rule itself.

Validation also differs from conditional formatting. Validation decides whether an entry is acceptable or offers a menu. Conditional formatting changes the appearance of a cell after a condition is met. A red fill can draw attention to an invalid or incomplete value, but formatting alone does not prevent someone from entering it.

Set up a rule based on another cell

For a straightforward example, suppose A2:A100 contains a status and B2:B100 contains a completion date. You want a date in column B only when the status in the same row is “Complete.” To apply a custom rule:

Select B2:B100.
Choose Data > Data validation, then select Add rule if the rule editor opens without a rule.
Confirm that the range is B2:B100. Under Criteria, choose Custom formula is.
Enter a formula that returns TRUE for allowed values. In the invalid-data setting, choose Reject input for strict enforcement, or Show a warning when exceptions should remain possible.
Select Done. Try a permitted and a non-permitted entry to confirm the behavior.

With status in A2:A100 and the corresponding completion date in B2:B100, enter this rule for the range beginning at B2: =OR(B2="",$A2="Complete"). The blank check lets the date cell remain empty; otherwise, an entry passes only when the status in column A on that row is Complete. The formula uses $A2 so the rule stays in column A while the row number adjusts for B3, B4, and so on.

If the date itself must be a real date, add a date criterion where available or expand the custom test to check the entry. In general, avoid making one formula do too many jobs if separate criteria are clearer to maintain. A rule that a colleague can read and verify is less likely to become a hidden source of data errors.

Use a custom formula to check permitted combinations

A custom formula is especially useful when a value must match a row in a small reference table. Imagine a mapping table with categories in D2:D20 and allowed items in E2:E20. The entry is in B2, and its category is in A2. Select B2:B100 and use a formula that looks for the category-and-item pair in the mapping table.

With categories in A2:A100, entries in B2:B100, and the allowed category/item pairs in D2:E20, use this custom formula for the range starting at B2: =OR(B2="",COUNTIFS($D$2:$D$20,$A2,$E$2:$E$20,B2)>0). COUNTIFS returns the number of matching pairs; the rule passes if that count is greater than zero, or if B2 is blank. If A2 is Fruit and B2 is Apple, and that pair appears in the table, the result is TRUE. If B2 is Hammer and no Fruit/Hammer pair exists, the result is FALSE.

Keep the reference ranges the same size, as they are here, and ensure the spelling in the mapping table matches the choices users enter. For larger or frequently edited lists, convert the reference area into a clearly labeled, maintained lookup table rather than embedding allowed values in a long formula. The table then becomes the place to add or remove valid combinations.

Build a dependent dropdown with a helper range

A custom formula can reject a wrong choice, but it does not automatically change the visible choices in a dropdown. To show only relevant options, create a helper range that filters the mapping table, then use that range as the dropdown source. This is useful when users should not have to remember which combinations are valid.

For a single dependent dropdown, keep the category in A2 and the dropdown cell in B2. Put the category/item mapping in D2:E20 and leave G2:G20 clear for the filtered results. In G2, enter this formula: =IFERROR(FILTER($E$2:$E$20,$D$2:$D$20=$A2),""). If A2 contains Fruit and the table maps Fruit to Apple and Pear, G2:G3 will display Apple and Pear. If no rows match, IFERROR returns a blank instead of showing a filter error.

Then select B2, choose Data > Data validation, choose Dropdown (from a range), and set the source to G2:G20. The dropdown now draws its options from the helper cells. Keep the helper range clear of other content because the FILTER result needs room to expand.

For a form with many independently changing rows, each row needs an appropriately row-specific helper output, or a carefully designed helper layout. A single helper list based on A2 cannot supply different results for every category in A3, A4, and later rows at the same time. If a sheet only needs to enforce valid combinations rather than display a changing menu, a custom-formula rule against the mapping table is usually simpler.

Validation versus conditional formatting

Use data validation when the goal is to control input: limit a number, require a date, offer a dropdown, or reject a combination that does not meet a rule. Use conditional formatting when the goal is to communicate visually, such as highlighting overdue tasks or coloring a row when a status is “Blocked.” The conditional-formatting editor is under Format > Conditional formatting.

The two features can complement each other. For example, a validation rule can restrict a status to a set of approved values, while conditional formatting colors “Needs review” amber. But applying a format rule does not create a validation rule, and a validation warning does not automatically give a cell a particular color. Set up each feature separately if you need both.

Excel has its own Data Validation tools and supports formula-based rules, but its menus and some list-source behavior differ from Sheets. Instructions that use Excel’s ribbon, named ranges, or spill-reference syntax should not be assumed to work unchanged in Google Sheets. In Sheets, follow the browser menus and use a helper range when you need a transparent, easy-to-check dropdown source.

Common problems and ways to prevent them

The formula rejects every entry: Check the selected range’s first cell against the row reference in the formula. A rule for B2:B100 should generally be written as if it is evaluating B2, not B1.
The wrong row is being checked: Use a relative row reference such as $A2 when each entry depends on its own row. Using $A$2 would make every row depend on A2.
The dropdown is empty: Verify that the source range contains results and that the category text exactly matches the mapping table. Also check that the helper formula has space to expand.
A list contains duplicates or blanks: Clean the source table or use a separate helper list designed for dropdown choices. A dropdown uses the values in its source range, so unwanted cells there can become unwanted options.
Invalid data appears despite a rule: Check whether the rule covers the cells in question and whether someone changed or removed it. Pasting data, importing a range, or editing the rule can affect the expected result, so periodically inspect important sheets and test representative entries.

For a sheet used by multiple people, label the reference table, keep helper formulas in a clearly identified area or separate sheet, and protect setup cells if editors should not change them. Protection and validation solve different problems: protection restricts editing access, while validation checks values entered into editable cells.

Frequently Asked Questions

Can Google Sheets data validation depend on another cell?
Yes. Choose Custom formula is and write a formula that refers to the controlling cell. Use relative row references when applying the rule to multiple rows, and choose Reject input if the condition must be enforced.

Can I make a dropdown change based on another dropdown?
Yes. A common approach is to use a helper range with FILTER, then set the second cell’s Dropdown (from a range) source to that helper output. For multiple rows, the helper setup must account for each row’s selected category.

Does conditional formatting validate data?
No. Conditional formatting changes how cells look; it does not block an invalid entry. Create a separate data validation rule when users should be prevented from entering a value.

Can I allow a cell to stay blank?
Usually. For a custom formula, include a blank check such as B2="" in an OR test. Whether blanks should be allowed is a rule-design choice; omit that exception when every row must contain a value.

Can I apply one rule to an entire column?
Yes, but select a practical range such as B2:B rather than including a header row. Write the custom formula for the first data row in that range, and check that its relative and absolute references behave correctly as the rule continues downward.

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.