Excel Data Validation Custom Formula Examples That Work

Excel custom data validation lets you accept an entry only when a formula returns TRUE. In Microsoft 365 for Windows, select the cells, choose Data > Data Tools > Data Validation, set Allow to Custom, and enter a formula such as =AND(A2>=1,A2<=100) to permit numbers from 1 through 100 in a range beginning at A2.

How custom formula validation works

A custom rule evaluates a formula for each cell in the selected range. A TRUE result permits the entry; a FALSE result rejects it when the error alert is configured to stop the entry. The formula is not a worksheet formula that displays a result in the cell. It is a condition Excel checks when someone enters or edits a value.

The cell reference should usually point to the upper-left cell of the selected range. Excel adjusts relative references as it checks the other cells. For example, if A2:A20 is selected and the formula refers to A2, the rule evaluates A3 for the next row, then A4, and so on. A reference with dollar signs, such as $A$2, remains fixed; use that when the rule should compare every entry with one specific cell.

Custom validation can use ordinary worksheet functions and operators, including AND, OR, COUNTIF, LEN, and WEEKDAY. The result must resolve to TRUE or FALSE. If a formula produces an error, Excel generally treats the entry as invalid, so test formulas with both acceptable and unacceptable sample values before relying on them.

Set up a custom validation rule

Select the cell or range to control. For a column of entries, include the first data cell, such as A2:A100, rather than the header.
On Windows, open Data > Data Tools > Data Validation. In the dialog, use the Settings tab and choose Custom in the Allow list.
Enter the formula in the Formula box. Use a reference to the first selected cell and do not put an equals sign in front of a cell reference inside the formula.
Optionally set an Input Message to explain what users should enter. On the Error Alert tab, keep Show error alert enabled and choose Stop if invalid entries must be blocked.
Choose OK, then test a valid value and at least one invalid value.

Excel for Mac has a Data Validation command on the Data tab; dialog labels and layout can vary slightly by release. Excel for the web supports creating and editing many validation rules, but some dialog options and workflows differ from the Windows desktop app. If a workbook will be used across platforms, verify the rule in the version your collaborators use.

Custom formula examples for common rules

Each example below assumes the rule is applied to the stated range and uses a relative reference for its first cell. Replace the sample columns or limits to fit your sheet.

Restrict values to a numeric range. With numbers in A2:A100 and the rule applied to that same range, use =AND(A2>=1,A2<=100). The value 42 returns TRUE and is accepted; 101 returns FALSE and is rejected. This checks the bounds, but if entries must be whole numbers, use the built-in Whole number option or add an integer test.
Prevent duplicate IDs while allowing blanks. With IDs in A2:A100 and the rule applied to A2:A100, use =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). A blank is allowed; an ID occurring once returns TRUE. If North already appears in A2 and a user enters North in A4, the count is 2, so the rule returns FALSE. The fixed range keeps the lookup area in place as the validation reference moves down.
Allow dates from today through 30 days ahead. With dates in B2:B100 and the rule applied to B2:B100, use =AND(B2>=TODAY(),B2<=TODAY()+30). A date 10 days from today passes, while a date 31 days from today fails. Because TODAY changes, the permitted window moves over time. If blank dates should be allowed, wrap the condition in OR with a blank check.
Require an ID with a prefix and fixed length. With text in C2:C100 and the rule applied to C2:C100, use =AND(LEFT(C2,4)="INV-",LEN(C2)=12). The text INV-2026-004 has 12 characters and passes; ABC-2026-004 fails because it lacks the required prefix. This checks the pattern’s beginning and total length, not whether the remaining characters are digits.
Require an end date that is not before its start date. With start dates in D2:D100, end dates in E2:E100, and the rule applied to E2:E100, use =OR(AND(D2="",E2=""),AND(ISNUMBER(D2),ISNUMBER(E2),E2>=D2)). Two blank cells are allowed, and an end date equal to or later than the start date passes. If D2 contains 12 August 2026 and E2 contains 11 August 2026, the rule returns FALSE. Requiring ISNUMBER helps avoid accepting text that merely looks like a date.

Choose relative and absolute references deliberately

Reference behavior is one of the most common sources of unexpected validation results. A relative reference changes with each checked cell, which is usually what a row-by-row rule needs. An absolute reference marked with dollar signs stays fixed. Mixed references fix only the column or row: for example, $A2 keeps column A fixed while allowing the row number to change.

For a rule applied to a multi-column range, decide whether the logic should follow each cell or remain tied to a particular column. If a rule is intended to govern column C based on the value in column B on the same row, apply it to C2:C100 and refer to B2 and C2. If the selection begins at C2 but the formula uses B1, Excel will evaluate the wrong row. A quick test in the first and second rows often reveals this kind of alignment mistake.

Custom formulas versus built-in validation choices

Use a built-in Allow option when it expresses the rule clearly. Whole number, Decimal, List, Date, Time, and Text length options are easier for another workbook editor to understand and can expose useful minimum, maximum, or source settings directly in the dialog. A custom formula is valuable when the condition combines fields, checks uniqueness, tests a text pattern, or allows several alternatives.

For a simple dropdown, List validation is usually clearer than a custom formula. For a condition such as “the end date must be on or after the start date,” Custom is the natural choice because the rule compares two cells. You can also use formulas to create a validation rule that responds to other worksheet values, but keep the logic as simple as possible so the next person can maintain it.

Common problems and how to diagnose them

The first cell reference is off by one. Base the formula on the top-left cell of the selected range. If your range starts at row 2, references to row 1 will usually check the header instead of the entry.
The rule appears to accept invalid data. Check the Error Alert tab. If the alert is disabled, or its style is Warning or Information rather than Stop, users may be able to proceed after a warning.
Blank cells behave unexpectedly. A condition that checks a number or text requirement may evaluate blank cells in an unintended way. Add an explicit blank condition with OR when blanks are allowed, or use a separate rule when they are not.
Existing values do not become invalid immediately. Validation is primarily checked when users enter or edit data. If a referenced cell changes later, Excel may not automatically flag every previously entered value that now violates the rule. Review affected entries with a helper column, filter, or conditional formatting.
Copying or pasting introduces invalid values. Data validation is not a complete security barrier. Pasting data, importing records, or using automation can bypass the normal entry prompt or replace a cell’s rule. Recheck important data after bulk updates.
A date rule rejects a date that looks correct. Excel stores dates as serial numbers, but text that resembles a date may not be a real date value. Check the cell format and underlying value, and use ISNUMBER where a rule must confirm a genuine numeric date.

Messages, maintenance, and data quality

An input message can prevent errors before they happen: describe the accepted range, required format, or relationship between fields. The error alert should explain what failed and how to correct it, rather than merely saying the entry is invalid. For example, an alert for an ID rule could say that the value must begin with INV- and contain 12 characters.

When a rule depends on a list or business condition that changes frequently, document where its inputs live and who maintains them. Test the rule after inserting rows, extending a table, changing a source range, or copying cells. For high-stakes records, combine validation with review procedures; a worksheet rule alone cannot guarantee that pasted or imported data is clean.

Frequently Asked Questions

Can a custom data validation formula refer to another cell?
Yes. For example, an end-date rule can compare the end date with the start date in the same row. Use relative references for row-by-row comparisons and absolute references when every entry should be checked against one fixed cell.

Does a custom validation formula have to start with an equals sign?
Yes. Enter it as a worksheet formula beginning with an equals sign in the Custom validation Formula box. The formula should return TRUE for an allowed entry and FALSE for an entry to reject.

Why is Excel allowing a value that fails my formula?
Check whether the error alert is enabled and set to Stop. Also confirm that the formula points to the first cell in the selected range, and remember that paste or import operations may not behave like typing into a validated cell.

Can I allow blanks but reject other invalid entries?
Yes. Include a blank test as one acceptable branch of an OR condition, then put the normal rule in the other branch. This makes the intended blank behavior explicit rather than relying on how a particular condition treats an empty cell.

Can Excel recheck validation when another cell changes?
Not reliably as an automatic audit of all existing entries. A dependent value can change after a valid entry was made, leaving the old entry in place. Use a helper formula, conditional formatting, or a review process to find violations after source values change.

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.