WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android


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

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 theDatatab.

3. Click theValidationdrop-down button.

4. Select theValidationoption. Then theData Validationdialog box will pop up.

Here you can set the required type of input data, click theSettingsbutton, selectText lengthin theAllowoption, and selectequal toin theDataoption area. Then enter9inValue.

When we need to display the input information of the selected cell area, click theInput Messagebutton first. EnterPassport NumberinTitle, and enter theValue length: 9 digitsinInput.

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

1. Check theShow error alert after invalid data is enteredinError Alert. We can set an error report if the input data is inconsistent with our requirements.

2. SelectStyleasStop.

3. EnterFill in errorinTitle.

4. EnterRefill Passport NumberinError 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 appropriateTitleandError Messagecontent according to our actual needs.

For instance, in theInsurance purchasedcolumn, we need to fill inYESandNO.

1. Click theValidationdrop-down button.

2. Then in theData Validationpopup dialog box.

3. Click theSettingsbutton.

4. SelectListat theAllowoption area.

5. EnterYESandNOin theSourceedit box.

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


In this way, when inputtingYESorNOinformation, we can directly select the input content.If we want to cancel the settings, just clickClear Allin theData Validationdialog 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.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.