Excel Data Validation Formula: Rules, Examples & Fixes

An Excel data validation formula is a custom rule that controls what users can enter in selected cells. In Microsoft 365 for Windows, select the cells, choose Data > Data Validation, set Allow to Custom, and enter a formula that evaluates to TRUE for permitted entries; for example, with A2 as the first selected cell, =AND(A2>=1,A2<=100) allows numbers from 1 through 100.

What a data validation formula does

Data validation helps keep spreadsheet entries within expected limits. Excel offers built-in rules for whole numbers, decimals, dates, times, text length, and lists. Choose Custom when those options do not express the rule you need, or when acceptance depends on a formula involving one or more cells.

The custom formula is a logical test. Excel accepts an entry when the test returns TRUE and rejects it when the test returns FALSE or an error, provided the validation rule is active and its error alert is set to Stop. The formula does not need to display a message or calculate a value for the user; its job is to decide whether the proposed entry is acceptable.

For example, a rule can require a value to fall within a range, match a naming pattern, or be absent from a list of previous entries. Validation is an input safeguard, not a guarantee that every value in a workbook is correct: copying or filling cells can bypass it, and it does not automatically repair entries that were already present.

How to add a custom rule in Excel

Select the cell or range to validate. For a multirow range, note the top-left cell because the formula is normally written relative to that cell.
On the ribbon, choose Data > Data Validation. In the dialog’s Settings tab, choose Custom from the Allow list.
Enter the formula in the Formula box. Start with an equals sign and make sure the references correspond to the first selected cell.
Use the Input Message tab if you want to explain the expected entry, and the Error Alert tab to set the message shown when an entry fails.
Confirm that the error alert is enabled and uses the Stop style if you want Excel to block invalid typed entries, then select OK.

In Excel for Mac, the Data Validation command is also available from the Data tab; the dialog’s layout can differ slightly, but the Allow and Custom settings work on the same principle. Excel for the web has data-validation controls too, though available options and dialog details may vary. If a workbook will be edited in different Excel versions, check the rule in the versions your users rely on.

How cell references work in validation formulas

Excel evaluates a custom rule in relation to the cells selected when you create it. If you select A2:A20 and enter a formula referring to A2, Excel adjusts that relative reference for each row: the rule for A3 will refer to A3. This makes a single formula useful across a range.

Dollar signs lock a reference. A reference such as $B$1 stays fixed as the rule is applied to other cells, while A2 changes by row. This is useful when every entry must be compared with a limit stored in one settings cell. If the formula is written for the wrong top-left cell, the rule can appear to work in one row but check another row’s value elsewhere in the range.

Before applying a formula to a large range, test it on one cell. Enter a value that should pass and one that should fail. If the rule uses another cell, test with that cell both inside and outside its expected limits. This catches reference shifts and logic mistakes before they affect a whole column.

Useful custom formula examples

Each example below states the assumed layout. In the formulas, the first reference is the top-left cell of the range being validated. Select the relevant range and use Allow: Custom.

Whole-number range: With the entry in A2 and the rule applied to A2:A100, use =AND(A2>=1,A2<=100,MOD(A2,1)=0). It accepts integers from 1 to 100 inclusive. For example, 42 passes; 100.5 and 101 fail. The MOD test excludes decimal values.
No future dates: With dates entered in B2:B100, use =AND(ISNUMBER(B2),B2<=TODAY()). It accepts a real Excel date serial number no later than today and rejects text that only looks like a date. Because TODAY updates, an entry that was valid can become invalid as time passes only if the rule is rechecked during later editing; validation is not a continuous audit of stored values.
Require a value unless blank is allowed: With entries in C2:C100, use =OR(C2="",AND(ISTEXT(C2),LEN(TRIM(C2))>0)). This permits an empty cell or nonempty text, while rejecting numbers and text made only of spaces. Remove the first OR condition if blanks should be rejected.
Unique entries: With identifiers in D2:D100, use =OR(D2="",COUNTIF($D$2:$D$100,D2)=1). A nonblank identifier passes only if it occurs once in the range, including its current cell. If two rows already contain the same identifier, both entries fail the test when edited; validation does not automatically remove the duplicate.
Match a value in a permitted range: With entries in E2:E100 and approved codes in H2:H20, use =COUNTIF($H$2:$H$20,E2)>0. It accepts an entry found in the approved-code range. For example, if H2:H20 contains codes A10 and B25, entering B25 passes and entering C30 fails.

For the unique-entry example, COUNTIF uses Excel’s normal matching behavior, which is not case-sensitive. Thus ABC and abc are treated as the same text for this test. If case distinctions matter, a different rule is needed. Also, formulas that use a range including the entry cell are intentional here: a unique value is counted once because it appears in its own cell.

Custom formulas versus built-in validation

Use a built-in validation type when it expresses the requirement clearly. For instance, Allow: Whole number with a between condition is easier to inspect than a custom formula for a simple integer limit. A built-in List rule is usually the straightforward way to create a drop-down menu. Custom rules are most useful for conditions involving multiple tests, other cells, or patterns that the built-in choices cannot express.

A list drop-down and a custom formula solve related but distinct problems. A List rule presents choices to the user; a custom formula checks whether an entry satisfies a condition. You can use a custom rule to check membership in a separate range, as in the approved-code example, but that formula does not itself create the drop-down arrow. If users should choose from visible options, configure a List rule instead.

Validation formulas also differ from conditional formatting. Validation controls whether an entry is accepted during data entry; conditional formatting changes how cells look according to a rule. Conditional formatting can flag suspicious values already in a sheet, while validation can guide future input. Neither should be assumed to replace the other when both prevention and review are important.

Common problems and practical checks

Wrong reference row: When the selected range begins at A2, write the formula for A2. Starting the formula at A1 shifts the test and may validate each row against the wrong cell.
Unexpected blanks: Decide explicitly whether blank entries are allowed. Add a blank condition such as A2="" when appropriate, and review the Ignore blank setting in the dialog. Do not rely on a complicated test to communicate a blank policy.
Input is not blocked: Open Data > Data Validation, check that the rule remains in place, and inspect the Error Alert tab. A warning or information alert informs users but does not block the entry; Stop is the blocking alert style for ordinary entry.
Existing invalid values remain: Adding a rule does not automatically clean up the range. Use the Data Validation dialog’s Circle Invalid Data command, where available, or check the values with a separate formula or filter.
Pasting bypasses expectations: Pasting data can introduce values without the same interactive check a user gets when typing. Review imported or pasted data after the operation, and consider a separate audit formula or controlled import process for important sheets.
Formula returns an error: A custom formula that evaluates to an error will not behave like a passing TRUE test. Check for invalid references, unexpected text, and operations such as division by zero. Add functions such as ISNUMBER when the input must be numeric.

For reliable workbooks, explain the rule in an input message, use a clear error message, and keep validation formulas as simple as the requirement allows. A complex rule may be technically correct but hard for the next person to maintain. Store changing thresholds in labeled cells rather than burying unexplained constants in several formulas.

Limits and maintenance

Data validation is designed to guide normal entry, not to serve as a security boundary or an immutable data-cleaning system. Users may paste, import, or otherwise alter data in ways that require later checking. Shared workbooks can also be edited by different applications, so test important rules in the environments used by the team.

Rules can be copied along with cells, and editing a formula later may affect only the selected cells. When a workbook changes shape, verify that the validated range still covers new rows. For a growing table, consider how new rows are added and confirm that the validation rule is carried into them rather than assuming it will always extend as intended.

For high-impact records, use a second check: a helper column, a summary of invalid entries, or a review step after imports. For example, the same condition used to accept an entry can be adapted into a separate audit formula that visibly reports whether existing data passes. The validation rule then supports entry, while the audit helps find problems that arrived through another route.

Frequently Asked Questions

What does a custom data validation formula need to return?

It needs to evaluate to TRUE for an allowed entry and FALSE for a disallowed entry. Start the formula with an equals sign in the Custom rule’s Formula box. A result that is an error should not be treated as a successful validation.

Can a data validation formula refer to another cell?

Yes. Use a relative reference when the relevant cell should change from row to row, or an absolute reference such as $B$1 when the comparison cell should stay fixed. Check the reference against the top-left cell of the selected range.

Why does Excel accept a value that the rule should reject?

Check that the rule applies to the intended cells, that the formula uses the correct starting reference, and that the Error Alert is set to Stop. Pasted or imported values may also require a separate audit because validation is not a complete barrier to every way data enters a workbook.

Does data validation remove duplicates already in a range?

No. A uniqueness rule can reject a duplicate during ordinary entry, but it does not delete or repair duplicates already present. Check existing values separately before relying on the rule.

Should I use a custom formula or a drop-down list?

Use a List validation rule when users should pick from a menu of allowed choices. Use a custom formula when acceptance depends on a calculation or condition. If users need both a menu and extra restrictions, test how the requirements can be combined in the workbook rather than assuming one rule supplies both behaviors.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.