Google Sheets Custom Data Validation: Rules and Tips

To create custom data validation in Google Sheets, select the cells, choose Data > Data validation, click Add rule, and choose Custom formula is under Criteria. Enter a formula that evaluates to TRUE for an allowed value, then choose whether invalid entries should be rejected or only flagged.

What custom data validation does

Data validation lets you control what people can enter in a cell. A simple rule can limit entries to a number within a range, a date after a specified day, or an item from a dropdown list. Custom formula is the more flexible option: it uses a Google Sheets formula to test each entry against conditions you define.

A custom rule does not change or calculate the cell’s value. Instead, Sheets checks whether an entry meets the rule. For example, a rule can allow whole numbers from 1 to 20, require a code to follow a pattern, or prevent duplicate names within a column. Rules are attached to a cell or range, so the same rule can be applied to many cells.

Validation is useful for shared trackers, intake forms, inventories, and other sheets where inconsistent entries can make sorting, filtering, or reporting harder. It is a guardrail, not a guarantee that every value in a workbook is clean: existing data, pasted values, and later edits may need separate review.

How to add a validation rule

In Google Sheets in a web browser, create a rule through the Data menu. The sidebar lets you set the range, choose a criterion, decide what happens to invalid input, and optionally show guidance to people filling in the sheet.

Select the cell or range to control. You can also open Data > Data validation first and enter the range in the rule panel.
Choose Data > Data validation, then click Add rule if the panel does not already show a new rule.
Check Apply to range and adjust it if needed, such as changing A2:A20 to A2:A500.
Under Criteria, select Custom formula is and enter your formula.
Choose Reject the input to block entries that fail, or Show a warning to let them through with a warning.
Optionally add help text, then click Done to save the rule.

Menu wording and layout can change as Google updates Sheets, but Data > Data validation is the standard route for editing a rule. Clicking a cell with a rule may display a warning or help text depending on the options you set. To change or remove a rule later, select an affected cell and reopen the validation panel.

How custom formulas are evaluated

The formula must return TRUE for a value that should be allowed and FALSE for a value that should fail. When a rule applies to multiple rows, write the formula using the top-left cell of the range as the reference. For example, if the rule applies to B2:B100, use B2 in the formula. Sheets adjusts the row reference as it evaluates B3, B4, and the other cells.

This relative-reference behavior is important. If you accidentally write a formula that always checks B2, every cell may be judged by that one cell instead of its own entry. Conversely, dollar signs can lock a reference when you need a fixed comparison cell. If a limit is stored in F1 and the rule applies to B2:B100, a formula can compare each B cell with $F$1.

Custom validation formulas generally do not need to include an equals sign? In Google Sheets, enter a formula beginning with an equals sign, as you would in a normal cell. The formula should test the value in the cell being validated; it should not return a label such as “Valid.” Use functions and operators supported by Google Sheets, and check that parentheses and cell references match your intended range.

Practical formula examples

These examples assume the rule applies to the range named in each description. Use the first cell in the range as the relative reference in the formula.

Whole-number scores from 0 to 100: with scores in A2:A, use =AND(ISNUMBER(A2),A2>=0,A2<=100,MOD(A2,1)=0). This accepts numeric integers from 0 through 100 and rejects decimals, text, and blanks. If decimals are acceptable, omit the MOD test.
Allow a blank or a number from 0 to 100: with optional values in A2:A, use =OR(A2="",AND(ISNUMBER(A2),A2>=0,A2<=100)). This is useful when a form is incomplete but any entered number still needs to meet the limit.
Prevent duplicate IDs: with IDs in A2:A, use =OR(A2="",COUNTIF($A$2:$A,A2)=1). A nonblank ID passes only when it appears once in the column. The blank check avoids treating empty cells as duplicate IDs.
Require a weekday date: with dates in C2:C, use =AND(ISNUMBER(C2),WEEKDAY(C2,2)<=5). Sheets stores dates as numbers, and WEEKDAY with a second argument of 2 numbers Monday as 1 through Sunday as 7. The ISNUMBER test prevents text from passing as a date.
Check a simple code pattern: with codes in D2:D, use =REGEXMATCH(D2,"^[A-Z]{3}-[0-9]{4}$") to require three uppercase letters, a hyphen, and four digits, such as ABC-1234. This checks the shape of the text, not whether the code exists in a separate records system.

For the whole-number example, MOD(A2,1)=0 checks that the remainder after dividing by 1 is zero. For the duplicate example, the dollar signs keep the search range fixed while A2 changes to the current row. Those details make formulas usable down a range rather than checking only one cell.

When to use a dropdown instead

A custom formula is not always the best rule. If the allowed values are a short, known set—such as Open, In progress, and Complete—a dropdown is easier to understand and less prone to formula mistakes. In Data > Data validation, choose a dropdown criterion and enter the options, or choose a dropdown from a range when the options are maintained in cells.

Dropdowns make permitted choices visible, while a custom formula can validate conditions that are not a fixed list. You can also use rules alongside dropdowns where a sheet has multiple needs, but avoid giving users competing instructions. If list options change often, storing them in a range can be easier to maintain than editing each rule.

Built-in criteria are also a good fit for straightforward limits, such as a date being before or after a chosen date, a number being between two values, or text matching a standard condition. Use Custom formula is when you need combinations, comparisons to other cells, or a condition not covered conveniently by the available criteria.

Choosing reject input or show a warning

Reject the input is appropriate when an invalid value should not be saved in the cell—for example, when an ID must be unique or a percentage must stay within a defined range. It helps keep accidental typing errors out of the data, but it can frustrate users if the rule is too strict or its purpose is unclear. Add useful help text to explain what the cell accepts.

Show a warning is better when unusual values may be legitimate and should be reviewed rather than blocked. For instance, a budget entry outside a normal range might be possible but worth checking. A warning does not enforce the rule; a user can proceed with the value. Make that distinction clear if another person relies on the sheet’s data.

Neither option replaces review of a large imported dataset. Validation is most effective when the rule is in place before entries are collected and the sheet’s owner periodically checks data, especially after pasting, importing, or changing rules.

Common problems and how to fix them

The rule rejects values that look valid: confirm the formula uses the first cell in the applied range. A rule on C2:C that refers to C1 may be checking the wrong row.
Every row appears to use the same value: remove an unintended dollar sign from a row reference. Use C2 for a reference that should move by row; use $F$1 when the comparison cell must stay fixed.
A blank cell fails: add an explicit blank condition, such as the A2="" part of the optional-number example, if blanks should be allowed. Otherwise, decide whether blanks should be blocked and make that policy explicit.
Numbers or dates are treated as text: check the cell contents and formatting. A value that looks like a date may have been entered or imported as text, so test it in a spare cell or normalize the source data before relying on date functions.
Existing entries do not match the new rule: adding validation does not automatically clean a range. Review current values separately, and use filters or formulas to find exceptions before depending on the rule.
The formula has a parse error: verify that the function names and separators are valid in Google Sheets, the quotation marks are straight double quotes, and all parentheses are balanced. Also check that the formula is entered under Custom formula is, not in a different criterion field.

When troubleshooting, test the formula in an unused cell with a representative allowed value and a representative disallowed value. The test can confirm the logic, but then verify the validation rule itself by trying entries in the target range. A formula that works for one sample may still fail when a row reference or applied range is misaligned.

Keeping rules understandable in shared sheets

In a collaborative spreadsheet, validation rules are easier to maintain when the sheet explains what they do. Use clear column headings, concise help text, and a visible list of permitted categories where appropriate. Keep rule ranges aligned with the data area, and revisit them when columns move or the sheet’s structure changes.

Custom validation formulas are rules for entry, not a substitute for protecting important formulas or controlling who can edit a sheet. If users should not alter a calculation or reference list, consider protecting the relevant sheet or range through Google Sheets’ protection options and assigning access deliberately. Validation and editing permissions solve different problems.

Google Sheets also differs from Excel: this article’s menu paths and rule editor refer to Sheets in a browser, not Excel’s Data Validation dialog. Both programs support validation features, but their menus, formula handling, and available options are not identical. Use Sheets-specific instructions when working in a Google spreadsheet.

Frequently Asked Questions

Can Google Sheets custom data validation use a formula?
Yes. Select Custom formula is in the Data validation rule’s Criteria menu and enter a formula that returns TRUE for values you want to allow. Set the formula’s cell references to match the first cell in the applied range.

Does custom data validation automatically fix existing data?
No. A rule controls or flags entries according to its settings, but it does not rewrite old values to make them compliant. Review existing entries separately when adding a rule to a sheet already in use.

Can I allow blank cells but validate anything entered?
Yes. Add a condition that accepts a blank, combined with the test for a valid entry. For example, the optional-number formula above allows blanks or numbers from 0 to 100.

What is the difference between Reject the input and Show a warning?
Reject the input blocks a value that fails the rule. Show a warning alerts the person entering it but permits the value to remain, so it is suitable when exceptions are possible and need human review.

Can I make a dropdown depend on another cell?
A basic dropdown presents a fixed list or values from a range. A custom formula can test whether an entry is valid, but it does not itself create a changing dropdown list. Dependent lists typically require carefully arranged source ranges and additional setup.

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.