How to Find, Highlight, and Remove Duplicates Across Excel Worksheets in WPS Office
Question details
The user wants a practical way to compare data ranges on two Excel worksheets, identify duplicate values, highlight matches, and potentially remove duplicate records.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Comparing two worksheet ranges to find matching values and clean up duplicate records.
- Observed behavior
- Duplicate values across the two worksheets are not automatically flagged or removed, so the user needs a structured method to identify and handle them.
Make sure both worksheets are open or accessible in the same workbook, and check that the columns you want to compare have a consistent data format before applying rules or formulas.
Highlight Duplicate Values Across Worksheets with Conditional Formatting
Best for quickly seeing which entries in one sheet also appear in another sheet.
Conditional formatting applies a visual highlight without changing the underlying data, which makes it easy to review matches before deciding whether to delete them.
Open the worksheet where you want to highlight duplicates and select the range, for example A2:A100.
Go to Home > Conditional Formatting > New Rule, then choose "Use a formula to determine which cells to format".
In the formula box, enter =COUNTIF(Sheet2!$A$2:$A$100,A2)>0 and select a fill color or highlight format.
Click OK to apply the rule. Cells in the selected range that also exist in Sheet2 are highlighted.

List Duplicates and Unique Values with a COUNTIF Formula
Use when you need a column that explicitly marks each row as Duplicate or Unique before filtering or sorting.
Combine Data and Remove Duplicate Records in WPS Spreadsheets
Use when you have already copied both datasets into one sheet and want a direct cleanup of duplicate rows.
Find and Remove Duplicates Across Excel Worksheets in WPS Office
WPS Spreadsheets gives you built-in conditional formatting, formulas, and a Remove Duplicates tool to identify and clean duplicate records across Excel worksheets without changing your original file format.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the two worksheets you want to compare.
- 2. Select the range to compare: Click the worksheet tab that has the first range, then select the cells where duplicates should be highlighted or marked.
- 3. Apply conditional formatting: Go to Home > Conditional Formatting > New Rule, use a COUNTIF formula referencing the second sheet, and choose a highlight color.
- 4. Remove duplicate rows after review: If you need to delete duplicates, copy the reviewed data into one sheet and use Data > Remove Duplicates.
- 5. Save as Excel-compatible file: Press Ctrl+S or use Menu > Save As and keep the .xlsx format so the file remains compatible with Microsoft Excel.

Frequently Asked Questions
Can I highlight duplicates across two separate worksheets without copying data?
Yes. Select the first range, go to Home > Conditional Formatting > New Rule, use a formula such as =COUNTIF(Sheet2!$A$2:$A$100,A2)>0, and choose a highlight format.
How do I remove only the duplicate rows after comparing two worksheets?
Add a helper column with =IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Duplicate","Unique"), filter the Duplicate rows, or copy both ranges into one sheet and use Data > Remove Duplicates.
Does WPS Spreadsheets include Power Query like Microsoft Excel?
WPS Spreadsheets does not have the same Power Query interface, but you can combine data manually and use the built-in Data > Remove Duplicates tool or formulas to achieve the same comparison.
Will conditional formatting and duplicate removal work with Microsoft Excel .xlsx files?
Yes. WPS Spreadsheets opens and saves .xlsx files and preserves conditional formatting rules, formulas, and data tools so you can share the workbook with Excel users.
What should I check when my cross-sheet formula is not highlighting matches?
Verify that the sheet name in the formula is spelled exactly as the worksheet tab, wrap names with spaces in single quotes, and confirm that both columns have matching data types, such as text or numbers.




