Skip to main content

Data Validation

Updated

You can create and apply validation rules to minimize the error in data, entered in a worksheet. A validation rules allows data to be entered in a cell, only when the data satisfies specified criteria.

To specifcy data validation

  1. Click Data-Data Validation-Data Validation.
  2. In each tab of the Data Validation dialog box, set validation conditions, messages, error messages. etc.
  3. Click OK.

 

Ensure the validity of data within a document by setting conditions to enter only certain range values ​​or items, or by specifying error messages for invalid data.

Allow Values in Particular Range

To set the data validation condition so that only a specific range of values can be entered, do the following:

  1. Select the cell or range for validation.
  2. Click Data-Data Validation-Data Validation.
  3. In the Data Validation dialog box, select Integer in the Settings tab.
  4. Select Between from Restriction Method.
  5. Enter the value or click the Select Area icon_specify_range.png to specify the value in Minimum.
    For practice, enter the value '36' in Minimum.
  6. Enter the value or click the Select Area icon_specify_range.png to specify the value in Maximum.
    For practice, enter the value '65' in Maximum.
  7. Click OK.
  8. Check the Data Validation function by entering a value that is less than the minimum or larger than the maximum in the validated range.

Allow Specific List Only

To set the data validation condition so that only a specific list can be entered, do the following:

  1. Select the cell or range for validation.
  2. Click Data-Data Validation-Data Validation.
  3. In the Data Validation dialog box, select List in the Settings tab.
  4. Make sure the Ignore Blank and Display List Box check boxes selected.
  5. Enter “Word, Spreadsheet, Presentation” in Source.
  6. Click OK.
  7. Click the cell with data validation is set.
  8. Click the Expand icon cell_filter_icon.png and then select “Spreadsheet” from the list.

Create Data Validation Description

To specify a description to appear in the selected cell, do the following:

  1. Select the cell or range for validation.
  2. Click Data-Data Validation-Data Validation.
  3. In the Data Validation dialog box, select the Show Comment when a Cell is Selected check box in the Description tab.
  4. Enter text in Title and Comment.
    If the input is restricted to an integer from 36 through 65, enter “Limited to Integer” in Title and “Enter an integer from 36 through 65.” in Comment.
  5. Click OK.
  6. Select a cell in the validated range to make sure the description appear correctly.

Create Data Validation Error Message

To specify a validation error message, do the following:

  1. Select the cell or range for validation.
  2. Click Data-Data Validation-Data Validation.
  3. In the Data Validation dialog box, select the Show error message when invalid data is entered check box in the Error Message tab.
  4. Select Stop from Styles and enter “Stop Entry” in Title and “Limit exceeded. Stop entering data.” in Message.
  5. Click OK.
  6. Check the error message by entering a value that is less than the minimum or larger than the maximum in the validated range.

More Information

Data Validation-Settings Dialog Box

Restriction

Category Restriction
All values Limit a number to a set value
Integer Limit a number to a set value
Real number Limit a number to a set value
List Limit to pre-defined items
Date Limit a date to a set value
Time Limit a time to a set value
Text length Limit text length to a set value
Custom Limit to a custom value

Ignore Blank

Set to ignore blanks between items separated by comma for the List restriction.

Restriction Method

Enter the value to be restricted, and then select one of the following restriction methods.

Minimum/Maximum

Specify the values of integers, real numbers, or text length. Enter the cell address in which the minimum and maximum values ​​of the limit target are entered or click the select area icon icon_specify_range.png to select the cell in which the value is entered.

Start Date/End Date

Specify the date. Enter the cell address in which the start date and end date were entered during the period of the limit date or click the select area icon icon_specify_range.png to select the cell in which the value was entered.

Start Time/End Time

Specify the time. Either directly enter the cell address in which the start time and end time are entered, or click the select area icon icon_specify_range.png to select the cell in which the value is entered.

Display List Box

Specify the list. When the cell is selected, a drop-down icon cell_filter_icon.png is displayed to expand and check the list.

Formula

Use formula to limit the allowed values. This appears when Custom is selected for restriction.

Reset Button

Delete the configured validation settings.

 

Data Validation-Description Dialog Box

Show Comment when a Cell is Selected

Display a description as a comment, when the specified cell is selected.

When a cell is selected, show this comment

  • Title

    Enter a title for the description. he title in the description will be displayed in bold.

  • Comment

    Enter description to be displayed. You can also enter the restrictions for the selected cell.

Reset Button

Delete the configured validation settings.

 

Data Validation-Error Message Dialog Box

Show error message when invalid data is entered

Display designated error message if invalid data is entered.

Error message to display when invalid data is entered

  • Styles

    Select the style of error message. Each style has a distinctive icon displayed in the message box.

    Category Restriction
    Stop Stop invalid data from being entered.
    Warning Allow invalid data entry, but displays a warning message.
    Information Allow invalid data entry, but displays a notice message.
  • Title

    Enter the title for the error message box.

  • Message

    Enter the message to be displayed in the error message box.

Reset Button

Delete the configured validation settings.

Related articles