The Microsoft Excel spreadsheet editor requires a huge amount of data to be entered during operation. In this process, you can easily make a mistake, and since many people use the program to create reports, there may be related problems. To prevent such problems, the developers introduced a special function - checking data in Excel.
In addition to checking, there are many other functions that allow you to carry out this work. We list the existing types of checks.
Validating Entered Values
Microsoft Office Excel allows you to flexibly customize your entries. In this case, the correctness will be checked by one or several parameters. Thanks to this, you can configure the search and cell.
Numeric Checks
Only one data type can be allowed to write to a table cell. These include:
- Integer values - only numbers without fractional parts can be entered into the cell. At the same time, you can also limit their value to a certain range or prohibit the entry of negative ones. This works well for column and row numbers.
- Real numbers. Only numerical values can be entered in a cell, which can include a fractional part (up to tenths). However, it is not possible to enter any text. You can optionally enter a date.
- If you select the "Date" option, you will be able to enter numerical data only in a specific format. To do this, check and search by the entered number, and if the format is selected incorrectly, an error will appear. Range of values: from January 1, 1900 to December 31, 9999.
- If you select the type of time limits, then, as in the case of the date, you can enter only the time period in a certain format. In addition, you can limit the entered values, for example, by entering data only after noon. It is also possible to enter data into a cell in Microsoft Office Excel using the numerical equivalent. So, 12:00 corresponds to the number 0.5. This is due to storage methods in Microsoft Office Excel. In the application, a fractional number is responsible for the date and time. For example, noon: 12/24 = 0.5.
Text check
You can limit the way text is entered by various methods. Let's consider in more detail.
- Checking data in Excel by the length of the entered text. Suitable for surnames and company names. In this case, it is allowed to enter into a certain cell only a predetermined volume of characters. You can also enter numbers and dates. However, there are their own characteristics. Due to the storage of dates in the form of a decimal number, it will not be possible to enter a date later than 13/10/2173, but only if you limit the duration of the entered value to 5 characters. The same applies to formulas. If the result of the formula is too long, writing to the cell will not be done.
- List of restrictions. Validation of input data in Excel is carried out using a predefined list of restrictions. In this case, you can pre-set a specific list of restrictions. In addition, you can specify values in a list using a cell reference or a named formula. The list can be filled in various ways.
Using the formula
There is a separate parameter if none of the standard ones meets the user's needs. Data verification in Excel in this case is carried out according to the formula. In the text box, you will be prompted to enter a formula corresponding to the desired type of values that will be entered into the cell. Thus, in Excel, checking the data in a cell is more accurate.
Some input methods.
- In order for the cell to contain only text with no characters, you can define only text values for input. Since it is impossible to limit the entered values in a regular data check, you can mistakenly enter a number in the text field.
- Limit the introduction, provided that in any of the cells the value goes beyond a predetermined range.
- Add a check of the entered value with the formula "IF". In this case, you can enter only those values that correspond to the truth in the formula. Thus, it is possible, for example, to prevent an erroneous age or amount of money from being entered.
- In addition, you can enter only certain values in a range of cells that will not intersect.
However, there are restrictions on entering formulas. Therefore, it is better to use conditional formatting.
Displays a comment if a cell is selected
The function of checking data in Excel has the ability to add various comments when selecting the current cell in which the record was made to verify the entered values. In order to do this, in the check window, go to the message section for input and enter any text in the text fields of the header and message.
Such a comment will remain on the screen while the cell is highlighted, which can be useful for long-term work.
Error message output
How to do data validation in Excel? If the user enters an incorrect value, then you can display an error message and offer to enter the values again. In fact, the application fully complies with the MessageBox function from the built-in programming language in Microsoft Excel Visual Basic Application.
In the check selection window, you need to go to the "Error message" section, then check the box "Display error message", select the type of message, then enter the header and an arbitrary message in the text box. Checking data entry in Excel in this case is carried out according to two conditions.
Types of error messages:
- “Stop” - cancel entering incorrect information, display the specified message.
- "Warning" - when choosing this type of message, a warning will appear about the incorrectness of the entered data, the program will offer to correct the error.
- A “message” is about the same as a warning, but it looks less intimidating and does not suggest changing the contents of a cell.
Using links to other sheets
In new versions of the program, you can link several unrelated sheets, as in conditional formatting. In order to be able to check the availability of information on another sheet when filling out the conditions, you can use the function to obtain names.
In order to correctly verify the data that is on another sheet, you must first determine the sheet name and the name of the cell, then use the reference type to refer to the full name of the sheet and cell and enter the data correctly. The issue was resolved with the Microsoft Office Redistributable Package upgrade to 2010. However, in earlier versions it is not possible to display on a sheet a data check that was performed in this way.
How data validation works
The subprogram performs its work when checking the data entered in the cell of the table. In this case, the built-in programming language is triggered; if the value is correct, no error will occur. However, if the value does not match the type or size, an error message will appear, but this has its limitations.
If you try to enter data using paste from the clipboard or during automatic completion, the check will not work. In addition, in the case of copying, you can “hurt” the verification rules and, in some cases, delete them.
How to find a cell with data verification
To find on the sheet all the cells with which the work is carried out and in which the data verification is completed, you can use the "Select a group of cells" tool. It is located in the "Home" section. The data validation property of this tool allows you to select all the necessary data in the cell.