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


Compare two sheets for duplicate rows in WPS Office Excel

January 27, 2022

Welcome to WPS Official Academy to check the contents of how to compare two excel spreadsheets for duplicate rows.

It would be a waste of time to search for duplicate rows by comparing each two rows manually. WPS Office provides a function of Highlight Duplicates to help you compare two sheets for duplicate rows quickly. Let’s have a try step by step.

Here we take two lists of participants as examples. We want to find out the participants who have participated in both conferences by highlighting the duplicate rows in these two sheets.

View side by side for easier comparison

For convenience, we can display two sheets side by side to avoid switching back and forth. Here are the simple steps.

Since the two sheets belong to the same workbook, we need to open a new window first by clicking the View tab > New Window.

Two options are available to display two sheets side by side.

Option 1

1. Go to one of the two windows, and click the View tab > Arrange All.

2. You can choose from Horizontal Text Box, Vertical Text Box and Cascade as needed. Here we take Vertical Text Box as an example.

In this way, you are able to edit or scroll these two sheets respectively.

Option 2

1. Go to one of the two windows, and click the View tab > View Side by Side.

2. You can select Synchronous Scrolling to scroll the two sheets at the same time or deselect it as needed. And you can also click Reset Position to reset the windows with one click.

Highlight duplicates rows quickly for comparison

The Manage Duplicates function can help to highlight duplicates quickly for efficient comparison. Steps are as follows:

1. Navigate to the Data tab > Manage Duplicates > Highlight Duplicates.

2. Since the data are in two sheets, we choose Two Worksheets and check “Conference 1” and “Conference 2.”

3. Choose a desired color and click Confirm to Mark.

Then the duplicates rows in these two sheets are highlighted in the selected color, which is conducive for us to compare these two sheets quickly and easily.

Following these steps, you are able to compare two excel spreadsheets for duplicate rows quickly and freely. Did you get it?

l What is Manage Duplicates?

The Manage Duplicates function is to quickly highlight or fetch duplicates or unique values. With this function, we can avoid comparing data or fetch needed data with naked eyes, which greatly improves our work efficiency.

l What is WPS Spreadsheet?

WPS Spreadsheet is a module of WPS Office (an acronym for Writer, Presentation and Spreadsheet). WPS Spreadsheet is consistent with Excel document, which helps users to work with high efficiency. Its personal basic version is free to use and it also provides abundant functions and unique features for you to explore.

Learn more advanced skills about comparison in WPS Spreadsheet:

The Camera in WPS Spreadsheet | WPS Academy Free Office Courses

How to compare two excel sheets side by side| WPS Academy Free Office Courses

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

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