Software & Apps MS Office How to Use Data Validation in Excel Limit what goes in worksheet cells by Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated on February 24, 2020 Tweet Share Email Ingo Jezierski / Getty Images MS Office Excel Word Powerpoint Outlook You can use Excel's data validation options to control the type and value of data entered into specific cells in a worksheet. These instructions apply to Excel for Microsoft 365, Excel 2019, 2016, 2013, and Excel 2010. 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 limitations when someone inputs invalid data. 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. 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 Prevent Invalid Data Entry In this example, we 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 displays. Select cell D1, which is the location where data validation will be applied. Select the Data tab. Choose the Data Validation icon in the Data Tools section of the ribbon to open the dialog box. Select the Settings tab. Under the Allow option choose Whole Number from the list. Under the Data option choose less than from the list. In the Maximum line type the number 5. Select the Error Alert tab. Make sure the Show error alert after invalid data is entered box is checked. Under the Style option choose Stop from the list. In the Title line, type Invalid Data Value. In the Error message line type Only numbers with a value of less than 5 are allowed in this cell. Select OK to close the dialog box and return to the worksheet. Test the Data Validation Finally, you should ensure that the data validation for cell D1 is functioning as it should — to restrict the entry of numbers with a value greater than 5: Select cell D1. Type the number 9 in cell D1. Press the Enter key on the keyboard. The Stop error alert message box appears since this number is greater than the maximum value set in the dialog box. Select the Retry button on the error alert message box. Type the number 2 in cell D1. Press the Enter key on the keyboard. The data should be accepted in the cell since it is less than the maximum value set in the dialog box. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit