Files Microsoft Excel quite often used for storing data that several different users work with. Sometimes users make mistakes and enter incorrect data, which causes problems of varying degrees of complexity, depending on the importance of the data. However, this situation can be easily avoided.

Especially for such a case, Excel has the ability to check the correctness of the data written to certain cells. When entering invalid Excel data will issue a warning or prohibition to continue the operation.

To enable the check, you need to select the protected cells, then go to the "Data" tab and select the "Data check" item.

In the window that opens, on the "Parameters" tab, first select the type of data that can be written to the selected cells, and then set the restrictions for the selected data type. There are quite a few options, for example, as a constraint, you can specify a certain range of numbers, a time / date range, a text of a certain length, or specify a file with a list of valid values. It is also possible to define a formula to calculate acceptable values ​​based on the contents of other cells.

As an example, we will indicate any integer no more than 100,000.

On the "Error message" tab, select the action that should occur when an incorrect entry is made. You can choose one of three options:

Stop - stops the operation, preventing incorrect data entry;
Warning - when entering incorrect data, a warning is issued, but the user is given the opportunity to continue the operation;
Message - The user is notified that the entered data is invalid, but data entry is allowed.

Also here you can add a title and message text that the user will see when entering an incorrect value. This should help the user understand what they are doing wrong.

And this is how the message looks like when you try to enter data that does not match the specified condition.

As additional assistance on the tab "Message for input" it is possible to leave a hint.

This hint will appear next to a cell when you select it and remain on the screen until you move to another cell.

And if it so happened that users still managed to "screw up", it is possible to highlight the incorrectly entered data. This can be done by selecting the "Circle Invalid Data" item in the "Data Validation" menu.

Such simple actions will make life easier for users and help to avoid many problems when working together with data in excel.

The OR function returns TRUE if at least one of the arguments is TRUE; returns FALSE if all arguments are FALSE.

Syntax

OR (boolean_value1; boolean_value2; ...)

Boolean1, boolean2, ... - from 1 to 30 conditions to test, which can be either TRUE or FALSE.

Attention!

Arguments must be boolean (TRUE or FALSE) or be arrays or references containing boolean values. Array is an object used to obtain multiple values ​​as a result of evaluating a single formula or to work with a set of arguments located in different cells and grouped by rows or columns. The array range uses a general formula; an array constant is a group of constants used as arguments.

If the specified range contains no Boolean values, OR returns the #VALUE! Error value.

You can use the OR function as an array formula to check if there are values ​​in the array. To enter an array formula, press CTRL + SHIFT + ENTER.

Example

	A	B
1	Formula	Description (result)
2	= OR (TRUE)	One argument is TRUE (TRUE)
3	= OR (1 + 1 = 1; 2 + 2 = 5)	All arguments are FALSE (FALSE)
4	= OR (TRUE, FALSE, TRUE)	At least one argument is TRUE (TRUE)

More about Excel.

Determining the data that can be entered into cells

1. Select the cell you want to check.

2. Select Validate from the Data menu, and then click the Options tab.

3. Determine the type of check required.

Allow only values ​​from a list to be entered

1. In the Data Type list, select List.

2. Click in the Source field and do one of the following:

to define the list locally, enter the values ​​of the list, separating them with commas;

to use a range of cells that is assigned a name, enter an equal sign (=) followed by the name of the range;

3. Select the List of allowed values ​​check box.

Allow input of values ​​that are within specified limits

3. Enter the minimum, maximum, or specific allowed value.

Allow Numbers Unlimited

1. In the Data Type list, select Integer or Real.

2. In the Value list, select the required restriction. For example, to set the lower and upper bounds, choose a value between.

3. Enter the minimum and maximum allowed values, or define a value.

Allow dates and times within a specific time interval

1. In the Allow field, select Date or Time.

2. In the Data field, select the required restriction. For example, to allow dates after a specific day, select a value greater.

3. Enter a start, end, or specific date or time.

Allow text of a specific length

1. Select the Text Length command in the Data Type window.

2. In the Data field, select the required restriction. For example, to set a specific number of characters, select a value less than or equal.

3. Specify a minimum, maximum, or specific length for the text.

Counting valid values ​​based on the contents of another cell.

1. Select the required data type from the Data type list.

2. In the Data field, select the required restriction.

3. In the field or fields below the Data field, select the cell that you want to use to define valid values. For example, to only allow entry of information for an account if the total does not exceed the budget, select the Decimal Places value in the Data Type drop-down list, select a value less than or equal to the Data drop-down list, and in the Maximum field, select the cell containing the budget amount.

Using a formula to calculate assumptions

1. Select the Other type in the Data Type box.

2. In the Formula field, enter the formula for calculating the Boolean value (TRUE for valid data or FALSE for invalid data). For example, to allow a value to be entered in a cell for a picnic account only if nothing is funded for a discretionary account (cell D6), and the total budget (D20) is also less than the allocated $ 40,000, you can enter = AND (D6 = 0 ; D20

4. Determine if the cell can be left blank.

If null (null) values ​​are allowed, select the Ignore check box empty cells.

If you are not allowed to enter blank (zero) values, clear the Ignore Blank Cells check box.

Note... If allowable values specified by a range of cells with an assigned name that contains a blank cell, selecting the Ignore Blank Cells check box will allow any values ​​to be entered into the cell being checked. This is also true for any cells that are referenced by validation formulas: if any cell that the reference points to is empty, then when the Ignore empty cells check box is selected, you can enter any values ​​in the cell to be validated.

5. To display an additional message for input when a cell is selected, go to the Message tab and select the Show a prompt if this cell is current check box, then specify a title and enter text for the message.

6. Determine the way Microsoft Excel reports incorrect data entry.

Instructions

1. Click the Error Message tab and select the Display error message check box.

2. Select one of the following options for the Appearance field.

To display an informational message that does not prohibit entering incorrect data, select the Information value.

To display a warning that does not prevent you from entering incorrect data, select Warning.

To prevent incorrect data entry, select the Stop value.

3. Specify a title and enter text for the message (up to 225 characters).

Note... If no title and text are entered, the default title is "Microsoft Excel" and the message "The entered value is invalid. The range of values ​​that can be entered into a cell is limited. "

Note... Validating the values ​​entered in a cell does not format the cell.

Sometimes, when working with tables in the Excel editor, a notification appears that "the range of values ​​that can be entered into a cell is limited." The biggest problem is that you can't enter the information you want. In fact, there is nothing wrong with that. This situation can be corrected in a few steps. In this article, we will take a closer look at what exactly this window appears and what can be done in this situation.

In the Excel editor, it is possible to restrict the input of information by some criteria. The process is as follows.

1. Click on any cell.
2. Go to the Data tab.
3. Click on the highlighted tool.
4. Select "Data Validation".

1. After that, a window will appear in which you can select the format of the information to be checked.

Let's take a closer look at these formats.

In this case, you can enter anything into the cell. The parameter is used by default for any cell.

By choosing this format, you can edit additional customization in the second field.

For example, if you select the option "between", then you will see that two additional fields will appear in this window: "Minimum" and "Maximum".

If you choose "greater than" some value, then you will need to enter the minimum acceptable threshold. That is why you will see only one field - "Minimum", since "Maximum" is unlimited.

In this case, the principle of operation is exactly the same as with integers. The difference is that this time you can use any value. Including fractional ones. For those who do not know what real numbers are, you can read more about them in Wikipedia.

This format is the most interesting.

In order to understand its capabilities, you need to follow a few simple steps.

1. Fill a few cells with something. No matter what.

1. Click on any cage. Go to a tab you are familiar with. Click on the "Work with data" icon. Select the highlighted tool.

1. In the Data Type field, select the List option. Click on the "Source" column. Then select the desired range of cells. This is much more convenient than editing the link manually. Click on "OK" to continue.

1. Thanks to this, now in this cell you can select the desired word from the drop-down list. This is much more convenient than changing the text manually.

This format is suitable for those cases when the cell should contain only the date. In this case, exactly the same additional conditions are available as with integers and real numbers.

The same is here. Only here one time is indicated (no date).

Sometimes there are situations when it is necessary to limit not the format of the cell, but the length of the content of the expression. That is, thanks to this setting, you can make sure that in certain fields you can specify text no more than the required number of characters. For example, in the column phone number or city name.

There are quite a few options. This method is used when filling out any forms or questionnaires.

This format is different from all the others. Here you can specify any formula to check if the information matches any condition.

If you wish, you can add some kind of notification about an error when entering data in a cell. To do this, you need to perform a few simple operations.

1. Repeat the steps described above to open the "Check entered values" window.
2. Choose some type of data. As an example, we will indicate that you need to enter a "Real" number that is greater than 10.

1. Now go to the "Error message" tab.
2. Choosing the type of display:
   • "A warning";
   • "Message".
3. We specify the title and text of the notification.
4. To save the check of the entered values, click on "OK"

As an example, we will indicate the following settings.

1. Enter any number. For example, anything less than the specified value. Press the Enter key. And you will see a notification that there was a data entry error.

V this case in the "View" field, the "Message" format was specified. In this case, you can enter any numbers. But you will see a similar notification every time.

If you make the type "Stop", then you will not be able to enter an incorrect value.

Now try to remove the entered settings and leave empty fields.

As a result, you will see the above error. That is, if you do not specify anything manually, the editor will display a warning by default.

But please note that the message text is different!

Difference between Microsoft Excel versions

The instructions described above are suitable for modern editors 2010, 2013 and 2016. There are some differences compared to older programs.

Open the same file in Excel 2007. Enter an invalid value. And you will see this error. That is, in modern editors, the default message text has been changed. The species familiar to everyone existed until 2007.

In old Excel 2003, the error is exactly the same.

Data entry message

To prevent users from entering such errors after entering information, it is necessary to indicate such prompts in advance. This is done very simply.

1. Click on any cell for which you want to set some rules.
2. Open the "Data" section in the menu.
3. Select the Data Tools tool.
4. Click on the "Data Check" icon.

1. In the window that appears, go to the "Message for input" tab.
2. Enter any text in the specified fields.
3. Click on "OK" to continue.

1. Now, every time you activate this cell, you will see a similar hint. This will greatly facilitate the situation for other users who will work with the files you have created.

How to disable this error

If you have opened someone else's table and you need to make some changes, but at the same time you see a similar error when entering data, then there is no need to despair. The fix is ​​pretty simple.

1. Select the cell where you cannot specify the value you want.
2. Go to the toolbar on the Data tab.
3. Click on the Data Tools tool.
4. Click on the "Data Check" icon.

1. In order to remove all settings, just click on the "Clear All" button.
2. We save the changes by clicking on "OK".

1. Now you can enter any data, as if you opened an empty file and there are no settings there.

Examples from Microsoft

If you do not quite understand how to specify conditions for entering values, it is recommended to open the official Microsoft website. There you can find the examples file. It contains absolutely all types of data and various cases of drawing up documents and reports.

Conclusion

In this article, we examined in what cases an error may appear when entering values ​​in Excel spreadsheets... In addition, special attention was paid to the settings of various conditions to determine the allowable set of values.

If something doesn't work out for you, it means that you are missing something or are choosing the wrong data format. Try to repeat these steps more closely.

Video instruction

If suddenly you could not understand this manual, below is attached a video in which all the steps are described in even more detail with additional explanations.

Consider how to protect individual cells in Excel ... There are several ways. You can insert a drop-down list into a cell. The user will select the desired word from the list that appears. For how to make a drop-down list, see the article "Drop-down list in Excel".
Second way to protect Excel cells from entering incorrect data - this is the "Data Validation" function.This is needed for correct filling questionnaires, applications, for working with a table where an employee is constantly distracted, etc.
First, mark the cell or range of cells where you want to set restrictions on data entry.How to select a range of cells by its name, see the article “Range in Excel».
Now go to the "Data" tab in the "Working with data" section, click on the "Data check" button.The window "Validation of entered values" will be displayed. On the " Options "in the line" Data type "there will be a type -" Any value ".
We need to set our value here. We have installed the "Integer" function. The smallest and largest numbers that can be entered were specified.
For example, the questionnaire says “age”. You need to indicate the full number of years, and write the date of birth.
We filled the dialog box like this.
Click "OK". Now we check, enter different numbers, and when we entered the number 1234 (this is more than 100) and pressed "Enter", such a warning window appeared.

Press the button "Repeat" or "Cancel" and write the correct number.
On the "Input Message" tab of the "Validate Input Values" dialog box, you can write an explanation of what exactly needs to be written here. And, when you hover over this cell, this message will appear. For example. In the window "Validation of entered values" on the third tab "Error message" you can write the text of the message that will be shown if the number is entered incorrectly.

To remove this setting from the cell, go to the "Data Check" function and press the "Clear All" button, then - "OK".
You can customize the cells of the table, the form for filling in so that they could not write only the date of the working day. for more information about such a setting of cells, see the article "Date Validation in Excel".
You can customize the table so that you cannot enter one name. word, code, meaning twice. Read about this "Prevent duplicate values ​​from being entered in Excel."
To control the timing of product sales, the timing of invoices, the difference in amount, etc., you can set the "Conditional Formatting" function. For example, if the amount in a cell is more than 6,000 rubles, then the cell will be colored.
See how to install this feature in the article "

When working with large amounts of information, especially when it is processed by formulas or macros, it is important that the data does not have errors that can affect the result. Difficulties in obtaining correct sources appear when they come from unstructured sources (poorly designed databases, manually filling out tables and forms by uninterested people). It is especially difficult to achieve the correct design for a specific mask (template). For example, a date can be written as a string in several ways:

• 01.01.2001;
• 01/01/2001;
• January 1, 2001, etc.

This article describes the functionality to simplify the process of collecting and validating data in Excel.

Where is?

To configure the parameters for checking the entered values, on the “Data” tab in the “Working with data” area, click on the “Data check” icon or select a similar item from the drop-down menu:

A window with default settings will appear on the screen, where any value can be used as the data type:

Configuring a check condition

Initially, you need to select the type of data to be checked, which will be the first condition. A total of 8 options are provided:

• Integer;
• Real number;
• List;
• Date;
• Time;
• The length of the text;
• Another.

In accordance with the selected item, it becomes possible to select additional conditions. For numbers, dates, time and text length, it is possible to specify restrictions: less, more, range, etc. The source is bound to the list, and the item “other” suggests entering a formula that returns a boolean value.

The most unusual view is drop-down list.

It allows you to select the values ​​specified as a source and limit the valid values ​​to it. It is also possible to create dynamic dropdown lists.

Excel cell tooltip

Data validation functionality in Excel allows you to customize tooltips for worksheet cells. To do this, go to the second tab of the input validation window - "Message for input".

The image shows an example of a possible message for a cell in which integers from 1 to 31 are entered (settings of the "Parameters" tab). The title and messages are indicated at your discretion, there are no rules for their design. Do not forget to check the box "Show hint if cell is current", otherwise the message will be deactivated.

An example of a tooltip in Excel:

Error message output

The last tab of the data validation window allows you to customize the behavior and display of messages when an erroneous value is detected.

There are three types of messages that differ in behavior:

• Stop;
• A warning;
• Message.

Stop is an error message and allows you to perform only 2 actions: cancel the input and repeat the input. If canceled, the new value will be changed to the previous one. Repeating the entry makes it possible to correct the new value.

A warning is more loyal in comparison with a stop, since it allows you to leave a value that does not meet the test conditions after the user confirms the input.

The message displays an error in the form of simple information and allows you to undo the last action.