Invalid value entered. How to prevent incorrect values ​​from being entered in Excel using data validation. Setting up a check condition

It's easy to make mistakes when entering a lot of information into table cells. In EXCEL, there is a tool for checking the entered data immediately after pressing the ENTER key - Data Validation.

Tool Data checking() would not be so popular if its functionality was limited only to the verification itself. The basic techniques for working with this tool are briefly listed below.

A. Checking the entered values

As you can see in the figure below, the conditions for checking the entered value can be configured very flexibly.

You can only allow values ​​to be entered into a cell of a certain type by selecting the required condition from the drop-down list:

• Integer. Only integer numbers belonging to a certain range are allowed to be entered into a cell;
• Valid. Only numbers can be entered into the cell, including those with a decimal part (you cannot enter text, but you can enter a date);
• Date of. It is assumed that dates ranging from 01/01/1900 to 12/31/9999 will be entered into the cell. Read more about the Date format in the article
• Time. It is assumed that in the cell with This check This type will enter the time. For example, the figure below shows a condition when it is allowed to enter time in a cell that only belongs to the second half of the day, i.e. from 12:00:00 to 23:59:59. Instead of tediously entering the value 12:00:00, you can use its numeric equivalent of 0.5. The ability to enter numbers instead of time follows from the fact that any date in EXCEL is associated with a positive integer, and therefore the time (since it is part of the day) corresponds to the fractional part of the number (for example, 0.5 is noon). The numerical equivalent for 23:59:59 would be 0.99999.

• Text length. Only a certain number of characters can be entered into a cell. With this restriction, you can enter both numbers and dates, the main thing is that the number of characters entered does not contradict the text length limit. For example, if the number of characters is limited to less than 5, you cannot enter a date later than 10/13/2173, because it corresponds to the number 99999, and 10/14/2173 is already 100000, i.e. 6 characters. Interestingly, if you limit, for example, less than 5 characters, you will not be able to enter the formula =ROOT(2) into the cell, because result =1.4142135623731 (depending on the precision specified in EXCEL), but =ROOT(4) - you can, because the result =2, and this is only 1 character.
• List. Probably the most interesting data type. In this case, the entry of values ​​into a cell can be limited to a previously defined list. For example, if you specify the unit of measurement of a product as a source, separated by a semicolon pcs;kg;sq.m;cub.m, then you will not be able to select anything other than these 4 values ​​from the list. In the source, you can specify a range of cells containing a pre-generated list or a link to . An example is given in the article
• Another. The cell is allowed to enter values ​​that satisfy more complex criteria. To set the criteria, you must use a formula. Let's consider this condition in more detail.

With the selected type Another, in field Formula you need to enter a formula to calculate the logical value. If it evaluates to TRUE, then such a value is allowed to be entered into the cell, if FALSE, then your value cannot be entered. As a formula argument, you need to use a reference to the cell itself to which it is applied. Data checking or a reference to a cell that depends on it. For example,

When selecting several cells, where necessary, do not forget to provide an absolute reference to the cells (for example, $A$1:$A$20 ).

When using the tool Data checking, it is assumed that constants will be entered into the cell ( 123, product1, 05/01/2010 etc.), although no one forbids entering formulas. In this case, the result of the formula calculation will still be checked. In general, I don’t recommend entering formulas into cells with data verification - it’s easy to get confused. In this case, I advise you to use .

IN.Display a comment if the cell is the current one.

Use the tab Output message to display the comment.

Unlike the usual note ( Review/ Comment/ Create a comment), which disappears after the mouse cursor leaves the cell (if the option is not active Show all notes), this comment is displayed whenever a cell is selected.

WITH.Displays a detailed error message.

After entering an incorrect value Data checking may display a detailed message about what was done wrong. This is some kind of analogue Msgbox() from VBA.

D.Creating Linked Ranges (Lists)

E. Using references to other sheets in rules

In EXCEL 2007 Data verification, as in, you cannot directly specify a link to the ranges of another sheet, for example, like this =Sheet2!$A$1 . Allows you to bypass this limitation using .

If in Data verification you need to make, for example, a reference to a cell A1 another sheet, then you need to first define for that cell and then reference that Name in the rule Data verification.

In Excel 2010, on the other hand, you can use data validation rules that reference values ​​in other sheets. In Excel 2007 and Excel 97-2003, this type of data validation is not supported and does not appear on the worksheet. However, all data validation rules remain available in the workbook and are applied when you reopen the workbook in Excel 2010 unless they were changed in Excel 2007 or Excel 97-2003.

F. How Background Check Works

Data checking explicitly triggered when values ​​are entered into a cell from the keyboard and then a key is pressed ENTER. In this case, a window describing the error appears.

If values ​​are inserted via Clipboard (Paste values) or using a keyboard shortcut CTRL+ D(copying the value from the cell from above) or are copied from top to bottom, then the check is not performed explicitly. In addition, when copying values, you can accidentally delete rules altogether Data checks, for example, if the source cell is not defined Data checking, and data from it is inserted via Clipboard and using a key combination CTRL+V.

Let's explain with an example. Suppose to cell A1 applied Data checking subject to verification Another, where =ROW(A1)=1 is entered in the formula field, i.e. for all cells from the first row the condition Data checks will take the value TRUE, for other rows - FALSE, regardless of the contents of the cell.

Now let's select the cell A2 and press CTRL+D. Value from A1 will be copied to A2 along with the condition Data checks. Despite the fact that now the condition Data checks will evaluate to FALSE and no warning message will be displayed. To ensure that the data in the cells meets the conditions defined in Data verification, you need to call the menu command Circle incorrect information (). Cells with incorrect data will be outlined in red ovals. Now let's select the cell again A2 and press the key F2(enter Edit mode), then click ENTER- a window will appear with a message that the entered value is incorrect.

There is another way to bypass background checks. Let's say input into a cell is limited to values ​​from 1 to 3. Now into any other cell without Data checks enter the value 4. Select this cell, in Formula bar select the value 4 and copy it to Clipboard. Now let's select the cell with Data verification and press CTRL+V. The value has been inserted into the cell! Besides, Data checking remained untouched, unlike the case when, through Clipboard, for example, a value from WORD is inserted. To make sure that the data in a cell does not meet the conditions defined in Data verification, you need to call the menu command Circle incorrect information (Data/ Working with data/ Validating data/ Circle incorrect data).

G. Search for cells with Data Validation

If there are many cells on the sheet with Data verification, then you can use the tool ( Home/ Find and Select/ Selecting a group of cells).

Option Data checking This tool allows you to select cells that are subject to data validation (specified using the command Data / Working with data / Validating data). When selecting a switch Everyone all such cells will be selected. When selecting the option These same Only those cells are highlighted for which the same data validation rules are set as for the active cell.

Let's assume that you need to enter values ​​of a strictly limited size into certain cells. For example, TIN, which must consist of 10 characters.

But often, when working in a hurry, errors in the form of more or less characters creep into such data. Therefore, for certain cells, you can limit the number of characters entered to a certain number.

For this:

1. Select the cells (you can have the entire row(s), column(s))

2. In the Menu, select “Data - Check”.

3. In the window that appears
in the “Data type” column, select “text length”,
in the “Value” column - “equal”,
in the “Length” column we indicate “10” (for TIN).

4. In the “Error Message” tab, you can set an individual title and message about this error. For example:

5. After entering the message text, click OK.

Now, if you try to enter values ​​that are not equal in length to 10 characters in the cells you specified, an error message will appear.

Moreover, it will not be possible to enter data of a different size.

If the length limit is not important, then in the “error message” tab, select “warning” rather than “stop” as the default.

Now the error message will appear, but you will be given the opportunity to enter values ​​of a different size.

If you're creating a worksheet that requires users to enter data, you can limit the input to a specific range of dates or numbers, or make sure that only positive integers are entered. Excel can restrict data entry into certain cells using data validation, prompt you to enter valid data when a cell is highlighted, and display an error message when the user enters invalid data.

Restricting data entry

Restricting data entry

Prompt for valid entries

When users click a cell that has data entry requirements, you can display a message that explains what data is valid.

Displaying an error message when entering invalid data

If you have data restrictions and the user enters invalid data into a cell, you can display a message describing the error.

Add data validation to a cell or range of cells

Note: The first two steps in this section are for adding any type of data validation. Steps 3-7 are specifically for creating a dropdown list.

Select one or more cells to which you want to apply the check.

On the tab Data in Group Working with data click the button Data checking.

On the tab Options In chapter Allow click the button List.

In field Source enter values ​​separated by semicolons. For example, enter " low", "medium", "high".

Make sure the checkbox is checked List of valid values. Otherwise, the drop-down arrow will not appear next to the cell.

To specify how to handle empty (null) values, select or clear the check box Ignore empty cells.

Test that data validation works correctly. Try entering valid and then invalid data into a cell to ensure that the validation settings are applied as you want and that your messages appear at the right time.

Notes:

Using Other Types of Data Validation

The table lists other types of data validation and shows how to apply them to the data in the worksheet.

Examples of formulas for data validation

Notes:

The examples below use the custom option when creating conditional formulas. In this case, the contents of the "Data" field do not matter.

The screenshots in this article are from Excel 2016; but the functionality is the same in Excel for the web.

To	Enter the formula
The value in the cell containing the product code (C2) always begins with the standard prefix "ID-" and is at least 10 (more than 9) characters long.	= AND (LEFT (C2; 3) = "ID-"; LENGTH (C2) >9)
	=ETEXT(D2)
The value in the cell containing someone's date of birth (B6) was greater than the number of years listed in cell B4.	=IF(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)
All data in the cell range A2:A10 contained unique values.

To make your worksheet easier for users to use, add drop-down lists to cells. Drop-down lists allow users to select items from a list you create.

Video

On a new worksheet, enter the data you want to appear in the drop-down list. It is desirable that the list items be contained in an Excel table. If this is not the case, you can quickly convert the list to a table by selecting any cell in the range and pressing CTRL+T.



Notes:

1. Select the cell on the worksheet where you want to place the drop-down list.

   On the ribbon, open the tab Data and press the button Data checking.

   Note: If the button Data checking unavailable, the sheet may be protected or shared. Unlock specific areas of the protected workbook or unshare the worksheet, and then repeat step 3.

   On the tab Options in field Data type select item List.

   Click the field Source and highlight the list range. In the example, the data is on the Cities sheet in the range A2:A9. Note that the header row is not in the range because it is not one of the options available for selection.

   

   If you can leave the cell blank, select the checkbox Ignore empty cells.

   Check the box List of valid values

   Open the tab Input hint.

   
   

2. Open the tab Error message.

   
   

3. Don't know which option to select in the field View?

Working with a Dropdown List

whether the column widths and row heights change when displaying all your posts.

Downloading examples

We suggest downloading a sample book with several examples of data verification, similar to the example in this article. You can use them or create your own data verification scripts. Download Excel data validation examples.

You can enter data faster and more accurately by limiting the values ​​in a cell to options from a drop-down list.

First, create a list of valid elements on the worksheet, and then sort or arrange them in the desired order. These elements can later serve as a source for a drop-down list of data. If the list is small, you can easily reference it and enter items directly into the data checker.

see also

After creating the dropdown, make sure it works correctly. For example, it is recommended to check

To reduce data entry inaccuracies, I often use lists based on a range of cells. Let's consider the use of this tool using the example of timesheet keeping.

To begin with, on a separate sheet (this is not necessary), we will place a list of acceptable values ​​in one column or one row (Fig. 1a); see also Excel file, “List” sheet.

Rice. 1. List of surnames: (a) in random order; (b) in alphabetical order.

Download in format, examples in format

This list of last names will appear in the drop-down list in the order in which they appear in this column. It is clear that for ease of searching it is better to sort them alphabetically (Fig. 1b).

Let's give our list the name of the range. To do this, select a range; in our case, this is area A2:A21 and enter the name of the range, as shown in Fig. 2; in our case, these are “surnames”:

Rice. 2. Naming the range

Let's select the area in which we will enter last names (see Excel file, “Input” sheet). In our example – A2:A32 (Fig. 3). Let's go to the Data tab, Data Working group, select the Data Validation command:

Rice. 3. Data verification

In the “Checking entered values” dialog box, go to the Parameters tab (Fig. 4). In the “Data type” field, select “List”. In the “Source” field we indicate: (a) the area of ​​cells in which the list is stored; this option is suitable if the list is located on the same Excel sheet; (b) range name; This option can be used both when the list is located on the same Excel sheet, and when the list is located on another Excel sheet (as in our case). In both cases, make sure that there is an equal sign (=) before the link or name.

Rice. 4. Selecting a data source for the list: (a) on the same sheet; (b) on any sheet

And about two more options on the “Options” tab:

• Ignore empty cells. If the checkbox is checked, Excel will allow you to leave the cell blank. If the checkbox is unchecked, you can exit the cell only after selecting one of the names in the list. The peculiarity of the option is that Excel will allow you to move between cells (for example, using the Enter key or the up/down arrows), but you cannot start typing, then erase all the characters and move to another cell.
• List of valid cells. If there is no checkmark, then when you place the cursor in an input cell, the list icon will not appear next to the cell, and therefore you will not be able to select from the list. Although all other properties of working with a list will be in effect, Excel will not allow you to enter an arbitrary value into a cell.

Let’s go to the “Input Messages” tab in the “Checking Input Values” window. Check the “Display tooltip if cell is current” box. Enter the title and text of the message in the appropriate fields (Fig. 5). Subsequently, when the user stands on one of the cells of the input area (in the example in Fig. 5 - in cell A6), the message we created will be displayed.

Rice. 5. Setting the Input Message

In the “Checking entered values” window, go to the “Error message” tab (Figure 6). Check the “Show error message” box. In the “Type” field, select the message type. Enter the title and text of the message. The message we created will appear whenever the user tries to enter an invalid value.

Rice. 6. Setting Error Messages

Acceptable types of error messages (Fig. 7):

• Stop– prevents entering invalid data; button Repeat allows you to return to input, button Cancel Repeat.
• Warning– warns about entering invalid data, but does not prohibit such input; button Yes allows you to accept invalid input; button No allows you to continue typing (the value previously typed in the cell becomes available for editing); button Cancel clears a cell and allows you to start typing over or move on to typing in other cells; button is selected by default No.
• Message– notifies about entering invalid data; although he allows them to be introduced. This message type is the most flexible. When an information message appears, the user can press the button OK to accept invalid data entry, or press the button Cancel to cancel the entry; button is selected by default OK.

Rice. 7. Selecting the error message type

Some comments. 1. If you entered too long text in the Message window of the Error Message tab, the error message window will be too wide (as in Fig. 7); use line break Shift + Enter at the point in the message where you want to split the lines (Figure 8).

Rice. 8. Reduced width error message box

2. The width of the dropdown list is determined by the width of the cell for which data validation is applied. Keep the input cells wide enough to avoid truncating valid entries that are wider than the dropdown list. Otherwise, you can make the wrong choice based on the first letters of the word.

3. The maximum number of entries in the drop-down list is limited, although not too much :), namely by the number 32,767.

4. If you do not want users to edit the review list, place it on a separate sheet, then hide and protect this sheet.