Validating cell contents
When creating spreadsheets for other people to use, you may want to make sure they enter data that is valid or appropriate for the cell. You can also use validity in your own work as a guide to entering data that is either complex or rarely used.
Fill series and selection lists can handle some types of data, but they are limited to predefined information. For a more general case, you can select a cell and use Data > Validity to define the type of contents that can be entered in that cell. For example, a cell might require a date or a whole number, with no alphabetic characters or decimal points; or a cell may not be blank.
Depending on how validation is set up, the tool can also define the range of contents that can be entered and provide help messages that explain the content rules you have set up for the cell and what users should do when they enter invalid content. You can also set the cell to refuse invalid content, accept it with a warning, or—if you are especially well-organized—start a macro when an error is entered.
Validation is most useful for cells containing functions. If cells are set to accept invalid content with a warning, rather than refusing it, you can use Tools > Detective > Mark Invalid Data to find the cells with invalid data. The Detective marks with a circle any cells containing invalid data.
Note that a validity rule is considered part of a cell’s format. If you select Format or Delete All from the Delete Contents window, then it is removed. (Repeating the Detective’s Mark Invalid Data command then removes the invalid data circle, because the data is no longer invalid.) If you want to copy a validity rule with the rest of the cell, use Edit > Paste Special > Paste Formats or Paste All.
The figure below shows the choices for a typical validity test. Note the Allow blank cells option under the Allow list.
The validity test options vary with the type of data selected from the Allow list. For example, the figure below shows the choices when a cell must contain a cell range.
To provide input help for a cell, use the Input Help page of the Validity dialog.
To show an error message when an invalid value is entered, use the Error Alert page. Be sure to write something helpful, explaining what a valid entry should contain—not just “Invalid data—try again” or something similar.
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
