How Excel data validation is used

when you enter data into Excel, you sometimes encounter a lot of duplicate data, for example, there is a worksheet, the first column shows the task name, the second column shows the task performer, and the third column shows the performer's gender, in which the task executor is performed alternately for a fixed number of people, and the gender is clearly either male or female and repetitive. If you encounter this situation, you can use the Excel data validation settings to avoid duplicate input and ensure that there is no error.

First move the cursor to the top of the "Sex" column, when the cursor becomes the downward arrow, and then click to select the entire column.

Switch to the Data tab, click Data validation, and continue clicking Data Validation from the Drop-down menu.

In the Data Validation dialog box that pops up, set "allow" in the validation condition to "sequence" and enter "male, female" directly from the source (excluding the outside quotes, separated by commas).

Now when you enter the sex, you don't need to enter it manually, just click the drop down button and select it from the dropdown.

Extension Tip 1:

In addition to the above options for direct input, there is a way to enter options in the Excel table somewhere. Then above, select the "Performer" column, and bring up the Data Validation dialog box, set the validation condition to "sequence." In the source item, this is no longer directly entered manually, instead, click on the Color button on the right, and then select the entries that have been entered in the table, where the data items will automatically appear in the Data validation box, click the Color button on the right side again to confirm, return to the Full Data Validation dialog box, and click OK.

At this point in the "performer", do not need a one to enter the name of the performer, click the Drop-down button and then select it.

Extension Tip 2:

If you force a value other than the data validation that you have set up, the system pops up a warning that the data validation does not match to ensure that the input is correct.

If you feel that the warning in the box above is too blunt, you can actually customize your own prompt. In the Data Validation dialog box, switch to Error warning, select the style of the alert, or set the warning prompt at will.

How about this Excel data validation is not very useful? By the way, this function is also applicable in WPS, but in WPS it is called "Data validity".