Excel Data Verification

• Data Validation Example
• How to create a data validation rule
• Input message
• Error alert
• Result of data validation

Use data validation in Excel to make sure that users enter only certain values ​​into cells.

Data Validation Example

In this example, we’ll restrict users to only enter an integer between 0 and 10.

How to create a data validation rule

To create a data validation rule, follow our instructions:

1. Highlight a cell С2.
2. On the Advanced tab Data (Data) click the button Data validation (Data Validation).

   

   On the Advanced tab Parameters (Settings) of the Data Validation dialog box, do the following:

   • From drop down list Data type (Allow) select Integer (Whole number).
   • From drop down list Value (Data) select Between (Between).
   • Enter the minimum and maximum values.

     

Input message

Input messages appear when the user selects a cell. They tell him what to enter. Go to the tab Input message (Input Message) and do the following:

1. Check the box next to Show tooltip if cell is current (Show input message when cell is selected).
2. Enter a title.
3. Enter the message itself.

   

Error message

If users ignore the prompt and enter an invalid number, you can show them an error notification. Go to the tab Error message (Error Alert) and do the following:

1. Check the box next to the option Display an error message (Show error alert after invalid data is entered).
2. Enter a title.
3. Enter an error message.

   

4. Press OK.

Result of data validation

1. Highlight a cell С2.

   

2. Try entering a number greater than 10.

   Result:

   

Note: To remove data validation from a cell, select it and on the tab Data (Data) click the button Data validation (Data Validation). Then click on clear all (Clear All). To quickly select all cells with data validation, use the tool Selecting a group of cells (Go To Special).