Data Validation.(Microsoft Excel)


Japanese version.

Setting a "Data Validation" for a cell can be accomplished, for example, by restricting input to numbers only, or by displaying a drop-down list of candidates from which the user can choose.

How to use

Set up

Select the cell you wish to set and choose "Data Validation" under "Data".


Any value

Initial state. No input restrictions.

Whole number

Restrict input to integers.

Under "Data," set the type of restriction.

  • between
  • not between
  • equal to
  • not equal to
  • greater than
  • less than
  • greater than or equal to
  • less than or equal to

In the example below, only 1 to 100 is allowed.

If "Ignore blank" is checked, no restriction will be imposed when the cell contents are blanked.

If unchecked, no whitespace is allowed.


Restrict to a small number.
In the example below, it is limited to 0.5 to 1.5.

Functions the same as "Whole number" except that a decimal number can be set.


Set the allowed values separated by commas.

Cell range can also be specified for "Source".


Restrict input to date. Can be restricted to time level.


Restrict input to time of day. With this setting, you cannot enter year, month, and date.

Text Length

Restrict the length of the input string.

Input Message

If set, a message is displayed when a cell is selected.

Error Alert

Sets the behavior when an out-of-bounds input value is entered.



This setting does not allow any input other than the restrictions.

If a value outside the limit is entered, the data must be corrected with "Retry" or restored to before entry with "Cancel".


Only a message will be displayed, and input outside of the limits will be allowed.

YesInput outside of the restrictions is allowed.
NoReturns to the cell entry state.
CancelReturns to the before input.

There is no difference from "Warning" other than the intensity of the notification and the dialog selection.

OKInput outside of the restrictions is allowed.
CancelReturns to the before input.

How to Release

Click "Clear All" from the Settings tab of the "Data Validation" window.



Introduction(Microsoft Excel)

How to select from an pull-down list to find the corresponding value.

How to put a blank in the first candidate in the pull-down.

How to enable input of only certain multiples.