Excel Data Validation Error Alert Not Working?

Errors & fixes · Updated November 19, 2026

If an Excel data validation error alert is not appearing, open Data > Data Validation > Data Validation, select the Error Alert tab, and check Show error alert after invalid data is entered. Choose Stop if invalid typed entries must be rejected. If the box is already checked, test by typing an invalid value directly: pasting, filling, or calculating a value can bypass the alert.

What the error alert does

Data validation is a rule attached to one or more worksheet cells. It can restrict entries to a list, a number range, dates, text length, or a condition defined by a formula. The error alert is the message Excel displays when a user tries to enter something that violates the rule. It is separate from the rule itself: a rule can remain in place even when its alert is disabled.

On Excel for Microsoft 365 for Windows, select a validated cell and choose Data > Data Validation > Data Validation. In the dialog, the Settings tab defines what is allowed; the Error Alert tab controls the warning. The Show error alert after invalid data is entered checkbox must be selected for Excel to display the alert during direct entry.

The alert style also matters:

  • Stop rejects an invalid value entered directly and lets the user retry or cancel.
  • Warning warns about the invalid value but offers a way to keep it.
  • Information displays a notice but also lets the user accept the entry.

Thus, seeing a dialog does not always mean the value is blocked. If the goal is to prevent invalid manual entries, choose Stop, not Warning or Information. The optional title and error message change what the dialog says; they do not change the underlying validation condition.

Check the rule and alert settings

Start by selecting the exact cell where the problem occurs. A worksheet can contain different validation rules in neighboring cells, and a rule may have been applied to only part of the intended input range. Open the Data Validation dialog and inspect both Settings and Error Alert rather than relying on what the worksheet appears to allow.

  1. On the Settings tab, confirm the Allow choice is the expected type, such as List, Whole number, Decimal, Date, or Custom.
  2. Check the criteria and any minimum, maximum, source, or formula. A rule that permits the entered value will not trigger an alert.
  3. On Error Alert, select Show error alert after invalid data is entered.
  4. Choose Stop if the entry should be rejected, then select OK.
  5. Enter a value that is clearly outside the rule by typing it into the cell, then press Enter.

For example, a whole-number rule from 1 through 10 accepts 7, so no error is expected. Typing 12 is a useful test. Make sure the test value really violates the configured rule; an alert that does not appear for a permitted value is normal behavior, not a malfunction.

Test by typing, not by pasting

A common source of confusion is the way the value was entered. Excel’s validation alert is intended to respond when a person enters a value directly into the cell. Copying and pasting, using the fill handle, dragging cells, or changing a formula that returns a value can place an invalid result in a validated cell without showing the same alert. The validation rule is not a universal barrier that checks every possible way a cell can change.

For a clean test, click the cell, type an invalid value, and press Enter. Do not copy it from another cell or use Ctrl+V. On Windows, Ctrl+Z undoes many ordinary worksheet edits; on Mac, use Command+Z. Undo is useful for a test entry, but it does not turn validation into a safeguard against every edit method.

Pasting can cause a second issue: depending on what is copied, the paste may bring cell formatting or validation settings along with the value. A pasted cell can therefore replace the destination’s rule, not merely put a value into it. If the rule itself seems to have disappeared, reopen Data Validation on the affected cell and compare it with a cell that still behaves correctly.

Check custom formulas and list sources

With Custom validation, Excel tests a formula for the cell being validated. The formula must evaluate to TRUE for an allowed entry and FALSE for a rejected one. A mistaken reference or reversed condition can make a rule accept everything, reject everything, or behave differently lower down the range. When applying one formula to a range, write its references for the range’s top-left cell and understand which references should move as the rule applies to other cells.

If entries are in A2:A100 and each must be a numeric value from 1 through 100, select A2:A100 and use this Custom validation formula, written relative to A2:

=AND(ISNUMBER(A2),A2>=1,A2<=100)

For this example, 7 is accepted and 120 is rejected when typed directly. ISNUMBER also prevents text such as abc from passing merely because it does not compare like a number. If a formula is already present, inspect its references and test both a value that should pass and one that should fail. A rule can be syntactically valid yet express the wrong condition.

For a List rule, verify the Source field and its range or typed list. A source range may be blank, may not include the expected item, or may point to the wrong sheet or cells. The in-cell dropdown is a convenience for choosing an allowed item; it is not the error alert. Turning off the dropdown does not necessarily disable validation, and seeing a dropdown does not prove that the alert checkbox is enabled.

Find existing invalid values

Changing or adding a validation rule does not automatically clean up values that were already in the cells. Nor does turning on the alert retroactively display a warning for old entries. This matters when someone pastes a block of data, imports values, or inherits a workbook with inconsistent inputs: invalid data may be present even though the rule now looks correct.

To locate values that violate the current rules in Excel for Windows, select the relevant range or worksheet and choose Data > Data Validation > Circle Invalid Data. Excel marks detected invalid entries with circles. After reviewing them, correct the values or remove the circles using Data > Data Validation > Clear Validation Circles. The circles are a review aid; they do not repair the data or prevent a later paste.

If the command is unavailable or does not find the value you expect, verify that the selected cells actually have the intended validation rule. Also consider whether a pasted value changed the rule or whether the current criteria now allow that value. Checking the cell’s rule and the cell’s contents separately is more reliable than assuming one proves the other.

Other causes that look like a failed alert

Before rebuilding a rule, rule out a few simple explanations. The entry may be valid under the current criteria, the alert checkbox may be off, or the alert style may allow the user to accept the value. Another possibility is that the user is editing a different cell from the one that has validation. Selecting the cell and opening the dialog confirms what Excel has actually applied there.

  • The cell is blank: Many validation types allow blank cells, especially when Ignore blank is selected. That option is not the same as the error-alert checkbox.
  • The rule was applied to the wrong range: Select cells in different parts of the input area and check whether their Settings are consistent.
  • The sheet is protected: Protection can limit whether cells or validation settings can be edited. It does not make an incorrect validation rule correct; check the rule on an editable copy or with the workbook owner.
  • The alert message is unclear: A blank or generic title/message can make a dialog easy to miss. Add a concise message on Error Alert so users know what value is expected.

Do not confuse an Input Message with an Error Alert. An input message can appear when a user selects a cell, before an entry is made. It explains what to enter but does not reject anything. The Error Alert appears after an invalid direct entry is attempted, provided its checkbox is enabled and the value actually fails the rule.

Limits of validation and safer workflows

Data validation is useful for guiding normal worksheet entry, but it should not be treated as a security control or a guarantee that every stored value is valid. Pasting, formulas, external data, macros, and other workflows can introduce values that were not blocked by the interactive alert. A workbook used for important reporting should include a way to review the data after entry, such as a separate check column, a review process, or a controlled import procedure.

For routine spreadsheets, a practical workflow is to apply validation to the full input range, use clear instructions, choose Stop for values that must not be entered manually, and periodically check for invalid values. If users need to paste large batches, validate the imported data after the paste rather than assuming the alert checked each cell. Keep a clean copy of the intended rule or document the criteria so that accidental pastes can be repaired consistently.

Excel for Mac has data-validation controls too, though dialog layout and some menu details can vary by release. Excel for the web may present a different interface and offers a different set of editing controls from the Windows desktop app. If a workbook behaves differently across platforms, inspect the rule in the desktop version and test the actual entry method used by the people filling it in.

Frequently Asked Questions

Why does Excel accept an invalid value even though validation is on?
It may have been pasted, filled, returned by a formula, or entered through another process that bypasses the interactive alert. It is also possible that the rule allows the value or uses Warning or Information rather than Stop. Test by typing a definitely invalid value directly.

How do I make Excel stop users from entering invalid data?
Open Data > Data Validation > Data Validation, confirm the Settings criteria, then select Show error alert after invalid data is entered on Error Alert and choose Stop. This blocks invalid direct entries, but it does not guarantee that pasted or formula-generated values will be blocked.

Why is the error alert not showing for a dropdown list?
The dropdown and the alert are separate settings. Check that the cell’s List source is correct and that Show error alert after invalid data is entered is selected. Then test by typing a value that is not in the list; choosing an item from the dropdown should not trigger an error.

Does Excel data validation check values already in a column?
No. A rule does not automatically remove or flag every existing invalid value just because it is applied. Use Data > Data Validation > Circle Invalid Data to review detected entries, then correct them.

Can I use validation to prevent every kind of invalid data?
No. It is a helpful worksheet-entry feature, not a complete data-integrity or security system. Pasting and other ways of changing cells can bypass the alert, so important datasets need an additional review or verification step.

Related

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.