Excel Data Validation Error: Causes, Fixes, and Tips Explained

An Excel data validation error means an entry conflicts with a rule set for that cell, such as a permitted list, date range, or number limit. To fix it, select the affected cell, open Data > Data Validation, and either enter a value the rule permits or revise the rule; if the problem is an incorrect warning, check the Error Alert settings too.

What a Data Validation Error Means

Data validation is a worksheet feature that controls what people can enter into selected cells. A workbook might restrict a status column to a list such as Open, In progress, and Closed, or require a number between 1 and 100. When an entry violates a rule and the rule is set to block it, Excel displays an error alert and rejects the entry.

The exact message varies with the Excel version and the rule. It may say that the value does not match the data validation restrictions defined for the cell, or that the value entered is not valid. The message does not necessarily mean the workbook is damaged. Often, it simply indicates that the cell has a rule the person entering data did not know about.

It also helps to distinguish a validation alert from a formula error such as #VALUE! or #NAME?. A validation alert appears while someone is trying to enter a value that breaks a rule. A formula error appears in a cell when a formula cannot calculate as expected. They have different causes and require different fixes.

How Excel Validation Rules Work

A validation rule is attached to one or more cells. Excel checks a new entry against the selected criteria, which can include whole numbers, decimals, dates, times, text length, a list, or a custom formula. Optional input messages can explain what to enter when someone selects a cell. Error alerts can explain why a rejected value is not allowed.

On Excel for Microsoft 365 for Windows, select the cell or range, then choose Data > Data Validation. The Settings tab shows the validation criteria. The Input Message tab controls the prompt displayed when a cell is selected, and the Error Alert tab controls the warning shown after an invalid entry. If several cells are selected, check the dialog carefully: the settings may differ among them.

The Error Alert tab has a Show error alert after invalid data is entered option and three alert styles:

Stop blocks the invalid entry. This is the strictest option.
Warning tells the user the entry is invalid but allows them to choose whether to continue.
Information displays a notice but allows the entry to remain.

If the alert checkbox is cleared, Excel may accept values that violate the rule without displaying the configured alert. That does not mean the validation criteria have disappeared; it means the alert is not being shown as configured.

Fix the Entry or Change the Rule

Start by deciding whether the value or the rule is wrong. If the worksheet is meant to enforce a standard, keep the rule and enter a compliant value. If the requirements have changed, update the validation settings with permission from the workbook owner. Removing a rule just to silence an alert can allow inconsistent or unusable data into the sheet.

Select the cell that displays the alert and read the message, if one appears.
Open Data > Data Validation and review the Settings tab. Note the type, operator, minimum and maximum, or list source.
Compare the attempted value with the requirement. Check details such as spaces, spelling, date order, decimal limits, and whether a list choice is required.
If the rule is correct, enter a value that satisfies it. If the rule is incorrect or outdated, edit the criteria and select OK.
Try a valid value and, if appropriate, an invalid one to confirm that the rule behaves as intended.

For example, suppose a quantity cell allows whole numbers from 1 through 50. Entering 0, 51, or 2.5 should fail a strict rule. If the cell is meant to record fractional quantities, the correct repair may be changing the validation type from Whole number to Decimal, rather than increasing the allowed maximum.

Check List Sources and Custom Formulas

List-based validation is a frequent source of confusion. A list can use items entered directly in the Source box or a range of cells containing the permitted choices. A range is usually easier to maintain when the choices are numerous or likely to change. If a list unexpectedly rejects an item, inspect the source range for missing choices, extra spaces, blank cells, or a reference that no longer covers the full list.

For a worksheet with choices in F2:F10, a validation list can use that range as its source. In the Data Validation dialog, select List and specify the source range. If the list is stored on another worksheet, Excel may require a named range rather than a direct cross-sheet reference in the Source box. Create or inspect names through Formulas > Name Manager, and verify that the name refers to the intended cells.

Custom validation uses a formula that returns TRUE for allowed entries and FALSE for disallowed ones. For example, with the first cell of the range at A2 and the rule applied to A2:A100, the formula =AND(A2>=1,A2<=100) permits numbers from 1 through 100. The reference A2 is relative so Excel can adjust it for each cell in the applied range. If the formula is written for a different starting cell, the reference must be adjusted too.

A common custom-rule mistake is anchoring the wrong reference. In a formula such as =COUNTIF($F$2:$F$10,A2)=1, assume the allowed choices are in F2:F10 and the rule is applied starting at A2. The formula accepts an entry in column A when it appears exactly once in the allowed range. The dollar signs keep the list range fixed, while A2 changes for each row. If the formula refers to the wrong row or column, valid entries can be rejected and invalid ones may pass.

Find Existing Invalid Values

Data validation generally governs future edits; adding a rule does not automatically repair or remove values already present. Pasting, filling, or importing data can also leave cells containing values that do not satisfy the current rule. Consequently, a cell may appear to have a validation setting and still contain an older invalid value.

To locate existing violations in Excel for Microsoft 365 on Windows, select the relevant cells or the worksheet area, then choose Data > Data Validation > Circle Invalid Data. Excel marks values that do not meet the applicable rules with circles. After reviewing and correcting them, use Data > Data Validation > Clear Validation Circles to remove the marks. The circles identify suspected invalid entries; they do not themselves change the cell values.

For a large workbook, inspect the intended input range rather than assuming every cell on the sheet has the same rule. You can also use Find or filters to investigate suspicious values, but compare results with the actual validation criteria. For instance, a text value that looks like a date may be stored as text, while a date displayed in a familiar format may have a different underlying value than expected.

Why Validation Errors Keep Appearing

If an alert returns after a value is corrected, check whether the rule applies to the full range and whether multiple cells have different settings. A neighboring cell may look identical but use a different validation type. Also check whether a formula is producing a value outside the allowed range or whether a copied cell brought a validation rule along with it.

Copying and pasting deserves special attention. Validation is not a foolproof security barrier: pasted or filled data may bypass the usual entry prompt or bring formatting and validation settings from another cell. If data quality is important, review imported or pasted values afterward, use Circle Invalid Data, and consider protecting the sheet to reduce accidental changes to the layout. Worksheet protection is a safeguard against routine editing, not a substitute for access controls or a guarantee that data cannot be changed.

Another common cause is a mismatch between the workbook’s intended rule and its regional or data-format expectations. Dates can be interpreted differently depending on settings, and list separators can vary with regional configuration. When a direct list behaves unexpectedly, using a cell range as the source is often clearer and easier to maintain than typing a long separator-delimited list.

Edit or Remove Validation Safely

To change a rule, select the affected cells, open Data > Data Validation, edit the Settings or Error Alert tab, and choose OK. If the selected cells contain different rules, Excel may warn that the selection has multiple validation types. Select a consistent range or inspect cells individually before applying a replacement rule, so you do not unintentionally overwrite a useful restriction.

To remove validation through the dialog, select the target cells, open Data > Data Validation, and choose Clear All. This clears the validation criteria and related validation settings for the selection; it is different from using Home > Clear > Clear All, which can also remove cell contents and formatting. Confirm the selected range before clearing anything, especially in a shared or important workbook. On Excel for Mac, the Data Validation controls are also on the Data tab, though the ribbon layout and dialog appearance can differ. Excel for the web may present a different interface, so use the desktop app if a control or option is unavailable in the browser.

Prevent Future Validation Problems

Good validation design makes the rule understandable as well as restrictive. Use an input message to show the expected format, and make the error alert specific. A message such as Enter a whole number from 1 to 50 is more useful than Invalid entry. Keep list choices in a clearly labeled source range, and update that source when the permitted options change.

Apply rules to the complete intended input range, including rows that will be used later.
Test a normal valid value and a likely invalid value before sharing the workbook.
Use Stop when an exception must not be entered; use Warning or Information only when users may legitimately need to override the rule.
Review data after imports, pastes, or bulk edits, since validation may not catch every route by which values enter a workbook.
Document unusual custom formulas and avoid making a rule more complicated than the data requirement calls for.

Validation is most effective when paired with clear instructions and occasional review. It helps prevent accidental mistakes, but it cannot establish that information is true. For example, a date rule can ensure that an entered value falls within a date range, but it cannot confirm that the date reflects a real event.

Frequently Asked Questions

How do I remove a data validation error in Excel?
Select the cell and either enter a value allowed by its rule or change the rule through Data > Data Validation. To remove the rule for selected cells, use Clear All in the Data Validation dialog, checking the selection before confirming.

Why does Excel reject a value that looks correct?
The cell may have a stricter rule than expected, such as a whole-number requirement, a limited date range, or a list that does not include the typed value. Check the Settings tab, the list source, spaces, and whether the value is stored in the expected type.

Can copied or pasted data bypass data validation?
It can. Pasting or filling values does not always trigger the same prompt as typing directly, and a copied cell can also carry its own validation settings. Review pasted data against the rule, using Circle Invalid Data where appropriate.

How can I find invalid data already in a worksheet?
On Excel for Microsoft 365 for Windows, choose Data > Data Validation > Circle Invalid Data. Correct the marked entries, then choose Clear Validation Circles to remove the indicators.

Does data validation prevent every incorrect entry?
No. It can guide ordinary entry and block many mistakes, but pasted data, existing values, or other changes may not be caught automatically. Validation checks whether data meets a defined rule, not whether the data is factually true.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.