An Excel data validation error message is the alert shown when someone enters a value that violates a cell’s validation rule. In Excel for Microsoft 365 on Windows, select the validated cells, choose Data > Data Validation > Error Alert, turn on “Show error alert after invalid data is entered,” and write a title and message.
What the error message does
Data validation controls what users can enter in selected cells. A rule might limit an entry to a whole number from 1 to 100, a date within a specified period, or one of a list of approved choices. The error alert explains that an attempted entry does not meet the rule and, depending on the alert style, either blocks it or asks the user to decide whether to keep it.
When a user types a disallowed value into a cell and confirms the entry, Excel displays the configured alert. If you have not customized the alert, Excel uses its standard message. A useful custom message identifies both the problem and the acceptable action—for example, “Enter a whole number from 1 to 100.” This is more helpful than a vague warning such as “Invalid entry.”
The error alert is different from the optional input message. An input message can appear when a user selects a cell and can explain what to enter before they make an attempt. The error alert appears after an invalid entry is submitted. You can use either feature independently or use both together.
How to set up an error alert
Use this process in the Windows desktop version of Excel for Microsoft 365:
- Select the cell or range that has the validation rule. To check a rule first, select a cell and choose Data > Data Validation.
- In the Data Validation dialog, open the Error Alert tab.
- Make sure “Show error alert after invalid data is entered” is selected.
- Choose an alert style: Stop, Warning, or Information.
- Enter a short title and a clear error message, then select OK.
The title appears as the dialog’s heading; the message explains what the user should do. The dialog’s title and message fields are optional, but a specific message is usually preferable in a shared workbook. If the validation rule is applied to several cells, selecting the entire range before editing it helps ensure you are changing the intended cells.
To add a rule and its alert in one pass, select the cells and choose Data > Data Validation. On the Settings tab, choose the validation criteria and set the allowed values. Then configure the Error Alert tab before selecting OK. On Excel for Mac, the Data Validation command is also on the Data tab, though dialog layout can vary by release. Excel for the web offers data-validation controls, but some dialog details and capabilities may differ from desktop Excel; use the Windows or Mac desktop app if a control shown here is missing.
Stop, Warning, and Information: choosing a style
The alert style determines what happens after an invalid value is entered. Pick a style according to how strictly the workbook needs to enforce the rule, rather than choosing one just for its appearance.
- Stop: Blocks the invalid entry. The user must correct it or cancel the edit. Use this when values outside the rule must not be accepted through ordinary typing.
- Warning: Tells the user the entry is invalid but offers a way to continue with it. Use it when the rule is a strong recommendation but exceptions may be legitimate.
- Information: Informs the user that the entry does not meet the rule and lets them continue. Use it for guidance rather than strict enforcement.
For example, a department code chosen from a controlled list may need Stop, because an unapproved code could break reporting. A target date that is usually within a planned window but sometimes needs an exception may suit Warning. Information is a gentle reminder, not a reliable barrier to invalid data. With Warning or Information, users can choose to keep the value, so the rule does not guarantee that every stored entry is valid.
Which validation rules can show an alert?
Excel data validation supports several kinds of criteria, including whole numbers, decimals, dates, times, text length, lists, and custom formulas. The Settings tab defines what is allowed; the Error Alert tab controls how Excel responds when a typed value fails that rule. The message should reflect the actual criteria, especially if the rule changes later.
For example, a list rule can restrict a cell to choices such as “Open,” “In progress,” and “Complete.” A clear alert might say, “Choose a status from the list.” If the list has an in-cell dropdown, users can select a valid option rather than typing it. The dropdown is a convenience, however, not a replacement for the validation rule or a well-written alert.
Validation rules may also use custom formulas. Suppose cells A2:A100 must contain whole numbers from 1 through 100. Select A2:A100, choose Data > Data Validation, select Custom on the Settings tab, and use this formula:
With A2:A100 selected and A2 as the active cell, use =AND(A2>=1,A2<=100,MOD(A2,1)=0). It accepts 42 because it is an integer in the permitted range, and rejects 101 and 42.5. The reference is relative so Excel evaluates the corresponding row in each cell.
Set the Error Alert style to Stop and use a message such as “Enter a whole number from 1 to 100.” If the selected range begins at a different row, adjust the formula’s first-cell reference to match. A custom formula must return TRUE for an allowed value and FALSE for a disallowed one.
Write a message users can act on
A good alert is short, specific, and consistent with the rule. State what went wrong and what will pass validation. Avoid relying on technical terms such as “criterion” or “formula” when the workbook is meant for general users. If there is a legitimate exception process, say who should be contacted or what the next step is.
- For a date rule: “Enter a date from 1 Jan through 31 Dec 2026.”
- For a text-length rule: “Use no more than 20 characters.”
- For a list: “Select an approved status from the dropdown.”
- For a required numeric range: “Enter a whole number from 1 to 100.”
Check that the wording matches the actual boundaries. If a rule allows values “between” two limits, confirm whether the endpoints are included. Also consider whether the instruction will still make sense if someone copies the workbook into another year or changes the allowed list. A message that says “enter this year’s date” may become misleading if the rule is not updated annually.
Why an error alert may not appear
If a user can enter something unexpected without seeing the alert, first inspect the cell’s rule and Error Alert settings. Select the cell and choose Data > Data Validation. Confirm that the correct criteria are on the Settings tab and that “Show error alert after invalid data is entered” is checked. Also check whether the alert style is Warning or Information, since those styles allow a user to keep an invalid value.
Another common source of confusion is how the value arrived in the cell. Validation is not a complete audit system. Pasting or filling data into cells can bypass the usual validation prompt, and a formula can return a value that does not meet the rule without triggering the same entry-time alert. Existing invalid values are not necessarily removed just because a rule is added later. Therefore, a workbook may contain a value that would have been rejected if someone had typed it directly.
When checking a range for questionable values, select it and use Data > Data Validation > Circle Invalid Data, if that command is available in the desktop version being used. Excel marks values that do not comply with the current validation rules. This helps locate exceptions, but it does not prevent them from being created by other means. Review pasted or imported data and, where accuracy is important, use a separate checking process suited to the workbook.
Also verify that the rule applies to the cell being edited. A nearby cell may have validation while the target cell does not, or the rule may cover only part of a range. If the workbook is protected or shared, confirm that users can edit the intended cells and that the rule has not been changed. A blank entry may be allowed depending on the rule’s settings, so check whether “Ignore blank” is selected when blanks are not expected.
Validation alerts versus other Excel warnings
A data validation alert is not the same as an Excel formula error such as #VALUE! or #N/A. Formula errors result from a calculation or reference problem. A validation alert is tied to an input rule and normally appears when a user submits an entry that fails that rule. Changing the Error Alert message will not fix a formula error, and changing a formula will not change the validation alert settings.
It is also different from conditional formatting. Conditional formatting can highlight a suspicious value after it appears, but it does not block an entry or display the validation dialog. These tools can complement each other: validation helps guide entry, while formatting can make exceptions easier to spot during review. Neither should be treated as a guarantee that pasted, imported, or calculated values all comply.
For more robust data collection, use validation alongside clear column headings, dropdowns where suitable, and checks before reports are produced. If data comes from another system, validate it during import or review it after loading. Data validation is a helpful worksheet-level control for everyday entry, not a substitute for every form of data-quality checking.
Frequently Asked Questions
How do I change the default Excel validation error message?
Select the validated cells, choose Data > Data Validation, open Error Alert, and enter a title and message. Leave “Show error alert after invalid data is entered” selected, then choose a style and select OK.
Can I make Excel show a warning but still allow the value?
Yes. Choose Warning or Information on the Error Alert tab. Both let the user proceed with an invalid entry; Stop blocks it. Use Warning when the user should consciously confirm an exception, and Information when the alert is primarily guidance.
Why does pasting bypass data validation?
Some paste and fill operations do not trigger the normal validation alert. Validation is primarily an entry-time prompt, not a guarantee that all values in a range are valid. Check pasted data and use Excel’s invalid-data checking tools where available.
Can I show instructions before someone enters a value?
Yes. Open Data > Data Validation, choose the Input Message tab, and enable the option to show an input message when the cell is selected. This message can explain the expected entry before an error occurs; it is separate from the Error Alert tab.
Does adding a validation rule fix values already in the cells?
No. Applying a rule does not automatically correct existing entries. Review the range for values that do not comply, and correct or investigate them as appropriate.
Related
- Contacts List Template Excel: Build a Useful Workbook
- Contact List Excel Template: Free Options and Setup
- Contact List Format in Excel: Columns, Setup, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Circular References: Find, Fix, and Understand ThemA circular reference in Microsoft Excel happens when a formula refers to its own cell, either directly or…
- COUNTA Formula Not Working? Fix Common Excel IssuesCOUNTA often seems not to work because it counts cells containing formulas that return an empty string, spaces,…
- Excel Pull-Down Formula Not Working? Fix Common CausesIf an Excel formula will not pull down, select the cell containing it and drag the small square…
- Dropdown List in Excel: Create, Edit, and Fix OneIn Excel for Microsoft 365 on Windows, select the cells for the dropdown, then choose Data > Data…
- Excel Filter Not Working With Merged Cells? Fix ItIf Excel’s filter is missing, disabled, or returning confusing results around merged cells, unmerge the cells in the…
- Transpose Error in Excel: Causes and FixesTo switch rows and columns in Excel, copy the source range and choose Home > Paste > Transpose,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.