• home
  •  > 
  • OS problems
  •  > 
  • How to remove data validation in excel. Validating input data in Excel

How to remove data validation in excel. Validating input data in Excel

"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.
To learn how to set error messages or cell prompts, see 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 "

Recently, my daughter asked if it was possible in Excel to make a drop-down list in a cell contextual, for example, depending on the content of the cell to the left of the cell with the list? I haven’t used drop-down lists in my work for quite some time, so first I decided to refresh my knowledge on data validation in Excel. Actually, see the answer to my daughter’s question.

Data Checker

Excel allows you to set certain rules that will determine what data can be contained in a cell. For example, it is necessary that the number contained in the cell belong to the range from 1 to 12. If the user enters an incorrect value, the program will display a corresponding message (Fig. 1).

Rice. 1. Display a message about incorrect input data

Download the note in or format, examples in format

Excel makes it easy to create criteria to validate data, including using formulas to create more complex criteria.

The input checker has a fundamental drawback - if the user copies a cell and then pastes it into a cell in which the input data is being checked, then all the formulas defined for verification will disappear.

Defining the test criterion

To determine the type of data that a cell or range of cells can contain, follow these steps:

1. Select a cell or range of cells.

2. Select a tab Data, region Working with data → Data checking. Excel displays the Validate Input dialog box.

3. Click on the tab Options(Fig. 2).

Rice. 2. Tab Options dialog box Validation of entered values

4. Select one of the options from the drop-down list Data type. May vary depending on the option selected. appearance tabs Options by adding or deleting additional elements control (to define the formula you need to select the option Another).

5. Using the controls available on this tab, set the data verification criteria. Available items controls depend on the selection made in the previous step.

6. (Optional) Click on the tab Message for input and enter the message that should appear on the screen when highlighting specified cell . This message is displayed to inform the user what data can be entered. If you skip this step, no message will appear when you select a cell.

7. (Optional) Click on the tab Error message and enter the message that should appear when the user will enter an invalid value . Selecting a message type in the list View will determine what choice the user will have when entering incorrect data. To prevent entering incorrect values, you must select a message type Stop. If you skip this step, a standard error message will appear when an error occurs.

8. Click OK.

After performing these actions, the entered data will be checked in the selected cell or range.

Types of data checked

Tab Options dialog box Validation of entered values provides big choice data types for test conditions. You can select the type of valid data from the drop-down list Data type(As noted, the rest of the controls on this tab change depending on the selected data type). Can choose following types data.

  • Any value. Selecting this option removes the data validation condition. However, the input message will still be displayed unless the checkbox is cleared Display an error message in the tab Message to be entered.
  • Integer. The user must enter an integer. Using a drop-down list Meaning you can define an acceptable range of values. For example, you can specify that the input value must be an integer and greater than or equal to 100.
  • Valid. The user must enter a valid number. Range acceptable values can be defined using a drop-down list Meaning. For example, you can specify that the number you enter must be greater than or equal to 0 and less than or equal to 1.
  • List. The user must select a value from the proposed list of values. See the section below for more details. .
  • date. The user must enter the date. Using a drop-down list Meaning you can define a valid date range. For example, you can specify that the date you enter must be greater than or equal to January 1, 2012, and less than or equal to December 31, 2012.
  • Time. The user must enter a time value. Using a drop-down list Meaning you can define an acceptable range of values. For example, the time value entered must be greater than 12:00.
  • Text length. The length of the input string (number of characters) is limited. Using a drop-down list Meaning you can determine the permissible string length. For example, you can specify that the length of the input string must be 1 (one character).
  • Another. Logical formula, which determines the correctness of the user input. The formula can be entered directly into the field Formula(which appears when you select this type) or define a cell reference with a formula. Below are examples of several useful formulas.

In the tab Options dialog box Validation of entered values contains two options.

  • Ignore empty cells . If this option is checked, empty entries are allowed in cells for which data validation is defined. This flag acts a little strangely. If you clear the checkbox and define some data validation rule in the cell, then the checkbox will allow you to leave the cell empty. But as soon as you try to enter something unacceptable in a cell, and then try to leave the cell empty, it will not work.
  • Propagate changes to other cells with the same condition. If this option is checked, then all changes made will also apply to other cells that contain the original data validation conditions. If you have defined a rule in one or more cells, and then selected a range that includes these cells and some others, and clicked on the menu Data checking, then the message will appear: “The selected area contains cells without conditions on the values. Should I extend the conditions to these cells?” If you click "Yes", a tab will appear Options dialog box Validation of entered values. So now there's a checkbox Propagate changes to other cells with the same condition no need to put it.

It is important to remember that even after input validation has been defined, the user may still enter incorrect values. Even if the Tab View drop-down list Error message message type selected Stop, the possibility of entering incorrect data still exists. It is also worth paying attention to the fact that input data verification does not respond to the results of formula calculations. In other words, applying data validation to a cell containing a formula will not produce any result.

Excel has a command Data → Working with data → Data checking → Circle incorrect information, after selecting which all incorrect values ​​will be circled in red (Fig. 3).

Rice. 3. Cells with invalid values ​​(values ​​greater than 100) are circled

Creating a Dropdown List

Perhaps the most common use of input validation is to create a drop-down list of values. In Fig. Figure 4 shows an example in which the month names contained in the range A1:A12 are used to create a drop-down list.

Rice. 4. List created using Data Validator

To create a list like this:

1. Enter a list of values ​​in one column or row. These values ​​will be used in the dropdown list.

2. Select the cell that should contain the drop-down list (D3 in our example).

3. In the tab Options dialog box Validation of input data select data type List and in the field Source specify a range that contains a list of values ​​(in our example, $A$1:$A$12).

4. Make sure the checkbox is checked List of valid values.

5. Make other settings in the dialog box Validation of input data, as described in the previous section.

After completing these steps, when you activate a cell, a drop-down button will appear to the right of it. Click this button to expand the list and select the desired value.

If the list should contain a small number of values, then they can be entered directly into the field Source in the tab Options dialog box Validation of entered values(this field will appear if you select from the drop-down list Type data type List). Between the entered values ​​you need to insert a separator defined in accordance with regional settings(for Russia this is a semicolon).

If you use a range to define a drop-down list, it must be on the same sheet as the activated cell. In case the list needs to use values ​​from a range that is on another sheet, you can give it a name and then use that name in the field Source(after the equals symbol). For example, if the list contains values ​​from the MyList list, you would enter the following formula: =MyList

Validating data using formulas

Create simple checks data using the input validation tool is quite simple. But the real power of this property can only be felt if you work with data validation formulas.

A formula defined to validate data must return the Boolean value TRUE or FALSE. If it returns TRUE, the entered value will be written to the cell. If the result of the formula calculation is FALSE, an error message will appear containing the warning defined in the tab Error message dialog box Validation of entered values.

You can define the formula in the dialog box Validation of entered values. To do this, from the drop-down list Data type tabs Options select type Another. The formula can be entered directly into the field Formula or insert a link to the cell containing this formula. Field Formula appears in the tab Options only if type is selected Another.

Type of cell references in formulas for data validation

If the formula entered in the dialog box Validation of entered values, contains a cell reference, it will be treated as a relative reference to the top left cell of the selected range.

The following example will explain why relative links should be used in formulas to validate data. Let's assume that only odd numbers are needed in the range B2:B10. In the dialog box Validation of entered values There is no such data type, so you need to use a formula to create a data validation criterion. To create such a data validation criterion, follow these steps:

1. Select the range B2:B10 so that cell B2 becomes activated.

2. Select a team Data → Working with data → Data checking to open the dialog box Validation of entered values.

3. Go to the tab Options and on the list Data type select Another.

4. Enter the following formula in the box Formula(Fig. 5) =UNCOUNT(B2). This formula uses the ODD function, which returns TRUE if its argument is an odd number.

5. Go to the tab Error message and select the message type Stop. Also enter the message text “Only odd numbers are allowed.”

6. Click OK to close the dialog box Validation of entered values.

Rice. 5. Entering a formula in the dialog box Validation of entered values

Notice that the formula you entered contains a reference to the top left cell of the selected range. This formula must apply to the entire range of cells, so you should expect that every cell in that range contains the same formula. Since in the formula the cell filling is relative, this formula changes for each separate cell range B2:B10. To verify this, place the cursor, for example, in cell B5, and open the dialog box Validation of entered values. In this window you should see the formula =UNEVENT(B5)

In general, when you enter a formula to test data in a range of cells, you should use a relative reference to the activated cell, which is typically the top-left cell of the selected range. The exception is situations when you need to make a link to a specific cell. For example, you want to enter only values ​​in the range A1:B10 that are greater than the value in cell C1. To do this, use the formula =A1>$C$1

Examples of formulas for data validation

Here are some examples that use formulas to validate input. All formulas were entered directly into the field Formula in the tab Options dialog box Validation of entered values.

Enter text only. To allow text entry only (and disable text entry) numerical values) into a cell or range, use the following formula: =ETEXT(A1). Here it is assumed that A1 is the activated cell of the allocated range.

Enter values ​​greater than those in the previous cell. The following data validation formula allows you to enter a number into a cell only if it is greater than the value in the previous cell: =A2>A1. The formula assumes that the activated cell in the selected range is cell A2. Note that this formula cannot be used on the first row of a worksheet.

Enter only unique values. The following input data validation formula will not allow the user to enter duplicate values ​​in the range A1:C20: =COUNTIF($A$1:$C$20,A1)=1. Here it is assumed that A1 is the activated cell of the allocated range. Note that the first argument of the COUNTIF function ($A$1:$C$20) is an absolute reference. The second argument (A1) is a relative reference that changes for each cell in the selected range. In Fig. Figure 6 shows how this formula works. Here an attempt is made to enter the value 2 into cell A5, which is already in the range A1:C20.

Rice. 6. Use Data Validator to Prevent Duplicate Entry

Entering text starting with the letter A. The following formula uses a technique that allows you to check for a given symbol. IN in this case the formula will return TRUE if you enter a string into the cell that begins with the letter A (regardless of case): =LEFT(A1)= " a " . This formula assumes that the activated cell in the selected range is cell A1.

Below is a slightly modified data verification formula. Using this formula, you can enter a string that consists of five letters and begins with the letter A:
=COUNTIF (A1; "A????") =1

Quoted in the book by John Walkenbach. Microsoft Excel 2007. The User's Bible. – M: LLC “I.D. Williams”, 2008. – pp. 482–489.

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.

Sometimes there is a need to be safe from errors before entering data into cells that does not meet certain conditions. For example, in a store’s product and price nomenclature, it should not be possible to enter negative numbers and zeros. After all, the store does not give out goods with an additional payment or for free.

Validating input data in Excel

To prevent operator errors computer typing in Excel, we will use input data validation. Thus, we will create comfortable conditions for him for routine work, where it is difficult to avoid mistakes.

We have a sheet of the store's product range:

Now let's check. Enter a natural number in cell B2 and a negative number in cell B3. As you can see in cell B3, the action of the dial operator is blocked. An error message is displayed: “The value entered is incorrect.”

Note. If you wish, you can write your own text for the error on the third tab of the “Error Message” tool settings.

To remove data validation in Excel you need to: select the corresponding range of cells, select the tool and click on the “Clear All” button (indicated in the second picture).



Features of data verification

This method checks data only during the input process. If the data has already been entered, it will not be verified. For example, you cannot enter text in column B after you have set the conditions for filling the cells in it. But the heading in cell B1 “Price” remained without an error warning.

Attention! If cells are copied and not entered, their values ​​will also not be checked.

To check whether all entered data corresponds to certain conditions in the column and whether there are any errors, you should use another tool: “Data” - “Data check” - “Circle incorrect data”.


If the values ​​in column B must meet certain conditions but contain errors, they will all be outlined in a red oval. This tool is very convenient to use when you need to check data that has already been entered or copied.

Of course, you can perform data validation on a column using logical function Excel – “IF”. Or conditional formatting. But using the Data Validation tool is more effective, convenient and productive for this task. Especially if we need to check on multiple columns at the same time. In such cases, the rationality of its use is more noticeable.

When validating data, you can easily specify the rules that the data must meet. Unfortunately, Excel insists that the lists used for validation must be on the same worksheet as the data being validated. Fortunately, there are always ways to avoid this requirement.

In this trick, we will introduce you to two ways to validate data based on a list in another sheet. The first takes advantage of Excel's named ranges (which are covered in more detail in Chapter 3), while the second uses a function call.

Method 1: Named Ranges

Perhaps the simplest and quick way To overcome Excel's barriers to validating data, you can name the range that contains the list. To create a named range, select the cells containing the list and enter a name in the Formula Bar Name box. To run this example, we will assume that the range is named MyRange.

Select the cell in which you want the drop-down list to appear, and then select Data → Validation. In the Allow field, select List, and in the Source field, enter =MyRange. Click OK. Since you used a named range, your list (even though it's on a different sheet) can now be used as a validation list.

Method 2: INDIRECT function

Function INDIRECT(INDIRECT) allows you to reference a cell that contains text that represents the cell's address. This cell can be used as a local reference even if it receives data from another worksheet. You can use this feature to link to the sheet where the list is located.

Let's say the list is on Sheetl in the range $A$1:$A$8 . Click any cell on another sheet where this check list (select list) should appear. Then select the Data → Validation command and in the Allow field select List. In the Source field, enter the following code: =INDIRECT("Sheetl!$A$1:$A$8"), in Russian Excel versions=INDIRECT("Sheetl!$A$1:$A$8") . Make sure that the Valid Value List (In-Cell) check box is selected and click OK. The list in the Sheetl should appear in the Validation dropdown.

If the name of the sheet on which the list is located contains spaces, you must use the following function syntax INDIRECT(INDIRECT): =INDIRECT(""Sheetl"!$A$1:$A$8") , in the Russian version of Excel =INDIRECT(""Sheetl"!$A$1:$A$8") . The difference is that here there is one apostrophe after the first quotation mark, and the second apostrophe is before the exclamation mark.

It's a good idea to always use single apostrophes, regardless of whether the name contains spaces or not. With apostrophes, you will still be able to reference sheets with names without spaces, and it will also make it easier to make changes later.

Advantages and disadvantages of both methods

For named ranges and functions INDIRECT(INDIRECT) has advantages and disadvantages. The advantage of using a named range is that changing the sheet name will not affect the validation list. This highlights the lack of function INDIRECT(INDIRECT): any change in the sheet name will not be automatically reflected in it. Feature advantage INDIRECT(INDIRECT): When the first cell or row or the last cell or row is deleted from a named range, the named range will return a #REF! . This is the disadvantage of a named range - if you delete cells or rows from it, the changes will not affect the validation list.