An Excel data validation rule error usually means the entered value does not meet the cell’s rule, or the rule itself has an invalid setting or source. In Microsoft 365 for Windows, select the affected cell and choose Data > Data Validation to inspect the rule; then check the cell’s value, the rule’s criteria, and whether the value was pasted rather than typed.
What an Excel data validation error means
Data validation lets a workbook limit what people can enter in a cell. A rule might allow only whole numbers from 1 to 10, dates within a period, or items from a dropdown list. When an entry violates a rule and a Stop-style error alert is active, Excel rejects the entry and displays the alert message. The default wording may be generic, so the message does not always identify the underlying cause.
Not every problem appears as a popup. A dropdown may be missing, a rule may not apply to the cell you are editing, or invalid values may have entered through pasting. These are different symptoms, and they call for different checks. First identify whether Excel is rejecting a new entry, failing to show the expected choices, or allowing data that should be blocked.
Check the rule and its error alert
On Windows, select a cell that should have validation, then open Data > Data Validation. The Settings tab shows the validation type and its conditions. The Input Message tab controls the guidance shown when someone selects the cell. The Error Alert tab controls the alert shown after an invalid entry is attempted.
Check these settings before changing the rule:
- Allow: Confirm that the selected type, such as List, Whole number, Decimal, Date, Time, Text length, or Custom, matches the intended restriction.
- Criteria: Check the comparison operator and any minimum, maximum, start, or end values. A rule for values less than 10 will reject 10.
- Ignore blank: If this is selected, blank entries may be permitted even when the rule appears to require a value. It does not make an otherwise invalid nonblank value acceptable.
- In-cell dropdown: For a List rule, confirm this is selected if users should see a dropdown arrow.
- Error Alert: Confirm Show error alert after invalid data is entered is selected. If it is cleared, the rule can allow an invalid entry without displaying the blocking alert.
The Error Alert style matters. Stop prevents an invalid typed entry when the alert is enabled. Warning and Information alerts notify the user but let them choose to proceed. A workbook that appears to accept an invalid value may therefore be using a permissive alert style rather than having no rule.
Fix a value that Excel rejects
Compare the value with the actual rule rather than relying on what the cell looks like. A displayed date might conceal a different underlying value; a number may be stored as text; and an entry that looks like a whole number may contain a decimal portion. Also check boundary conditions: between usually includes the stated endpoints, while greater than and less than do not.
For example, if a cell allows whole numbers from 1 through 10, entering 0, 11, or 4.5 should fail. If the intended range is 0 through 10, edit the rule instead of repeatedly trying the value. For a Date rule, verify both the permitted date range and the date value Excel recognizes. Regional date formats can make an ambiguous entry such as 03/04/2026 mean different dates to different users; entering an unambiguous date or selecting it from a calendar, where available, can help.
For custom rules, check the reference and the range to which the rule applies. For example, if cells A2:A100 should accept whole-number values from 1 to 100, select A2:A100 with A2 as the active cell, choose Custom, and use =AND(A2>=1,A2<=100). The relative reference A2 is intentional: Excel adjusts it for each row. If the same rule is applied to a range but its formula refers to the wrong row or uses an unintended absolute reference, it may evaluate a different cell than the one being edited.
Repair a missing or broken dropdown
If a List rule has no visible arrow, select the cell and inspect Data > Data Validation > Settings. Confirm Allow is set to List and In-cell dropdown is selected. Also check whether the cell actually has a validation rule: select it and open the dialog. Selecting a neighboring cell can reveal that the rule was applied only to part of the intended range.
When the list source is a cell range, confirm that the source points to the correct cells and that those cells contain the intended items. A source range with blank cells can produce blank choices. If the source range is on another worksheet, a direct cross-sheet reference may not be accepted in the Source box. One common approach is to define a named range for the source cells and use that name as the list source. If the list is maintained by other people, check that its cells have not been moved, deleted, or replaced.
Do not confuse a missing dropdown arrow with a rule that is not working. The arrow is a convenience for choosing a list item; a list validation rule can still reject a typed value even when the arrow is hidden. Conversely, seeing a dropdown does not prove every value in the cell is valid, particularly if someone pasted data into it.
Why invalid data can get into validated cells
Data validation is useful for guiding entry, but it is not a foolproof data-integrity boundary. Pasting or filling cells can bypass the normal entry check, and copying a cell can also replace the destination’s validation rule with the copied cell’s properties. Imported data, formulas, or edits made by other tools can create similar surprises. As a result, an existing invalid value does not necessarily mean the rule was configured incorrectly.
To review existing entries, select a cell in the relevant range and use Data > Data Validation > Circle Invalid Data, if that command is available in the Data Validation menu. Excel marks values that violate the current rule with circles. Review the marked cells, correct the data, and remove the circles using Clear Validation Circles. This check compares the current values with the rules; it does not prevent future pasting from introducing another invalid value.
For important workbooks, combine validation with other checks. A summary formula, conditional formatting, or a review step can make out-of-range values visible. Protecting a worksheet can reduce accidental changes to rules, though protection is not a substitute for checking data and does not make validation a security control. If data is collected from external sources, validate it after import as well as at the point of entry.
Apply a corrected rule without creating new problems
Before editing a rule across a large range, check which cells currently use it. In the Data Validation dialog, Excel may offer to apply changes to other cells with the same settings. Use that option only when those cells should share the revised rule. Changing one cell does not automatically guarantee that every similar-looking cell has the same validation.
When fixing a list rule, keep the source items in a clearly maintained range and verify the source after adding or moving items. When fixing a numeric or date rule, test a permitted value, a value at each boundary, and a value just outside the range. For custom formulas, check a few cells at different rows to confirm that relative references adjust as intended. If the rule is meant to require an entry, test a blank cell too.
If the dialog says the value cannot be used or the validation settings cannot be changed, check whether the worksheet is protected. Unprotect it if you are authorized and have the required password, make the change, then restore protection if appropriate. Also confirm that you are editing the intended workbook and worksheet; a similarly named sheet or a copied template may have a different rule.
Excel for Mac and Excel for the web
In current Excel for Mac, data validation is also available from the Data tab, though the ribbon layout and wording can vary with the app version. Look for Data Validation on the Data tab and inspect the same settings: validation type, conditions, input message, and error alert. Keyboard shortcuts for opening the dialog can differ by platform and version, so using the ribbon command is the dependable route.
Excel for the web supports common data validation tasks, but some workbook features and dialog options may differ from the Windows desktop application. If a rule is complex, a source behaves unexpectedly, or the required command is unavailable in the browser, open the workbook in desktop Excel and check it there. Be cautious when switching applications: confirm the rule after editing and test it in the version that the workbook’s users will rely on.
Common misconceptions about validation
A validation rule does not automatically repair old data. It governs eligible new entries under its settings, while existing values may remain even after a rule is added or changed. Use Circle Invalid Data or another review method to find values that no longer comply.
Another misconception is that every alert blocks entry. Only the Stop style is designed to prevent the user from proceeding after an invalid typed entry; Warning and Information allow a choice to continue. Finally, a dropdown is not a guarantee that a cell contains only a list item. Pasting can bypass the usual check, so review data when accuracy matters.
Frequently Asked Questions
Why does Excel say the value violates data validation?
The value does not meet the selected rule, or the active rule is different from what you expected. Open Data > Data Validation for the cell and check its type, limits, error alert, and applied range.
Why can I type an invalid value despite data validation?
The Error Alert may be disabled or set to Warning or Information, which allows the user to continue. Pasting and filling can also bypass normal validation checks.
How do I find invalid values already in a worksheet?
Select a cell in the range and choose Data > Data Validation > Circle Invalid Data, when available. Correct the marked values, then use Clear Validation Circles to remove the marks.
Why is my data validation dropdown missing?
Check that the cell has a List rule, In-cell dropdown is selected, and the list source is valid. Also confirm that you selected the cell with the rule, not a neighboring cell without one.
Does data validation prevent copy-and-paste errors?
No. Pasting can put an invalid value into a validated cell or replace its rule. Check imported and pasted data afterward, especially when the values affect reports or decisions.
Related
- How to Insert a Checkbox in Excel: Steps and Tips
- Change Text to Numbers in Excel: Easy, Reliable Methods
- Center Across Selection in Excel: Add It to Quick Access
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Flash Fill Not Working? Common Causes and FixesIf Flash Fill is not working in Excel, enter one or two examples beside your source data, select…
- Page Breaks in Excel Won’t Go Away? How to Fix ThemIf page-break lines in Excel won’t go away, switch to View > Normal to leave Page Break Preview,…
- Excel Formulas Showing as Text? Fix It FastIf your Excel formulas are showing as text instead of calculating, the most common cause is the cell…
- Microsoft Outlook Block Sender Not Working? Try These FixesIf Microsoft Outlook’s Block Sender command is not stopping unwanted messages, check that the address is on the…
- Excel Share Not Working? Fix Sharing and CoauthoringIf Excel’s Share button is missing, greyed out, or not giving someone access, first save the workbook to…
- Excel Cell Keeps Changing to Date? Stop the Auto-FormatIf an Excel cell keeps changing to a date, format the cell as Text before entering the value,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.