Data Validation - Examples

Hello guys!!

We saw technical details about Data Validation in our earlier post. Now, let's continue with few examples where you can use this feature.

1)       Select one or more cells to validate

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

3)       In the Data Validation dialog box, click the Settings tab

4)       Select the appropriate option from ‘Allow’ drop down

Example 1: Restrict data entry to values in a drop-down list

                     i.            In the Allow box, select List

                    ii.            Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default)

For example:

·        To limit entry to a question, such as "Do you have children?” to two choices, type Yes, No

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

You can also create the list entries by referring to a range of cells elsewhere in the workbook

                  iii.            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

Example 2: Restrict data entry to a whole number within limits

         i.            In the Allow box, select Whole number

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

      iii.            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 minimum limit of deductions to two times the number of children in cell F1, select ‘greater than or equal to’ in the Data box and enter the formula, =2*F1, in the Minimum box.

Example 3: Restrict data entry to a decimal number within limits

         i.            In the Allow box, select Decimal.

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

      iii.            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 in the Number group on the Home tab.

Example 4: Restrict data entry to a date within a time frame

         i.            In the Allow box, select Date.

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

      iii.            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 Minimum box, and enter =TODAY()+3 in the Maximum box.

Example 5: Restrict data entry to a time within a time frame

         i.            In the Allow box, select Time.

        ii.            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’.

      iii.            Enter the start, end, or specific time to allow. You can also enter a formula that returns a time.

For example, to set a time frame for serving breakfast between the time when the restaurant opens (the value in cell H1) and five hours after that, select between in the Data box, enter =H1 in the Start time box, and then enter =H1+"5:00" in the End time box.

Example 6: Restrict data entry to text of a specified length

         i.            In the Allow box, select Text Length.

        ii.            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.

      iii.            Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.

For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

Example 7: Calculate what is allowed based on the content of another cell

         i.            In the Allow box, select the type of data that you want.

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

      iii.            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 E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.

Example 8: Use a formula to calculate what is allowed

         i.            In the Allow box, select Custom.

        ii.            In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example: 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 with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.

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

6)       Optionally, display an input message when the cell is clicked.

         i.            Click the Input Message tab.

        ii.            Make sure the Show input message when cell is selected check box is selected.

      iii.            Fill in the title and text for the message.

7)       Specify how you want Microsoft Office Excel to respond when invalid data is entered.

         i.            Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

Note:  If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.

        ii.            Select one of the following options for the Style box:

·        To display an information message that does not prevent entry of invalid data, select Information.

·        To display a warning message that does not prevent entry of invalid data, select Warning.

·        To prevent entry of invalid data, select Stop.

      iii.            Fill in the title and text for the message (up to 225 characters).

Note: If you don't enter a title or text, the title uses the default string "Microsoft Excel" and the message is (by default): "The value you entered is not valid. A user has restricted values that can be entered into this cell."

8)       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.

That's enough examples to get you going. Try it out for yourself. In the next post, we will see few points worth noting while using Data Validation. Bye until then!!