Custom Error Messages in Data Validation in Microsoft Excel 2010

Consider while creating a pre-set field for other users which will be helpful when entering the value that you have already set where you do not want users to enter the information on their own.

With the use of Data Validation feature in Excel, you can create your own custom error message when the user enters the information which is not predefined.

 

Let us take an example:

 

If we want user to enter number between 1 & 10 then the user will not be able to enter any other number like -1, 0, 11 & so on.

To prevent the user from entering numbers other than between 1 & 10, we will use Data Validation Error Alert.

 

Select the cell in which you want Data Validation to be applied.

Click on Data tab

Under Data Tools group, select Data Validation
 
img1
 
Or use ALT + D + L shortcut keys for Data Validation

In Settings group, click Allow drop down

Select Whole Number, select between in Data drop down

Enter minimum & maximum values
 
img2
 
Select Error Alert tab & enter the Title & Error Message as shown in below screenshot:
 
img3
 
Now, when you enter value other than between 1 & 10 then, the following dialog box will appear.
 
img4
 
In case you want user to be able to enter the value skipping the error message

In Error Alert tab; select Style as Warning rather than Stop
 
img5
 
After entering the value like 11; the following Warning dialog box will appear
 
img6
 
If you want to continue, you can click on Yes button to accept your input value.

If you do not want to continue with the input value then, you can click on No
 
In this way, you can show the custom error messages that you want to show.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube