Computer and Business Skills training

Sign Up
Home / Excel / Data Validation

Using Data Validation in Excel


To protect against incorrect data entry, you can use data validation to restrict the type of data that may be entered into a cell (see Figure 1 below). You can specify a list of the valid entries or limit the number of characters in an entry. To further assist in accurate data entry, you can have a data input message appear that informs you of the type of data to be entered in a particular cell (see Figure 2 below). You can also have an invalid data error message appear when incorrect data is entered (see Figure 3 below).


For more details on our face-to-face Excel training in Chicago and Los Angeles call us on 888.815.0604. All classes are hands-on and come with a free repeat.





Data Validation Dialog Box

User generated message


Date Entry Error Message Box



Method to set data validation

To apply data entry restrictions:

  1. Select the desired cells.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, select the Settings tab.
  4. In the Validation criteria area, in the Allow drop-down list box, select the desired data type.
  5. In the drop-down list box(es) that appear, make the necessary selections.
  6. Choose OK.

To create a data input message:

  1. Select the cells to which you have applied data validation.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, select the Input Message tab.
  4. On the Input Message page, make sure the Show input message when cell is selected check box is selected.
  5. In the Title text box, type a title.
  6. In the Input message text box, type an input message.

To create an invalid data error message:

  1. Select the cells to which you have applied data validation.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, select the Error Alert tab.
  4. On the Error Alert page, make sure the Show error alert after invalid data is entered check box is selected.
  5. From the Style drop-down list, select the desired style.
  6. In the Title text box, type a title.
  7. In the Error message text box, type an error message.

Also see Protecting your worksheet.


Onsite Excel training

Through our network of skilled trainers we deliver onsite Microsoft Excel training right across the country. View our Excel training class testimonials or obtain a quote for onsite Excel training.


Excel courses

Why train with us?Need Group training quotation?Fresh Starbucks brewed every day Book two levels of Excel training and save $100FREE Class Repeat with each courseBook 3 Level of Excel Training and save $150