How to Use Data Validation in Excel

A stop sign at the front of a parking garage entrance

Ingo Jezierski/EyeEm/Getty Images

by Ted French
A spreadsheet expert who is a master at Excel and Google Sheets.
Updated February 25, 2019

You can use Excel's data validation options to control the type and value of data entered into specific cells in a worksheet. The levels of control you can apply to cells are:

  • Restricting the format and range of data that users can enter into a cell.
  • Displaying a prompt message when a cell containing data validation restrictions is clicked on but with no data restrictions on the cell itself.
  • Using a formula located in a different location to determine if data entered into a cell is valid.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365. 

Data Validation in Excel

In addition to placing restrictions on the type of data allowed in a cell, you can also create an Error Alert message explaining the restrictions when someone inputs invalid data.

There are three types of error alerts:

  • Stop — prevents the entry of invalid data
  • Warning — warns that invalid data is in a cell with an option to override the restrictions
  • Information — informs users that invalid data is in a cell but does not prevent its entry

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 used:

  • Entering data via the fill handle even if it 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

Preventing Invalid Data Entry

In this example, we will apply data validation to cell D1 that allows only whole numbers with a value of less than 5. If invalid data is entered into the cell, a Stop error alert will display.

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

First, you set up the cell restrictions, then you can specify the type of alert and add explanatory text. If you don't edit the alert tab, the default is a blank Stop error.

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

  2. Click the Data tab.

    A screenshot of Data Validation option selected in Excel's ribbon.

  3. Click the Data Validation icon in the Data Tools section of the ribbon to open the dialog box.

    A screenshot of Excel's Data Validation dialog box showing the Settings tab.

  4. Click 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.

    A screenshot of Excel's Data Validation dialog box showing the Error Alert tab.

  8. Click the Error Alert tab.

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

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

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

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

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

Testing the Data Validation

Finally, you should 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.

    A screenshot of Excel's data validation error message.

  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.