If you use a Excel application frequently then you must be familiar with the feature of excel function that is “Data Validation”. In excel 2010 by using data validation feature users can ensure the data-type integrity by enforcing users to enter valid data from the range you have been selected off. Likewise you can also input message that appears before entering data, it helps users to explain with valid entries and error message that appear in case of any invalid data entered. In this tutorial we will talk about how to set error alerts for cell correctly.
Manual Solution To Resolve MS Excel Data Validation Error
Create Data Validation Rule
To create the data validation rule, execute the following steps.
- Select cell C2.
- On the data tab, click data validation.
On the Settings tab:
- In the allow list, click whole number.
- In the data list, click between.
- Enter the Minimum and Maximum values.
Input message appear when the user selects the cell and tell the user what to enter. On the
Input Message tab:
- Check ‘Show input message when cell is selected’.
- Enter a title and also an input message.
Data Validation Error Messages
If you add a drop list on a worksheet or any other type of data validation, you have the option to select for the different error alert message like Stop, Warning or Information.
The default one is the Stop and that prevent you from entering the invalid data. An error message appears with the button for Retry, Cancel and Help.
“This value doesn’t match the data validation restrictions defined for this cell.”
Respond to the Error Message
If you see that error message, what happen if you click one of the buttons?
- Help– Takes you to data validation page on the Microsoft website, where there are instructions for settings up a data validation cell. It won’t give you any specific details on why the value entered wasn’t valid.
- Cancel– Clears the cell, so that you can type a new value or select from the drop down list, if there is one.
- Retry– Highlight the value which you have typed in the cell, so that you can type a new value. You will have to clear the cell if you want to use the drop down arrow.
Customized Error Messages
Instead of leaving the default settings for data validation errors you can check out the Error Alert tab in the data validation dialog box, and customize them.
Choose one the styles and enter a suitable title and error message that will best define other on what went wrong.
Suppose you have selected warning style and entered a customized message. Now if you enter an item that is not in the list the customized message appears along with a Warning icon, and different buttons.
Turn off error messages:
If in case you desire to turn off the data validation error alerts completely then also you have the option.
On the Error Alert tab, remove the tick mark from “Show error alert after invalid data is entered” option.
Data Validation Error Messages in Tables
If you are using data validation in a named Excel table, invalid data might be flagged by excel’s error Checking Rules, even if you turned off error alerts or set the Style to Warning or Information.
In the screen shot shown below, there is a warning on cell C2, because two items are in the cell.
If you copied cell C2 to a different part of the worksheet, outside of a table, the error warning should disappear- it only affects tables.
Turn Off Table Data Setting
If you think that there is not any problem with the cells data, you can simply ignore the error warnings. Or if there are just a few messages, you can set the error message in each cell manually, to ignore error.
If you want to turn off all of the data validation warnings in tables, you can follow the steps given below, to change one of the excel options.
- Click the arrow on the error alert and click error Checking Options
- In the option window, in the formulas category, scroll down to the Error Checking Rules section.
- Remove the check mark for “Data entered in a table is invalid”.
- Click OK.
Keep in mind, that excel will stop flagging ALL data errors in your tables, in ALL workbooks that you open.
A Method to Solve Problems of Error Alert
In the above part, we have figured out the different styles of error alert and you know that in some circumstances, the invalid value will remain in the cells. Therefore to handle this issue you can use the additional features to check for the errors.
- Click the tab “data” in the ribbon.
- And then click the small arrow next to the button “Data Validation”.
- In the drop down menu click the option “Circle Invalid Data”.
4. After then you will see that a window will appear on your screen. If you want to get more accurate result then click “yes” in the window.
5. This will highlight all the invalid content within the red oval circle.
But if you copy the format into B2, the cell is actual an ordinary cell. Therefore even if the content is invalid, it will not be highlighted with a red oval. Hence you may pay more attention when paste values from other cells.