Using Data Validation to Prevent Invalid Data Entry in Excel

01
of 01

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

As shown in the image above, this example will:

  1. set data validation options that allow only whole numbers with a value of less than 5 to be entered into cell D1;
  2. 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
Format
mla apa chicago
Your Citation
French, Ted. "Using Data Validation to Prevent Invalid Data Entry in Excel." ThoughtCo, Jul. 29, 2017, thoughtco.com/data-validation-prevent-invalid-data-entry-3123440. French, Ted. (2017, July 29). Using Data Validation to Prevent Invalid Data Entry in Excel. Retrieved from https://www.thoughtco.com/data-validation-prevent-invalid-data-entry-3123440 French, Ted. "Using Data Validation to Prevent Invalid Data Entry in Excel." ThoughtCo. https://www.thoughtco.com/data-validation-prevent-invalid-data-entry-3123440 (accessed January 23, 2018).