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


More features in Remove Duplicates

Uploaded time: December 24, 2021 Difficulty Beginner

More features in Remove Duplicates

More features in Remove Duplicates

To remove the duplicate data when dealing with tabular data, we can use the function of Remove Duplicates.

In Microsoft, we can select the needed cell range and click the Remove Duplicates button in the Data tab to remove them.

In WPS Spreadsheets, in addition to removing duplicates, we can also mark them for further review and collation.

1. Click the Data tab, click the Highlight Duplicates drop-down button, and select Set.

2. Then select the cell range that needs to be marked and click OK.

3. Then the duplicate data in the table are highlighted with a fill color.

Besides, WPS Spreadsheets supports managing duplicates. With this feature, in addition to marking the duplicates of one cell range, we can also mark those of two cell ranges or even two worksheets.

Click the Manage Duplicates drop-down button, and there are two features, Highlight and Fetch, for highlighting and fetching duplicates or unique values.

· Highlight Duplicates

Click the Manage Duplicates drop-down button, select Highlight duplicates, and set the filtering range in the pop-up dialog.

For example, I want to highlight all the duplicates in a single worksheet.

Select the Within the Worksheet and click Confirm to Mark. Then all the copies of Sheet1 are highlighted to make the data more intuitive.

We can also filter the data of different ranges according to actual needs.

For example, select Two Ranges to filter the data of two ranges,

or select Two Worksheets to filter that of two worksheets.

· Highlight Unique Values

Now I need to compare these two data tables. So it would be very troublesome if I needed to compare their data one by one with the naked eyes.

At this point, we can highlight the unique values of the two data ranges.

1. Click the Manage Duplicates drop-down button, select Highlight unique values, and select Two Ranges in the pop-up dialog.

2. Select the cell range of B2:D10 in Range1 .

3. Select the cell range of G3:I12 in Range 2.

4. Click Confirm to Mark, and the different values in these two ranges are highlighted.

We can also highlight the unique values of the two worksheets to find out the differences.

Click Two Worksheets, check Two Worksheets 1 and Two Worksheets 2 and click Confirm to Mark. Then the unique values of these two worksheets are highlighted obviously.

Apart from highlighting, WPS Spreadsheets also supports fetching needed data.

The steps are simple. For example, we want to fetch unique values by removing duplicates.

Select the desired cell range, select Fetch unique values, and click Export to new Sheet.

In general, this function provides more options for our selection. Did you get it? 

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