How to give the error messages in Data Validation

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 enter 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

Untitled

  • 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 value.

image 2

 

  • Select Error Alert tab & enter the Title & Error Message as shown in below screenshot:

image 3

 

Now, when you enter value other than between 1 & 10 then the following dialog box will appear.

 

image 4

 

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

image 5

 

  • After entering the value like 11; the following Warning dialog box will appear

image 6

 

  • 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