Excel Data Validation Rules: How They Work and When to Use Them

Excel data validation rules control what users can enter in selected cells, using limits such as a number range, a date range, a list of approved choices, or a custom formula. In Microsoft Excel for Microsoft 365 on Windows, select the cells and choose Data > Data Validation to create or edit a rule.

What Data Validation Rules Do

A validation rule checks an entry against conditions set for a cell or range. For example, a quantity cell can accept whole numbers from 1 through 500, while a status cell can offer a dropdown containing “Open,” “In progress,” and “Complete.” When an entry does not meet the rule, Excel can block it or display a warning.

Validation is useful wherever a workbook depends on consistent input: order forms, staff logs, survey sheets, inventory lists, and planning templates. Limiting entries can reduce spelling variations, accidental dates, out-of-range values, and other errors that make sorting, filtering, or calculations less reliable. It guides data entry, but it does not verify that information is true in the real world.

Rules apply to worksheet cells, not to the meaning of an entire workbook. A valid date may still be the wrong date for a particular order, for example. Validation works best as one part of a broader process that can include clear instructions, sensible column headings, and checks on the completed data.

Validation Criteria Available in Excel

Excel’s Data Validation dialog offers several types of criteria. The available settings depend on the type selected:

Whole number: Allows integers within a specified limit, such as 1 to 500 items.
Decimal: Allows numeric values, including fractions, within limits. This suits measurements or rates where decimals are meaningful.
List: Restricts entries to a set of choices and can display an in-cell dropdown.
Date: Checks that an entry is a date within, before, after, or between specified dates.
Time: Applies similar comparisons to time values.
Text length: Sets a minimum, maximum, or exact number of characters, useful for fields such as short codes.
Custom: Uses a formula that evaluates to TRUE or FALSE for the cell being checked.

For number, date, and time criteria, the comparison choices include options such as between, equal to, greater than, and less than. A criterion can use fixed values or cell references, which makes it possible to maintain limits elsewhere in a worksheet. Remember that Excel stores dates and times as numeric values; date validation works on Excel-recognized dates, not on any text that merely looks like one.

How to Create or Change a Rule

On Windows, select the target cell or range, open the Data tab, and choose Data > Data Validation. In the Settings tab, choose a validation type from Allow, set its condition, and enter the required values or references. For a list, select List and provide the source choices. Select OK to apply the rule.

The dialog’s other tabs let you add guidance and decide how Excel responds to invalid input. An input message appears when a user selects a cell, so it can explain the expected format before anyone types. An error alert appears after an entry fails the rule. These messages are optional, but helpful wording can make a form much easier to use.

To change or remove a rule, select a cell where it applies and return to Data > Data Validation. Change the settings, or use Clear All in the dialog to remove validation from the selected cells. If a range has inconsistent rules, inspect cells individually or select the intended range and apply one consistent rule. On Excel for Mac, use the Data tab’s Data Validation command; dialog layout and some controls can differ from Windows. Excel for the web also provides data validation, though available controls may vary, so check the interface in the version being used.

Building Useful Dropdown Lists

Dropdown lists are among the most recognizable validation rules. In the Settings tab, choose List and specify the source. For a short, stable set of options, choices can be entered directly in the Source box. For a longer or frequently updated list, store choices in worksheet cells and use that range as the source. A named range is often useful when choices are on another worksheet.

Keep each option distinct and use consistent wording. A list containing “In progress” and “In-Progress,” for instance, allows two spellings of what may be the same status. Avoid blank rows inside a source list if they could create empty choices, and update the source range when adding new options. The In-cell dropdown setting controls whether the arrow appears in the validated cell; it does not change which entries are allowed by the rule.

List validation is not the same as a searchable database or an automatic completion system. Users still need to choose an available option, and the dropdown may be inconvenient when there are many entries. For a long list, organize the source clearly and consider whether another input design would be more practical. Directly typed list separators can also depend on regional settings, so a cell range is often more maintainable.

Using Custom Formulas

Custom validation lets a workbook check conditions that do not fit a built-in criterion. The formula should evaluate to TRUE for an acceptable entry and FALSE for a rejected one. When a rule is applied to a range, write relative references with the first, or top-left, cell in mind; Excel adjusts those references for other cells in the range.

For example, suppose cells B2:B50 should contain whole-number scores from 1 to 100. Select B2:B50, choose Custom in the Allow field, and use this formula:

With the score range B2:B50 selected and B2 as the active top-left cell, the validation formula is =AND(ISNUMBER(B2),B2>=1,B2<=100). It returns TRUE for a numeric score of 75 and FALSE for 120 or for text, so the rule accepts the former and rejects the latter.

A custom rule can also help prevent duplicate identifiers. Suppose entries are kept in A2:A100 and the rule is applied to that same range, with A2 as its top-left cell. Use =OR(A2="",COUNTIF($A$2:$A$100,A2)=1). A unique entry such as INV-104 appears once in the range, so the condition is TRUE; if that identifier appears in two cells, the count is 2 and the condition is FALSE. The blank check permits empty cells. When adapting this pattern, test it with both a new value and a duplicate, and confirm the formula references remain correct throughout the selected range.

Input Messages and Error Alerts

In the Input Message tab, a title and short instruction can tell users what to enter, such as “Enter a whole number from 1 to 500.” This is a prompt, not an enforcement mechanism: the rule in Settings does the checking. Keep the message specific, especially when a workbook will be used by people who did not create it.

In the Error Alert tab, choose whether an alert is shown and select its style. Stop prevents a user from accepting an invalid entry through ordinary typing in the cell. Warning lets the user choose whether to continue, while Information displays a notice that can be acknowledged. A warning or information alert is therefore less restrictive than Stop. The alert title and text should explain how to correct the problem rather than merely saying the entry is wrong.

Allow blank is another setting to review. It affects whether an empty cell can be accepted under the rule; it does not make a required field genuinely mandatory in every situation. If a completed form must have no blanks, add a separate completeness check or review step. Validation settings should match the purpose of each field rather than relying on defaults.

Important Limits and Common Problems

Data validation is a useful entry control, not a security boundary. Pasting, filling cells, or importing data can introduce values that do not follow a cell’s validation rule, and formula results may not behave like a person typing directly into the cell. A worksheet can therefore contain invalid data even when its cells have rules. Review or clean incoming data when accuracy matters, and do not use validation alone to protect confidential information or enforce permissions.

Another common issue is applying a rule to the wrong range or using the wrong relative reference in a custom formula. A formula written for row 2 may need to refer to B2, not a fixed B2, if it should adjust for every row. Conversely, absolute references, marked with dollar signs, are appropriate when a limit should always come from the same cell. Test representative cells at the top, middle, and bottom of the range.

Rules can also be overlooked when users copy a worksheet, insert rows, or change a list’s source. After making structural changes, verify the affected cells. To identify invalid entries already in a selected range, open the Data Validation menu and use Circle Invalid Data when available. This can help locate problems, but it does not repair them; correct the value or revise the rule as appropriate.

Designing Rules That People Can Follow

Before adding validation, decide what a valid entry means and whether the rule should block, warn about, or simply explain unusual values. A strict rule is appropriate for a fixed category or a required numeric limit. A softer warning may be better when legitimate exceptions are possible. Avoid narrowing acceptable data without a real business reason: for example, a text-length limit should reflect the actual format, not an arbitrary guess.

Use a short test plan before sharing a workbook:

Enter a value that should be accepted, including values at the permitted boundary.
Try values just outside the boundary, an incorrectly formatted entry, and a blank if blanks matter.
For a dropdown, test every choice and confirm the displayed instructions are clear.
Check what happens after copying or pasting, and explain any workflow restrictions to users.
Recheck the rule after changing its range, source list, or related cells.

Consistent rules make later analysis easier, but they should fit the people and process behind the workbook. A short instruction, a well-maintained source list, and a sensible review are often as important as the criterion itself.

Frequently Asked Questions

Do data validation rules stop every invalid value?
No. They can block invalid entries made through normal typing when a Stop alert is active, but pasting, importing, or other changes can bypass the rule. Check data after those workflows.

Can I create a dropdown list in Excel?
Yes. Select the cells, choose Data > Data Validation, set Allow to List, and specify the choices in Source. Turn on In-cell dropdown if you want the arrow displayed.

Can I use a formula to validate an entry?
Yes. Choose Custom in the Allow field and enter a formula that returns TRUE for acceptable entries and FALSE for unacceptable ones. The formula’s cell references should match the top-left cell of the applied range.

Why does an invalid value appear in a validated cell?
The value may have been pasted, filled, imported, or already present before the rule was applied. Validation does not automatically clean existing data, so inspect the range and correct invalid entries separately.

How do I remove data validation from cells?
Select the cells, open Data > Data Validation, and choose Clear All in the dialog. This removes the validation settings from the selection; it does not delete the cell contents.

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.