Designate valid cell entries

Microsoft Office Excel 2003

previous page next page

Show All Show All

Designate valid cell entries

  1. Select the cell to validate.
  2. On the Data menu, click Validation, and then click the Settings tab.
  3. Specify the type of validation you want:

    ShowAllow values from a list

    1. In the Allow box, click List.
    2. Click the Source box and then do one of the following:
      • To define the list locally, type the list values separated by commas.
      • To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range.
      • To use cell references, select the cells on the worksheet and then press ENTER.

        Note  Cell references have to be to cells on the same worksheet. If you have to refer to a different worksheet or a different workbook, use a defined name and make sure the workbook is already open.

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

    ShowAllow numbers within limits

    1. In the Allow box, click Whole Number or Decimal.

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

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

    ShowAllow dates or times within a timeframe

    1. In the Allow box, click Date or Time.

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

    3. Enter the start, end, or specific date or time to allow.

    ShowAllow text of a specified length

    1. In the Allow box, click Text Length.

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

    3. Enter the minimum, maximum, or specific length for the text.

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

    1. In the Allow box, click the type of data you want.

    2. In the Data box, click the type of restriction 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, click Decimal for Allow, click less than or equal to for Data, and in the Maximum box, click the cell that contains the budget amount.

    ShowUse a formula to calculate what's allowed

    1. In the Allow box, click Custom.

    2. In the Formula box, enter a formula that calculates a logical value (TRUE for valid entries or FALSE for invalid). For example, to allow the value in the cell for the picnic account only if nothing is budgeted for the discretionary account (cell D6) and the total budget (D20) is also less than the $40,000 allocated, you could enter =AND(D6=0,D20<40000) for the custom formula.

  4. Specify whether the cell can be left blank:
    • If you want to allow blank (null) values, select the Ignore blank check box.
    • If you want to prevent entry of blank (null) values, 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.

  5. To display an optional input message when the cell is clicked, click the Input Message tab, make sure the Show input message when cell is selected check box is selected, and fill in the title and text for the message.

  6. Specify how you want Microsoft Excel to respond when invalid data is entered:

    ShowHow?

    1. Click the Error Alert tab, and make sure 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, click Information.

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

      To prevent entry of invalid data, click 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."

Note  Applying data validation to a cell does not format the cell.

previous page start next page