Formatting Issues

How to Highlight Duplicate Values Across Excel Sheets

Khadija Khan Oct 9, 2026 871 views

Question details

The user needs to compare values in column B across two different worksheets and highlight the cells that appear on both sheets, regardless of their order.

How to Highlight Duplicate Values Across Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Comparing data in specific columns across multiple worksheets to visually identify duplicates.
Observed behavior: Matching values appearing in column B on both Sheet1 and Sheet2 need to be dynamically highlighted for easy identification.

Before you start

Ensure both worksheets are within the same workbook and that you know the exact range of data (e.g., rows 2 to 500) in column B for both sheets so you can apply the formula correctly.

Solution 1Recommended

Use Conditional Formatting with the COUNTIF Function

Applying a COUNTIF formula within Conditional Formatting allows you to dynamically cross-reference and highlight matching data across multiple worksheets.

By using the COUNTIF function within a Conditional Formatting rule, you can instruct the software to check if a cell's value exists in a specified range on another worksheet. If the count is greater than zero, the condition is met, and your chosen formatting is applied automatically.

Select the target cells on the first sheet

Go to Sheet1 and select the populated cells in column B where you want duplicates to be highlighted (e.g., B2:B500).

Create a New Rule

Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the cross-sheet formula

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter =COUNTIF(Sheet2!$B$2:$B$500,B2)>0. Adjust the sheet name and row numbers to match your actual workbook data.

Apply formatting

Click the 'Format' button, go to the 'Fill' tab, choose your desired highlight color, and click 'OK' twice to apply the rule.

Repeat for the second sheet

To highlight the matching values on the second sheet as well, go to Sheet2, select the cells in column B, create a new rule, and use the formula =COUNTIF(Sheet1!$B$2:$B$500,B2)>0.

Use Conditional Formatting with the COUNTIF Function

Locking Cell References: Make sure to use absolute references (with dollar signs, like $B$2:$B$500) for the lookup range so it doesn't shift, but keep the criteria cell (B2) relative.

WPS Spreadsheet Solution

Highlight Duplicates Across Sheets Easily in WPS Office

WPS Spreadsheets provides robust Conditional Formatting features that work identically to Microsoft Excel. You can quickly compare data across multiple sheets using the exact same COUNTIF formulas without a steep learning curve.

1. Open your workbook: Launch WPS Spreadsheets and highlight the target column on your first sheet.
2. Access Conditional Formatting: Go to the Home tab, click Conditional Formatting, and choose New Rule.
3. Set the formula: Select 'Use a formula to determine which cells to format' and input your cross-sheet COUNTIF formula.
4. Apply and format: Set a background fill color to highlight the duplicates and click OK.

Free and lightweight spreadsheet software100% compatibility with Microsoft Excel (.xlsx) formatsSeamless execution of complex cross-sheet formulasFamiliar ribbon interface for a smooth transition

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting formula highlighting the correct duplicates?

This typically happens if your absolute and relative references are incorrect. Ensure the lookup range on the other sheet uses absolute references (e.g., $B$2:$B$500) and the lookup criteria cell uses a relative reference for the row (e.g., B2 without the dollar sign before the row number).

Does this method work if the values are not in the exact same order on both sheets?

Yes. The COUNTIF function searches the entire specified range on the target sheet, so it will accurately find and highlight duplicate values regardless of the order they appear in.

Can I use the exact same steps to compare entire rows instead of just one column?

To compare entire rows, you cannot use a simple COUNTIF on a single column. You would need to use the COUNTIFS function to check multiple criteria across several columns, or concatenate the row values into a single helper column before comparing them.

What if my sheet names contain spaces?

If your worksheet names contain spaces (e.g., 'Data Sheet 1'), you must enclose the sheet name in single quotation marks within the formula, like this: =COUNTIF('Data Sheet 2'!$B$2:$B$500,B2)>0.

Document & File Issues