Data Import & Export

How to Find, Highlight, and Remove Duplicates Across Excel Worksheets in WPS Office

Natalie TaylorNatalie Taylor Nov 13, 2026 871 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the range to compare

Open the worksheet where you want to highlight duplicates and select the range, for example A2:A100.

2
Create a conditional formatting rule

Go to Home > Conditional Formatting > New Rule, then choose "Use a formula to determine which cells to format".

3
Enter a cross-sheet COUNTIF formula

In the formula box, enter =COUNTIF(Sheet2!$A$2:$A$100,A2)>0 and select a fill color or highlight format.

4
Apply and review highlighted matches

Click OK to apply the rule. Cells in the selected range that also exist in Sheet2 are highlighted.

Highlight Duplicate Values Across Worksheets with Conditional Formatting
Duplicates are highlighted: The highlighted cells show matches from the second worksheet. You can filter by color or manually delete the rows after review.

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. 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. 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. 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. 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. 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.
Free to use for comparing Excel .xlsx worksheetsHighlights matching values across sheets with conditional formattingMaintains full compatibility with Microsoft Excel filesFamiliar ribbon interface with easy Data toolsLightweight and simple for daily duplicate cleanup
microsoft office alternative - wps office

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.