Using data verification in Excel is mainly to limit the data type or range of input in cells and prevent incorrect input. 1. Restrict input to specified options: By setting the source of the list (such as "male, female, unknown"), the drop-down menu is selected; 2. Set the value range: For example, set the age between 18 and 60, select an integer and set the minimum maximum value; 3. Prevent duplicate content: Check uniqueness with the formula = COUNTIF(A:A,A1)=1 to avoid duplication of ID number and other repetitions; 4. Ignore blank items: Check "Ignore empty values", allowing blanks, but the rules must be compliant when filling in, such as 11-digit phone numbers. Using these functions rationally can greatly reduce input errors, especially suitable for multi-person collaboration scenarios.

Use data verification in Excel mainly to limit the data type or range entered in the cell and prevent incorrect input. For example, if you hope that a column can only enter specific options, dates, numeric ranges, etc., data verification can help you achieve it.

The following are some common application scenarios and operation methods, suitable for daily office work and form sorting.

Restrict input to specified options (drop-down menu)

This is the most commonly used data verification method, such as allowing your form to only select "male" or "female", or "completed" or "in progress".

Operation steps:

• Prepare a list of all allowed input values ??(such as A1:A3 fill in "Male", "Female", "Unknown")
• Select the cell area you want to set the rules (such as B1:B100)
• Click "Data" → "Data Verification" in the menu bar → select "List" in the conditions
• Enter =$A$1:$A$3 in the "Source" box or enter it manually directly男,女,未知
• Click OK

In this way, a drop-down arrow will appear in the set area, and the user can only select it from it and cannot enter it at will.

Set the numerical range (for example, the age must be 18~60)

If you want a column to only enter numbers within a certain range, for example, the employee age must be between 18 and 60, you can use this function to control it.

Setting method:

• Select the cell area to be set
• Open the Data Verification dialog box
• Select "Integer" or "Decimal" in the condition
• Select "between" for operator
• Enter the minimum value of 18, the maximum value of 60

This way, if someone enters a value that is out of range, Excel will pop up and block the input.

Note: You can customize the prompt information in the "Error Warning" tab to tell the user the correct input format to avoid misunderstanding.

Prevent duplicate content (such as ID number, work number)

Although there is no option to "no duplication allowed" in data verification itself, similar effects can be achieved by combining formulas.

The method is as follows:

• Suppose you want to check if the data in column A is duplicated
• Select "Custom" in Data Verification
• Input formula: =COUNTIF(A:A,A1)=1

In this way, when the user tries to enter an existing value, Excel will report an error.

This technique is very practical when entering information such as unique numbers, mobile phone numbers, etc.

Ignoring blank items does not affect verification (optional input)

If you want certain fields to be left blank, but once filled in, it must comply with the rules, you can check "Ignore empty values" in the data verification window.

For example, some employees may not have filled in their phone number yet, and you can allow blanks at this time, but if you fill it in, it must be an 11-digit number.

Basically these common uses. Data verification may seem simple, but reasonable use can greatly reduce input errors, especially in multi-person collaboration forms.

The above is the detailed content of how to use data validation in excel. For more information, please follow other related articles on the PHP Chinese website!