Data Validation

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 to specify the value in Minimum.
    For practice, enter the value '36' in Minimum.
  6. Enter the value or click the Select Area 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 “Hwp, Cell, Show” in Source.
  6. Click OK.
  7. Click the cell with data validation is set.
  8. Click the Expand icon and then select “Cell” 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 Error 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 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 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 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 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

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

Reset Button

Delete the configured validation settings.

See Also

Data Validation, Mark Invalid Data, Clear Validation Marks, Fill, Filter, Formula

www.hancom.com