Sunday, December 11, 2016

Apply data validation to cells

Apply data validation to cells

You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Check out this video by Doug from Office as he gives a quick data validation overview.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Download our examples

You can download an example workbook with all of the data validation examples you'll find in this article. You can follow along, or create your own data validation scenarios.

Download Excel data validation examples

Add data validation to a cell or a range

Note: The first three steps in this section are for adding any type of data validation. Steps 4-8 are specifically for creating a drop-down List.

  1. Select one or more cells to validate.

  2. On the Data tab, in the Data Tools group, click Data Validation.

    Data Validation is located in Data tab, Data Tools group

  3. On the Settings tab, in the Allow box, select List.

    Settings tab in Data Validation dialog box

  4. In the Source box, type your list values, separated by commas. For example:

    1. To limit an answer to two choices ("Do you have children?" for example), type Yes,No.

    2. To limit a vendor's quality reputation to three ratings, type Low,Average,High.

      Note: These steps are generally only recommended for list items that aren't likely to ever change. If you have a list that could change, or if you need to add or remove items over time, then you're better off following the Best Practice step below.

      Best Practice: You can also create list entries by referring to a range of cells elsewhere in the workbook. The most efficient way is to create your list, then format it as an Excel Table (from the Home tab select Styles > Format as Table > choose the Table Style that works best for you). Next, select the table's Data Body Range, which is the portion of the table that has just your list, not the table Header (Department in this case), and give it a meaningful name in the Name Box above column A.

      Enter a meaningful name for the list in the name box

    Now, instead of typing your list values in the data validation Source box, you add the name that you just defined, preceded by an Equal (=) sign.

    Precede the table name with an = sign

    The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically.

    Note: It's best to put your lists on a separate worksheet (hidden if necessary) so that no one can edit them.

  5. Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell.

    In-cell dropdown appearing next to the cell

  6. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

    Note: If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

  7. Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Notes: 

  • After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries.

  • If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

  • Remove data validation - Select the cell or cells that contain the validation you want to delete, then goto Data > Data Validation and in the data validation dialog press the ClearAll button, then OK.

The following table lists other types of data validation and shows you ways to add it to your worksheets.

To do this:

Follow these steps:

Restrict data entry to whole numbers within limits.

  1. Follow steps 1-3 in Add data validation to a cell or a range above.

  2. From the Allow list, select Whole number.

  3. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.

  4. Enter the minimum, maximum, or specific value to allow.

    Validation criteria dialog box

    You can also enter a formula that returns a number value.

    For example, say you're validating data in cell F1. To set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.

Restrict data entry to a decimal number within limits.

  1. Follow steps 1-3 in Add data validation to a cell or a range above.

  2. In the Allow box, select Decimal.

  3. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.

  4. Enter the minimum, maximum, or specific value to allow.

    You can also enter a formula that returns a number value. For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.

    Note:  To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style Button image in the Number group on the Home tab.

Restrict data entry to a date within range of dates.

  1. Follow steps 1-3 in Add data validation to a cell or a range above.

  2. In the Allow box, select Date.

  3. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.

  4. Enter the start, end, or specific date to allow.

    You can also enter a formula that returns a date. For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Start date box, and enter =TODAY()+3 in the End date box.

    Validation criteria settings to restrict date entry to a specific time frame

Restrict data entry to a time within a time frame.

  1. Follow steps 1-3 in Add data validation to a cell or a range above.

  2. In the Allow box, select Time.

  3. In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.

  4. Enter the start, end, or specific time to allow. If you want to enter specific times, use the hh:mm time format.

    For example, say you have cell E2 set up with a start time (8:00 AM), and cell F2 with an end time (5:00 PM), and you want to limit meeting times between those times then select between in the Data box, enter =E2 in the Start time box, and then enter =F2 in the End time box.

    Validation settings to restrict time entry within a time frame

Restrict data entry to text of a specified length.

  1. Follow steps 1-3 in Add data validation to a cell or a range above.

  2. In the Allow box, select Text Length.

  3. In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.

  4. In this case we want to limit entry to 25 characters, so select less than or equal to in the Data box and enter 25 in the Maximum box.

    Data Validation example with limited text length

Calculate what is allowed based on the content of another cell.

  1. Follow steps 1-3 in Add data validation to a cell or a range above. In the Allow box, select the type of data that you want.

  2. In the Data box, select the type of restriction that you want.

  3. In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.

    For example, to allow entries for an account only if the result won't go over the budget in cell E1, select Allow >Whole number, Data, less than or equal to, and Maximum >= =E1.

    Validation settings to calculate based on another cell content

Note: The following examples use the Custom option where you write formulas to set your conditions. You don't need to worry about whatever the Data box shows, as that's disabled with the Custom option.

To make sure that

Enter this formula

The cell that contains a product ID (C2) always begins with the standard prefix of "ID-" and is at least 10 (greater than 9) characters long.

=AND(LEFT(C2, 3) ="ID-",LEN(C2) > 9)

Example 6: Formulas in data validation

The cell that contains a product name (D2) only contains text.

=ISTEXT(D2)

Example 2: Formulas in data validation

The cell that contains someone's birthday (B6) has to be greater than the number of years set in cell B4.

=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

Data validation example to restrict an entry to a minimum age

All the data in the cell range A2:A10 contains unique values.

=COUNTIF($A$2:$A$10,A2)=1

Example 4: Formulas in data validation

Note: You must enter the data validation formula for cell A2 first, then copy A2 to A3:A10 so that the second argument to the COUNTIF will match the current cell. That is the A2)=1 portion will change to A3)=1, A4)=1 and so on.

For more information

Ensure that an e-mail address entry in cell B4 contains the @ symbol.

=ISUMBER(FIND("@",B4)

Data validation example ensuring an e-mail address contains the @ symbol

  • Why is the Data Validation command not enabled on the ribbon? The command may be unavailable because:

    • A Microsoft Excel table might be linked to a SharePoint site You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.

    • You might currently be entering data The Data Validation command is not enabled on the Data tab while you are entering data in a cell. To finish entering data, press ENTER or ESC.

    • The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see Protect a workbook.

  • Can I change the font size? No, the font size is fixed. The only way to change the display size is to adjust your screen zoom in the lower right-hand corner of the Excel window. You can use an ActiveX Combo Box however. See Add a list box or combo box to a worksheet.

  • Is there any way to make my data validation auto-fill or auto-select as I type? No, but if you use an ActiveX Combo Box you do have that functionality.

  • Can I make multiple selections in a data validation list? Not unless you use an ActiveX Combo or List Box.

  • Can I select an item in a data validation list and have it populate another list? Yes! This is called Dependent Data Validation. For more information, see Create Dependent Drop Down Lists.

  • How can I remove all data validation on a worksheet? You can use the Go To > Special dialog. On the Home tab > Editing > Find & Select (or press F5 or Ctrl+G on the keyboard), then Special > Data validation and select either All (to find all cells with data validation) or Same (to find cells that match specific data validation settings).

    Go To Special dialog box

    Next call up the data validation dialog (Data tab > Data Validation), press the Clear All button, then OK.

  • Can I force someone to make an entry in a cell(s) with data validation? No, but you can use VBA (Visual Basic for Applications) to check whether someone has made an entry in certain conditions, like before they Save or Close the Workbook. If they haven't made a selection, you can cancel the event and not let them proceed until a selection has been made.

  • How can I color cells based on a data validation list selection? You can use Conditional Formatting. In this case you'd want to use the Format only cells that contain option.

    Format only cells that contain option

  • How do I validate an e-mail address? You could use the Custom > Formula method and check if the @ symbol exists in the entry. In this case, the formula used is =ISNUMBER(FIND("@",D2)). The FIND function looks for the @ symbol, and if found it returns its numeric position in the text string and allows the entry. If it's not found, then FIND returns an error and prevents the entry.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

More on data validation

Video: Create and manage drop-down lists

Add or remove items from a drop-down list

Remove a drop-down list

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)