Data Validation Checks in Excel: Rules and Examples

Data validation checks are rules that control what users can enter in spreadsheet cells. In Microsoft Excel for Microsoft 365 on Windows, open Data > Data Validation to allow or restrict entries such as dates, numbers, text lengths, or choices from a list; a clear error message can help prevent invalid entries.

What data validation checks do

A validation rule tests an entry against criteria you set. For example, a cell might accept only whole numbers from 1 to 100, dates within a reporting period, or a department name from an approved list. If an entry fails the rule, Excel can block it or display a warning, depending on the error-alert setting.

These checks are useful wherever people enter or update data: survey responses, order forms, inventory sheets, staff records, and budgets. They improve consistency at the point of entry, which makes later sorting, filtering, calculation, and reporting more reliable. They do not establish whether information is true. A valid-looking date, for instance, can still be the wrong date for a particular transaction.

Validation is therefore best understood as a guardrail, not a complete data-quality system. It can prevent many accidental mistakes and guide users toward the expected format, but it cannot replace review of the underlying information.

Types of checks available in Excel

In the Data Validation dialog, the Allow setting determines the kind of check. Excel provides several built-in options, as well as a Custom option for a formula-based rule.

Whole number: Restricts entries to integers, such as a quantity between 1 and 500.
Decimal: Allows numeric values with decimal places, such as a measurement from 0 to 25.5.
List: Offers a set of permitted choices, often shown as a drop-down arrow in the cell.
Date or Time: Limits entries to a date or time range, such as dates on or after a project start date.
Text length: Restricts the number of characters, which can help standardize an ID or short code.
Custom: Uses a formula that returns TRUE for an acceptable entry and FALSE for an unacceptable one.

For built-in checks, choose a condition such as between, greater than, or less than, then enter the relevant limit or limits. The available comparison choices vary with the selected type. A list may be typed directly as comma-separated items or linked to cells containing the approved choices. For a long or frequently updated list, keeping choices in worksheet cells is usually easier to maintain than editing them inside the rule.

How to set up a validation rule

On Windows, select the target cell or range, then choose Data > Data Validation. In the Settings tab, select the check type and specify its criteria. If you want users to see a drop-down for a list rule, enable In-cell dropdown. The Input Message tab can show guidance when a validated cell is selected, while the Error Alert tab controls what happens when an entry fails.

Decide what counts as acceptable. Write down the allowed values or boundaries before setting the rule. For example, decide whether zero is a valid quantity and whether the end date is included.
Select the entry cells. Apply the rule to the whole intended range, not only the first cell, and confirm that the formula references will work throughout it.
Configure the rule and guidance. Choose the validation criteria, then add a short prompt if users may not know what format is expected.
Choose the error behavior. Use a blocking alert for values that must not be accepted, or a warning when exceptions may be legitimate.
Test both valid and invalid entries. Try a boundary value, a typical valid value, and at least one value that should fail.

Excel for Mac has a Data Validation command on the Data tab, although the dialog layout can differ by version. In Excel for the web, available commands and dialog details may also differ from the Windows desktop app. If a workbook is used across platforms, verify its rules in the versions people actually use.

Formula-based checks and examples

Custom validation is useful when a built-in rule cannot express the requirement, such as requiring a value to be unique or allowing a cell to remain empty while checking nonempty entries. The formula is evaluated for the selected cell; when applying the rule to a range, references should generally be written relative to its first cell.

Suppose scores are entered in A2:A100 and each must be from 0 through 100. Select that range, choose Allow: Custom, and use this formula. The reference A2 adjusts for each row, so the same rule checks A3, A4, and the later cells in turn.

=AND(A2>=0,A2<=100)

For a unique employee ID in A2:A100, select the range and use a count check. The formula permits an entry only when that value appears once in the range. As users fill the range, test the behavior with an existing ID and a new one; blank cells may need separate handling depending on how blanks are meant to work.

=COUNTIF($A$2:$A$100,A2)=1

If blank entries should be allowed, the following version accepts an empty A2 or a value that occurs only once. It assumes the rule is applied starting at A2; the relative reference updates for each row.

=OR(A2="",COUNTIF($A$2:$A$100,A2)=1)

For formula rules that depend on another cell, consider a start date in B2 and an end date in C2. Applied to C2:C100, this check accepts an end date only if it is on or after the start date on the same row. It also permits an empty end date, which may be useful while a record is incomplete.

=OR(C2="",C2>=B2)

Custom formulas can become difficult to understand or maintain. Keep them short, label the relevant columns clearly, and document any business rule that is not obvious. For complex requirements, it may be clearer to use a helper column or a review step than to put all the logic into one validation formula.

Lists, messages, and error alerts

Drop-down lists reduce spelling variations and make categorical data easier to summarize. A list of status values such as Pending, Approved, and Rejected is more dependable than asking every user to type a status freely. Directly typed lists are convenient for a few stable choices; a worksheet range is more practical when the list is long or likely to change. If choices are on another worksheet, a named range can be used as the list source.

Validation lists do not automatically make a list searchable or explain what each choice means. Use an input message to state the expected action, such as “Choose the current order status.” Keep the options concise and unambiguous; similar labels can still lead to inconsistent decisions even when spelling is controlled.

The Error Alert tab offers three styles:

Stop prevents the invalid entry when the rule is active.
Warning tells the user the value is invalid but lets them choose to continue.
Information informs the user and allows the entry to remain.

A Stop alert is suitable when the rule is mandatory. Warning or Information can be appropriate when exceptions are sometimes valid and should be reviewed. Make the message specific: explain what is wrong and what value or range is acceptable. A generic alert gives users little help correcting the entry.

Limits and common problems

One important limitation is that validation is not a guarantee that every value in the workbook meets the rule. Pasting data, filling cells, or using other methods to alter cells can bypass or replace validation in some circumstances. Formula results and data brought in from another source also need to be checked rather than assumed valid. Treat validation as a first line of defense, not as a substitute for auditing imported or edited data.

Rules do not necessarily repair or flag values that were already present before the rule was added. After setting a check on an existing sheet, review the old entries. Excel desktop includes a Circle Invalid Data command in the Data Validation controls that can help identify values that fail the current rule. You can also use filters, conditional formatting, or a separate check column to investigate problems.

Other common issues include:

A list arrow is missing: Check that the rule is a List rule and that In-cell dropdown is enabled.
A formula check gives unexpected results: Confirm that its first reference matches the top-left cell of the selected range and that fixed references use dollar signs where needed.
A valid entry is rejected: Recheck the limits, date interpretation, and whether the cell contains a value in the expected form.
Users can still enter exceptions: Confirm that the error alert is enabled and set to Stop rather than Warning or Information.
A rule disappears after copying: Review the destination cells after pasting, because copied content can affect the cell’s validation settings.

Validation compared with other quality tools

Data validation, conditional formatting, and data cleaning solve related but different problems. Validation determines whether an attempted entry meets a rule. Conditional formatting changes how a cell looks, often to draw attention to unusual values, but it does not stop someone from entering them. Data cleaning is the broader process of finding and correcting inconsistent, incomplete, duplicated, or incorrect data, often after it has been collected.

For example, a list rule can limit a status cell to approved labels, while conditional formatting can highlight records marked overdue. A separate review can determine whether those records are genuinely overdue. These tools complement one another: validation helps prevent certain errors, formatting makes patterns visible, and review or cleaning addresses issues validation cannot judge.

Use the simplest method that matches the requirement. A built-in whole-number or date rule is easier for another workbook user to inspect than a complicated custom formula. A drop-down is effective for a short set of categories, while a formula check is more suitable for a condition involving multiple cells. If the rule is difficult to explain, users may need clearer instructions or a redesigned entry process.

Frequently Asked Questions

Can Excel data validation stop users from pasting invalid data?
Not reliably in every situation. Pasting or other editing methods can bypass a check or overwrite the validation settings, so inspect imported and pasted values separately and review the rules after significant edits.

Does adding a validation rule check existing entries?
Do not assume that it does. Adding a rule controls future entries but does not automatically correct old values. Use Excel’s invalid-data tools or another review method to find existing entries that fail the new criteria.

Can I make a drop-down list from another worksheet?
Yes. A named range referring to the choices on that worksheet can be used as the list source. This keeps the options in worksheet cells, where they are easier to maintain than a long typed list.

What is the difference between Stop and Warning?
Stop blocks an entry that fails the rule. Warning alerts the user but allows them to choose whether to keep the value, which is useful when exceptions may be legitimate and should be reviewed.

Can data validation check more than one cell?
Yes. Select a range before creating the rule. For a custom formula, write references with the range’s top-left cell in mind; relative references can then adjust across the selected cells.

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.