This page has been robot translated, sorry for typos if any. Original content here.

Excel secrets. Validation of input data.

as a position, to fill in even the most complex spreadsheet of problems does not make up. It is much more difficult not to make mistakes at the same time. After all, you see, it is easy to make a mistake by typing, for example, 1899, instead of 1999, or to prevent a miss in a set of impressive numbers. In the popular tabular processor Microsoft Excel, there are several tricks that allow you to simplify data entry and also perform automatic validation.

When entering numerical data it is allowed to set a range of conditions for checking the entered values. In this case, for each value entered, it will be checked whether it is pleasing to the specified interval. If this condition is not met in any way, a message appears on the screen stating that the entered value does not fit the specified limits.
In order to set the condition for checking numerical values, it is necessary to select a range of cells where numbers will be entered, also select "Data -> Verification". In the "Data Type" field you need to set integers or valid, depending on what is planned to be placed in the cell. The type "valid" is used for numbers with a decimal point. Below, the "value" field indicates a logical condition, for example, "between", "greater than", "greater than or equal", "not equal". The last pair of fields are for numeric values ​​with which the input data will be compared.

Here are the conditions for checking numeric values.
Excel allows you to simplify data entry that is repeated. Enter all the name data in a separate county worksheet. After that, having selected the range of the future check of values, select "Data -> Check", in the "Data Type" field set the value to "List". Below in the "Source" line you need to specify the addresses of the cells in which this list is located.

When using a data entry list, you must specify the address of the range in which it is located.
As a result of these actions, you will not need to enter text into the selected cells: when the cell is activated, a list will appear next to it, from which you just need to select the desired value.

Instead of entering data, it is now allowed to select them from the list.
Another convenient solution: in Excel you can set the option to display a tooltip next to a cell when entering data into it. To do this, in the "Check Input Values" window, go to the "Message for Input" tab and type the message that will appear when you enter data in the cell. Convenient will be to use the switch "Display hint if the cell is current".

A hint will help you specify what is entered in the cell.
On the "Error Message" tab, you can control the appearance of a standard system message, which prompts the user that the data was entered incorrectly. You can disable its appearance (uncheck "Show error message"), only it seems allowed to set its own text of this message in the fields "Header" and also "Message".