How can we use the data validation feature

Uploaded time: August 27, 2021 Difficulty: Beginner

How can we use the data validation feature

How can we use the data validation feature

How can we use the data validation feature

We can greatly improve the accuracy of data entry in our work if we use the data validation feature of WPS Spreadsheet.


Take this table as an example. This is the information collection form for US citizens joining tour groups for international travel. We need to fill in the information of all the participants. How can we ensure that the passport numbers and phone numbers we entered will not be wrong?


1. First, select the cell area where data needs to be filled.

2. Click the Data tab.

3. Click the Validation drop-down button.

4. Select the Validation option. Then the Data Validation dialog box will pop up.

Here you can set the required type of input data, click the Settings button, select Text length in the Allow option, and select equal to in the Data option area. Then enter 9 in Value.



When we need to display the input information of the selected cell area, click the Input Message button first. Enter Passport Number in Title , and enter the Value length: 9 digits in Input. 


After entering all this information, we are now coming to the most essential step.

1. Check the Show error alert after invalid data is entered in Error Alert. We can set an error report if the input data is inconsistent with our requirements.

2. Select Style as Stop.

3. Enter Fill in error in Title.

4. Enter Refill Passport Number in Error Message , then click OK.


In this way, when the length of the input text does not match, it will remind us of the input error. By this, we can improve the accuracy of the input data. We can also set appropriate Title and Error Message content according to our actual needs.



For instance, in the Insurance purchased column, we need to fill in YES and NO.

1. Click the Validation drop-down button.

2. Then in the Data Validation popup dialog box.

3. Click the Settings button.

4. Select List at the Allow option area.

5. Enter YES and NO in the Source edit box.

6. Use a comma to separate them, and click OK.


In this way, when inputting YES or NO information, we can directly select the input content. If we want to cancel the settings, just click Clear All in the Data Validation dialog box.



This skill can greatly help us to improve the accuracy of data entry. Did you get it? To be office excel advancers, you could learn how to use WPS Office Spreadsheet online in WPS Academy.