How to Use Data Validation in Excel

Stop Sign Against Building
Ingo Jezierski / EyeEm / Getty Images

 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 to cells 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.

of 03

Data Validation in Excel

Screenshot of Excel showing the Data validation option

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 error alerts that can be displayed and the type chosen affects 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 one of the following data entry methods is utilized:

  • Entering data via the fill handle that adds invalid data
  • Entering data via a formula that calculates an invalid result
  • Entering data via a macro that enters invalid data into a cell
of 03

Preventing Invalid Data Entry

Screenshot of Excel showing data validation settings

Four this tutorial, we will create a data validation example that meets the following criteria:

  1. Sets 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 dialog box.
  4. Click on the Settings tab.
  5. Under the Allow: option choose Whole Number from the list.
  6. Under the Data: option choose less than from the list.
  7. In the Maximum: line type the number 5.
Screenshot of Excel showing the error alert tab

The Error Alert Tab

These steps specify that type of error alert to be displayed and the message it contains when a user attempts to add invalid data to the cell.

  1. Click on Error Alert tab.
  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
of 03

Testing the Data Validation

Screenshot of Excel showing the testing of invalid data

Follow these steps to ensure that the data validation for cell D1 is functioning as it should — to restrict entry of numbers with a value greater than 5:

  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.