The tutorial explains how to do Data Validation in Excel: create a validation rule for numbers, dates or text values, make data validation lists, copy data validation to other cells, find invalid entries, fix and remove data validation. When setting up a workbook for your users, you may often want to control information input into specific cells to make sure all data entries are accurate and consistent. Among other things, you may want to allow only particular data type such as numbers or dates in a cell, or limit numbers to a certain range and text to a given length. You may even want to provide a predefined list of acceptable entries to eliminate possible mistakes. Excel Data Validation allows you to do all these things in all versions of Microsoft Excel 2016, 20013, 2010 and lower. What is data validation in Excel? Excel Data Validation is a feature that restricts (validates) user input to a worksheet.
This tutorial quickly teaches you how to add Radio Buttons (sometimes called Option Buttons) in Microsoft. Create a List in Excel 2008 I was surprised to find a List Wizard in Excel 2008 (Mac) because Excel 2007 (Windows) has Tables, and had already been out for nearly a year when Excel 2008 was released.
Technically, you create a validation rule that controls what kind of data can be entered into a certain cell. Here are just a few examples of what Excel's data validation can do:. Allow only numeric or text values in a cell. Allow only numbers within a specified range. Allow data entries of a specific.
Restrict dates and times outside a given time frame. Restrict entries to a selection from a drop-down list.
Validate an entry based on another cell. Show an input message when the user selects a cell. Show a warning message when incorrect data has been entered. Find incorrect entries in validated cells. For instance, you can set up a rule that limits data entry to 4-digit numbers between 1000 and 9999. If the user types something different, Excel will show an error alert explaining what they have done wrong: How to do data validation in Excel To add data validation in Excel, perform the following steps.
Open the Data Validation dialog box Select one or more cells to validate, go to the Data tab Data Tools group, and click the Data Validation button. You can also open the Data Validation dialog box by pressing Alt D L, with each key pressed separately.
Create an Excel validation rule On the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:. Values - type numbers in the criteria boxes like shown in the screenshot below. Cell references - make a rule based on a value or formula in. Formulas - allow to express more complex conditions like in.
As an example, let's make a rule that restricts users to entering a whole number between 1000 and 9999: With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add an input message or/and error alert. Add an input message (optional) If you want to display a message that explains to the user what data is allowed in a given cell, open the Input Message tab and do the following:. Make sure the Show input message when cell is selected box is checked. Enter the title and text of your message into the corresponding fields.
Click OK to close the dialog window. As soon as the user selects the validated cell, the following message will show up: 4. Display an error alert (optional) In addition to the input message, you can show one of the following error alerts when invalid data is entered in a cell.
Alert type Description Stop (default) The strictest alert type that prevents users from entering invalid data. You click Retry to type a different value or Cancel to remove the entry.
Warning Warns users that the data is invalid, but does not prevent entering it. You click Yes to input the invalid entry, No to edit it, or Cancel to remove the entry. Information The most permissive alert type that only informs users about an invalid data entry. You click OK to enter the invalid value or Cancel to remove it from the cell.
To configure a custom error message, go to the Error Alert tab and define the following parameters:. Check the Show error alert after invalid data is entered box (usually selected by default).
In the Style box, select the desired alert type. Enter the title and text of the error message into the corresponding boxes.
And now, if the user enters invalid data, Excel will display a special alert explaining the error (like shown in the beginning of this tutorial). If you do not type your own message, the default Stop alert with the following text will show up: This value does not match the data validation restrictions defined for this cell. Excel data validation examples When adding a data validation rule in Excel, you can choose one of the predefined settings or specify custom criteria based on your own validation formula. Below we will discuss each of the built-in options, and next week we will have a closer look at in a separate tutorial. As you already know, the validation criteria are defined on the Settings tab of the Data Validation dialog box ( Data tab Data Validation). Whole numbers and decimals To restrict data entry to a whole number or decimal, select the corresponding item in the Allow box.
And then, choose one of the following criteria in the Data box:. Equal to or not equal to the specified number. Greater than or less than the specified number.
Between the two numbers or not between to exclude that range of numbers For example, this is how you create an Excel validation rule that allows any whole number greater than 0: Date and time validation in Excel To validate dates, select Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more. Similarly, to validate times, select Time in the Allow box, and then define the required criteria. For example, to allow only dates between Start date in B1 and End date in B2, apply this Excel date validation rule: To validate entries based on today's data and current time, make your own data validation formulas as shown in these examples:.