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


Clear and avoid duplicates entries in WPS Spreadsheet

Uploaded time: August 27, 2021 Difficulty Beginner

Clear and avoid duplicates entries in WPS Spreadsheet

Clear and avoid duplicates entries in WPS Spreadsheet

When using WPS Spreadsheet to process a large number of data, it would be troublesome to find duplicated items manually. Here are some tips to delete duplicates.

· Option One

Select a range of cells. Get into theDatatab, and click the triangle next toHighlight Duplicates. PickSetfrom the drop-down list. Duplicated cells within the selected range will be highlighted.To clear the highlight color, we just need to click theSetbutton.

· Option Two

Select cell range (A2:C14). Get into theDatatab, and click theRemove Duplicatesbutton. As we can see, some data in the 5th and 14th are duplicated. To delete them, clickSelect AllandRemove Duplicatesin the dialog boxRemove Duplicates.

How can we avoid inputting duplicated data?

Take this spreadsheet as an example.

1. Select the column starting withProduct Name.

2. ClickData.

3. ClicktheReject Duplicates Entriesdrop-down button.

4. ClickSet. Then, there will be a warning dialogReject Input Duplicates.

5. PressEnter.

In this way, the entries in the sheet can be continued.

If we want to avoid duplicated entries, what should we do?

1. Get into theDatatab and click the drop-down button next toValidation.

2. Now we can see the popup windowData Validation.

3. Choose theError Alerttab and changeStyletoStop.In this way, when we double-pressEnter,we cannot enter the duplicated items.

Finally, if we want to clear the related setting, click the triangle symbol of theReject Duplicates Entriesbutton, and chooseClearin the drop-down menu.

To be office excel advancers, we 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.