data validation messageIn many worksheets that you create, users will enter data to get the desired calculations and results. Ensuring valid data entry is an important task. You may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Providing immediate help to instruct users and clear messages when invalid data is entered is also essential to make the data entry experience go smoothly.

Once you decide what validation you want to use on a worksheet, you can set up the validation by doing the following:

  1. Select one or more cells to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.

    excel ribbon image

    The Data Validation dialog box is displayed.

  3. Click the Settings tab.
  4. To specify the type of validation that you want, do one of the following:

    Allow values from a list

    1. In the Allow box, select List.
    2. 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, High.

      You can also create a list of values from a range of cells. For more information, see .

      Note The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

    3. Make sure that the In-cell dropdown check box is selected.

    Allow a whole number within limits

    1. In the Allow box, select Whole Number.
    2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
    3. 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.

    Allow a decimal number within limits

    1. In the Allow box, select Decimal.
    2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
    3. 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 allow a user to 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 Sheet tab.

    Allow a date within a timeframe

    1. In the Allow box, select Date.
    2. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
    3. 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.

    Allow a time within a timeframe

    1. In the Allow box, select Time.
    2. 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.
    3. Enter the start, end, or specific time to allow. You can also enter a formula that returns a time value.

      For example, to set a time frame for serving breakfast between the time when the restaurant opens in cell H1 and 5 hours after the restaurant opens, select between in the Data box, enter =H1 in the Minimum box, and then enter =H1+"5:00" in the Maximum box.

    Allow text of a specified length

    1. In the Allow box, select Text Length.
    2. 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.
    3. 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.

    Calculate what's allowed based on the content of another cell

    1. 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's 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.

    Use a formula to calculate what's allowed

    1. In the Allow box, select Custom.
    2. In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example:
      To make sure that Enter this formula
      The cell for the picnic account (B1) can only be updated if nothing is budgeted for the discretionary account (D1) and the total budget (D2) is less than the $40,000 allocated. =AND(D1=0,D2<40000)
      The cell containing a product description (B2) only contains text. =ISTEXT(B2)
      For the cell containing a projected advertising budget (B3), the subtotal for subcontractors and services (E1) must be less than or equal to $800, and the total budget amount (E2) must also be less than or equal to $97,000. =AND(E1<=800,E2<=97000)
      The cell containing an employee age (B4) is always greater than the number of full years of employment (F1) plus 18 (the minimum age of employment). =IF(B4>F1+18,TRUE,FALSE)
      All the data in the cell range A1:A20 contains unique values. =COUNTIF($A$1:$A$20,A1)=1

      You must enter the formula in the data validation for cell A1, and then fill the cells A2 though A20 so that the data validation for each cell in the range has a similar formula, but the second argument to the COUNTIF function will match the current cell.

      The cell containing a product code name (B5) always begins with the standard prefix of ID- and is at least 10 characters in length. =AND(LEFT(B5, 3) ="ID-",LEN(B5) > 9)
  5. 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 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.

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

    How?

    1. Click the Input Message tab.
    2. Make sure the Show input message when cell is selected check box is selected.
    3. Fill in the title and text for the message.
  7. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

    How?

    1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
    2. 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.
    3. 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 defaults to "Microsoft Excel" and the message to: "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's 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.

    Tip If you make changes to the validation in one cell, you can automatically apply your changes to all other cells that have the same settings.

Notes: