WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

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

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to highlight and compare duplicates in two columns in excel

March 4, 2022
24.7K Views

Welcome to today’s tutorial, after studying today’s trick about how to highlight and compare duplicates in excel, you will become a confident WPS Spreadsheet user.

· Compare two columns and highlight duplicates in excel

When processing mass data, it is hard to find the duplicates, let alone highlight them. To help out, WPS Spreadsheet may assist by comparing data in rows and columns with easy steps.

Suppose we want to compare the duplicate names in these two classes. What should we do?

1. Select the data area, and then click the Data tab > Highlight Duplicates >  Set.

2. A dashed box will automatically select the table area. If there are repeated contents in cells in the selected area, the cells will be set in orange background.

If you want to change the data range, please drag to select the desired data area.

Finally, click OK to preview the effect.

· After comparing these two columns, cancel the highlighted duplicatesin excel

To clear the highlighted duplicates, select the data area, and then click the Data tab > Highlight Duplicates > Clear.

· After comparing these two columns, remove the highlighted duplicates in excel

To remove the highlighted duplicates, select the data area, and then click the Data tab > Highlight Duplicates > Remove Duplicates.

In the pop-up dialog, you can check columns that contain the duplicates. Suppose we want to remove the duplicates in class 2, click the Data tab > Highlight Duplicates > Remove Duplicates, uncheck Class 1.

Finally, click Remove Duplicates to finish the process.

Note: The duplicates that from two different columns cannot be recognized.

In today’s free WPS Office tutorial, we have learned a simple yet useful way to compare and make duplicates in two columns stand out. Similar to Microsoft Office, WPS Spreadsheet is available for Windows, PC, Mac, Linux, Android, and iOS devices.

WPS Office Spreadsheet not only covers all of the basic functions of Excel, but also enables advanced techniques for handling duplicates. WPS Office Academy teaches strategies for free.

· To explore more about WPS Office:

Click here to learnhow to compare two sheets for differences

Click here to know how to compare two excel sheets and highlight differences

Click here to how to compare two sets of data

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