How to check in Excel. Checking data entry in Excel and its features

When entering large quantity information in table cells is easy to make mistakes. 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 necessary 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 a cell, including numbers decimal part(you cannot enter text, 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 fraction numbers (for example, 0.5 is noon). The numerical equivalent for 23:59:59 would be 0.99999.

Text length. The cell is only allowed to enter a certain amount characters. 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. It is interesting that if there is a limit of, for example, less than 5 characters, you will not be able to enter the formula =SQRT(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,

To	Enter the formula	Explanation
Cell B2 contained only text	ITEXT(B2)	IN Data type no option to select type Text, so we have to achieve this indirectly. You can Data verification apply directly to cell B2
Allow a value to be entered into a cell B1 only if after entering the value in the cell D1 will be more than 100, in D2 less than 400	AND(D1>100;D2<400)	Data verification apply to cell B1 . Moreover, in the cell D1 the formula =B12 was introduced, and in D2 – formula =B13. Although this formula is equivalent to the constraint Valid with a range from 50 to 133.33, but with more complex cell connections, this technique can be useful
The value in the cell containing the employee's age ( C1 ), must always be greater than the number of full years of work ( D1 ) plus 18 (minimum age for employment)	=IF(C1>D1+18,TRUE,FALSE)	When filling out a table with data on age and work experience, you can set this check for both cells ( C1 And D1 ). To do this, you need to select 2 cells at once, call Data verification and slightly modify the formula =IF($C1>$D1+18,TRUE,FALSE)
All data in a range of cells *A1:A20* contained values	=COUNTIF($A$1:$A$20,A1)=1 =MATCH(A1,$A:$A,0)=ROW(A1)	Cells need to be selected A1:A20 , call the tool Data checking and enter the formula. The second formula can be used for the entire column A , for this you need to select not the range, but the entire column A
The value in the cell containing the product code name ( B5 ), always began with the standard prefix "ID-" and was at least 10 characters long.	=AND(LEFT(B5,3)="ID-"; LENGTH(B5)>9)	We enter data verification for the cell B5

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 can display 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.

"Protection Excel cells from incorrectly entered data." Here we will consider another option - how to check the correct spelling of code with letters and numbers in Excel.
First option.
Validation of data entry intoExcel.
We have such a table. In column A we will write codes with letters and numbers.
In these cells we will install data verification in accordance with our conditions. Select the cells of column A (A31:A36).
On the “Data” tab, in the “Working with Data” section, click on the “Data Check” button. The Validate Input Values dialog box appears.
In this window, on the “Parameters” tab, in the “Validation conditions” - “Data type” section, select the “Other” function.
In the “Formula” line we write the following formula.
=AND(LEFT(A31)="F",LENGTH(A31)=3,ESNUMBER(VALUE(RIGHT(A31,2))))
Explanations for the formula.
With this formula we tell Excel that in the cells of column A (from cell A31) the data should begin with the letter “f” - this is part of the formula “LEFT(A31)="F"; .
A total of 3 characters should be written in the cell - this is the function in the formula - LENGTH (A31) = 3.
And the function ISNUMBER(VALUE(RIGHT(A31,2))) says that the last 2 characters in the cell must be numbers.
Click the “OK” button. All data checks have been installed. Let's check.
In cell A34 we wrote the first letter “a”. An error warning window appears.
If we enter a number less than or more than 3 digits, a window will again appear indicating an error.
Attention!
You can write a message - indicate what the error is, or write a hint on how to write the code correctly. To do this, go to the “Error Message” tab or the “Input Message” tab.
How to set error messages or prompts for filling out a cell, read the article " Validating Data in Excel ».
Second option.
Validation of entered data inExcel.
You can write any number of characters, any letters, several letters, etc. in the formula. For example, this formula.
=AND(LEFT(C33)="Fruit/";LENGTH(C32)=8,ESNUMBER(VALUE(RIGHT(C32,2))))
The result is the following code.
The Data Validation feature can be used to prevent data in a table from being retroactively corrected. For more information about this method, read the article “To avoid correcting data in Excel spreadsheet backdating" .
You can prevent the entry of duplicate data. For this method, see the article “Prevent entering duplicate values in Excel.”
You can do it yourself in Excel a simple table, you can make a complex table, with filters, etc. For ways to make a table in Excel, see the article "

MS Excel has a number of very useful tools for amazing easy creation for real complex shapes collecting information. However, here’s the problem - most often the stumbling block in interaction with them is not the capabilities table editor, but human “club-handedness”. Users erase formulas from cells, enter unselfish data, and in general, spoil the fruits of our labors.

Fortunately, they are not doing this out of malice, and all we, the developers, need is to provide them with tips and answers already prepared in advance. I will tell you how to do this in this article.

Hints in MS Excel

The first way to insert a tooltip

First of all, let's deal with the tips. Take a look at my sign. It seems that everything is simple, but even with such simplicity you can plant a substantial vegetable garden. For example, the “document number” field is No. 1, 1, or 22.03-1?

The simplest thing is to right-click on the desired cell and select “ Insert Note". In the field that appears, just write the required text, and if the user moves the mouse cursor to this cell, the text will immediately appear in the tooltip.

Please note: a cell with a note is marked with a special icon - a small red triangle in the right top corner. The note text itself can be formatted like any other - you can make it bold or italic to emphasize it. important points of your message.

You can only delete comments from context menu right button mice

Please note that clicking the Delete button in a cell will not delete the note. You can get rid of it (or change its text) by right-clicking in the cell again and selecting “Delete Note” or “Edit Note”.

Second way to insert a hint

Another way to enter a hint is interesting to us only from the point of view of what we will talk about next (input filtering), however, it is useful to know about it.

Place the mouse cursor in the selected cell in the " Data" in Group " Working with data» open the tool « Data checking", and fill out the "header" and "message body" fields on the "tab" Message to be entered". Click OK.

Now, when you click on a cell with a hint (note that a cell marked in this way is not marked with a red triangle, and looks the same as any other), its text will also be displayed in a yellow rectangle. You cannot delete or change a note entered through “Data Check” using the right mouse button.

Check by number of entered characters

Tips are good, but what about the most “progressive” users who don’t read the tips? There is only one thing left to do - shake hands... that is, use input filtering, I wanted to say.

Open the tool again Data checking", go to the tab " Options» and look at our capabilities to limit the user's capabilities.

The first thing that might be useful is checking by length of entered text. Select the item “ Text length" and indicate any range of restrictions. For example, I want the text in the “Message” field to be as short as possible, but at the same time the cell cannot remain empty. Values from 3 to 25 entered characters, this is quite enough to enter something like “Payment reminder” (21 characters), and if you try to enter more long text, MS Excel will display a warning window and will not allow you to complete the input.

Check by number

Another good way filtering which will come in handy without a doubt: filtering by number. Install a filter similar to the one shown in the figure and forever get rid of problems with letters, spaces and extra symbols appearing in the price field.

Select from an existing list of values

The most powerful and interesting input check in MS Excel is selection from a drop-down list. It is not suitable for all types of cells, but where accuracy is needed and the range of values is not too large, sampling by list is what you need.

Now, let's check using the drop-down list in MS Excel. We will enter the data manually, separated by semicolons.

Select in " Data verification» tab « Options", and indicate “Data type” → “List”. You can enter “preset” values in two ways:

Select a data range using the Data Range button.
Enter the values of the drop-down list manually, separated by semicolons.

...or choose from the range

The second method is more interesting, since the data is “hidden” from the user, but entering more than a dozen of them in a row is problematic. The first method allows you to easily specify a much larger list for selecting data, but first you will have to place this list somewhere in the document (each value on a separate line), for example, on a separate “service” sheet in the same MS Excel workbook.

Select from a list on an MS Excel sheet. You cannot enter any other data.

That's it - now you can fill the cell only with the values that we indicated in the list. When we try to enter data manually, MS Excel will give us a familiar error.

By the way, you can change its text - to do this, go to the “ Output message"In "Data check", as we already did with the "second method" of inserting hints, change the text to the required one.

Excel allows you to control the data entered into table cells. That is, specify the type and range of data that can be entered into the cell; all other data is considered incorrect and cannot be entered into the cell. You can also set a range acceptable values numbers, for example, from 1 to 100, dates, for example, not earlier current date, maximum maximum or minimum value, number of characters entered, etc. If the data entered into a cell does not satisfy the specified rules, on the screen, after pressing the key , an error message window appears.

1. Select a cell or group of cells for which you need to define a control procedure.
2. On the Data tab -> Working with data, click on the Data Validation button.
3. On the Options tab of the Input Value Validation window, set the data validation condition.

The verification condition is set by selecting in the Data Type and Value drop-down lists, respectively, the type of data (integer, real number, date, etc.) that is considered correct, and the comparison rule (between, greater than, less, etc.), as well as boundaries of the range of acceptable values (Minimum and Maximum fields). For example, to set a condition meaning that only an integer from 1 to 99 can be entered into a cell, you must select Integer in the Data Type list, select the option between in the Value list, and enter 1 and 99 in the Minimum and Maximum fields, respectively. .

If a cell has a validation rule defined, and the value the user entered into the cell is incorrect, the result of pressing the key or trying to move to another cell in some other way (pressing a key or mouse click), a standard error message appears on the screen. Clicking the Repeat button in this window activates the process of editing the contents of the cell in which the incorrect data is located, using the Cancel button to remove incorrect data from the cell.

The developer can specify a message that will appear on the screen in response to erroneous data entry. To do this, open the Error Message tab, select the type of message in the View list, and enter the title and text of the message in the Header and Message fields, respectively.

The message type determines the type of icon displayed in the message window and the actions that can be taken if the data is incorrect.

Message type	Icon	Command Buttons
Stop		Redo, Cancel
Warning		Yes, No, Cancel
Message		OK, Cancel

Regardless of the type of message, if an error occurs and the Cancel button is clicked, the operation of entering data into a cell is canceled, and the state of the cell remains as it was before the attempt to enter data.

The warning message ends with the question "Continue?". As a result of clicking the Yes button, incorrect data is written to the cell. Clicking the No button activates editing mode.

Clicking the OK button in the window information message leaves incorrect data in a table cell.