What is DATA Validation and how …

Data validation is intended to provide certain well-defined guarantees for fitness, accuracy, and consistency for any of various kinds of user input into an application or automated system. Data validation rules can be defined and designed using any of various methodologies, and be deployed in any of various contexts.

Data validation rules may be defined, designed and deployed.

Add data validation to a cell or a range

NOTE: The first three steps in this section are for adding any type of data validation. Steps 4-8 are specifically for creating a drop-down List.

1. Select one or more cells to validate.

2. On the Data tab, in the Data Tools group, click Data Validation.

   

3. On the Settings tab, in the Allow box, select List.

   

4. In the Source box, type your list values, separated by commas. For example:

   1. To limit an answer to two choices ("Do you have children?" for example), type Yes,No.

   2. To limit a vendor's quality reputation to three ratings, type Low,Average,High.

      NOTE: These steps are generally only recommended for list items that aren’t likely to ever change. If you have a list that could change, or if you need to add or remove items over time, then you’re better off following the Best Practice step below.

      Best Practice: You can also create list entries by referring to a range of cells elsewhere in the workbook. The most efficient way is to create your list, then format it as an Excel Table (from the Home tab select Styles > Format as Table > choose the Table Style that works best for you). Next, select the table’s Data Body Range, which is the portion of the table that has just your list, not the table Header (Department in this case), and give it a meaningful name in the Name Box above column A.

      

   Now, instead of typing your list values in the data validation Source box, you add the name that you just defined, preceded by an Equal (=) sign.

   

   The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically.

   NOTE: It’s best to put your lists on a separate worksheet (hidden if necessary) so that no one can edit them.

5. Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell.

   

6. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

   NOTE: If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

7. Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.