Specifying Response to Invalid Data Entry

How to Specify a Response to Invalid Data Entry in Microsoft Excel

In my last post, I showed how to make data entry easier, or limit entries to certain items that you define and ensure the correct data is entered in Excel worksheet. Additionally, you can specify how you want Microsoft Office Excel to respond when invalid data is entered. Take these the following steps to create Response to Invalid Data Entry in Excel:

• First, you have to create a list of valid entries for the drop-down list, so type the entries in a single column or row without blank cell as can be seen in this picture below.

• You can sort the data in the order that you want it to appear in the drop-down list.
• If you want to use another worksheet, type the list on that worksheet, and then define a name for the list. (Click Defining Name Reference to read my post on that).
  1. Select the cell where you want the drop-down list.
  2. Click on the Data tab, in the Data Tools group, click Data Validation. The Data Validation dialog box will be displayed.
  3. Click the Settings tab.
  4. In the Allow box, click List.
  5. To specify the location of the list of valid entries, do one of the following:
  • If the list is in the current worksheet, enter a reference to your list in the Source box. For example, enter =Depts or =$C$5:$C$25.
  • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
  6. Make sure that the In-cell drop-down check box is selected.
  7. 2) Click the Error Alert tab, and make sure that the "Show error alert after invalid data" is entered check box is selected.
  8. 3) Select one of the following options for the Style box:
  • To display an information message that does not prevent entry of invalid data, click Information.
  • To display a warning message that does not prevent entry of invalid data, click Warning.
  • To prevent entry of invalid data, click Stop.
  9. Type the title and text for the message (up to 225 characters).
  10. Click Ok.