Set up reliable data validation in Excel

/
You can use data validation to restrict the type of information allowed in a given cell (or cells). You can also specify a range of valid data for numerical, time or date values, and even text length. In addition, you can use data validation to limit data to a predefined list of acceptable items.

To set up data validation:



  1. Select the cells you wish to affect.

    2. Cells you wish to affect Cells you wish to affect
  2. In the Data menu, click Validation.

    3. The Data Validation dialog box appears.

    Data Validation dialog box Data Validation dialog box
  3. On the Settings tab, in the Allow box, click List.

  4. In the Source box, type the values, separated by commas, you want to allow.

    5. For example, try typing: blue, green, orange.
  5. Click OK.

    6. When you click on a cell, the drop-down arrow is available with the values you specified.

    Data Validation dropdown list Dropdown list

Note: In the Source box, you can use a formula or reference to a range of cells that contain your list values rather than explicitly typing those values in.

However, if you want to refer to a range of cells on a different worksheet in the same workbook, you need to define a named range, then refer to the named range in the Source box, =named_range.