1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Prevent Invalid Data Entry in Excel

By

Using Data Validation to Prevent Invalid Data Entry
Prevent Invalid Data Entry in Excel

Prevent Invalid Data Entry in Excel

© Ted French

Using Data Validation to Prevent Invalid Data Entry

Excel's data validation options can be used to control the type and value of data entered into specific cells in a worksheet.

The various levels of control that can be applied involve:

  • displaying a prompt message when a cell containing data validation restrictions is clicked on but with no data restrictions on the cell itself
  • restricting the type and range of data that can be entered into a cell
  • using a formula located in a different location to determine if data entered into a cell is valid

This tutorial covers the second option of restricting the type and range of data that can be entered into a cell in an Excel worksheet.

Using an Error Alert Message

In addition to placing restrictions on the data that can be entered into a cell, an Error Alert message can be displayed explaining the restrictions when invalid data is entered.

There are three types of the error alert that can be displayed and the type chosen affect how strictly the restrictions are enforced:

  • Stop - prevents the entry of invalid data
  • Warning - warns that invalid data has been entered into a cell with an option to override the restrictions
  • Information - informs users that invalid data has been entered into a cell but does not prevent its entry

Error Alert Exceptions

Error Alerts are displayed only when data is typed into a cell. They do not appear if:

  • invalid data is copied into a cell or entered using the fill handle
  • a formula in the cell calculates an invalid result
  • a macro enters invalid data into a cell

Example: Preventing Invalid Data Entry

Refer to the image above to view the results of this example.

This example will set data validation options to allow only whole numbers with a value of less than 5 to be entered into cell D1.

If invalid data is entered into the cell, a Stop error alert will be displayed.

Opening the data validation dialog box

All data validation options in Excel are set using the data validation dialog box.

  1. Click on cell D1 - the location where data validation will be applied

  2. Click on the Data tab

  3. Choose Data Validation from the ribbon to open the drop down list

  4. Click on Data Validation in the list to open the data validation dialog box

The Settings Tab

These steps restrict the type of data that can be entered into cell D1 to whole numbers with a value of less than five.

  1. Click on Settings tab in the dialog box

  2. Under the Allow: option choose Whole Number from the list

  3. Under the Data: option choose less than from the list

  4. In the Maximum: line type the number 5

The Error Alert Tab

These steps specify that type of error alert to be displayed and the message it contains.

  1. Click on Error Alert tab in the dialog box

  2. Make sure the "Show error alert after invalid data is entered" box is checked

  3. Under the Style: option choose Stop from the list

  4. In the Title: line type: Invalid Data Value

  5. In the Error message: line type: Only numbers with a value of less than 5 are allowed in this cell

  6. Click OK to close the dialog box and return to the worksheet

Testing the data validation settings

  1. Click on cell D1

  2. Type the number 9 in cell D1

  3. Press the Enter key on the keyboard

  4. The Stop error alert message box should appear on screen since this number is greater than the maximum value set in the dialog box

  5. Click on the Retry button on the error alert message box

  6. Type the number 2 in cell D1

  7. Press the Enter key on the keyboard

  8. The data should be accepted in the cell since it is less than the maximum value set in the dialog box

Related Tutorial

©2014 About.com. All rights reserved.