Formatting Issues

How to Highlight Duplicate Values Across Excel Worksheets

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to highlight specific values in one worksheet when those identical values appear in designated columns of other worksheets within the same workbook.

Product: Excel
Device & OS: not provided
Scenario: Identifying and visualizing duplicate data entries across multiple worksheet tabs using conditional formatting.
Observed behavior: Requires a valid conditional formatting rule or alternative method capable of cross-referencing ranges on separate sheets.

Before you start

Verify that all worksheet names in your workbook exactly match the ones you plan to use in your formula, paying close attention to spaces and special characters.

Solution 1Recommended

Use Conditional Formatting with OR and COUNTIF Formulas

Apply a custom conditional formatting rule utilizing the COUNTIF function to check multiple worksheets simultaneously.

This is the most direct approach. By combining the OR function with COUNTIF, Excel evaluates whether the target cell exists in any of the referenced external sheets. If the count is greater than zero, the conditional formatting triggers.

Select the target range

Go to the 'TIDs' sheet and highlight the data range in column A where you want the duplicates to be formatted. Note the first cell of your selection (e.g., A2).

Open Conditional Formatting

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

Enter the formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =OR(COUNTIF('D Subs'!B:B,A2)>0,COUNTIF('J Subs'!B:B,A2)>0). Make sure to adjust 'A2' to match the very first cell of your highlighted range.

Apply formatting

Click the 'Format' button, select a fill color (like yellow or red) to highlight the duplicates, and click 'OK' to apply the rule.

Handling Sheet Names with Spaces: If your worksheet names contain spaces (like 'D Subs'), you must enclose the sheet name in single quotation marks within the formula.

Solution 2

Identify Missing Values Using XMATCH

Use the XMATCH function within a formula to specifically highlight values that do not exist on your target list.

Solution 3

Merge Data Using Power Query

For massive datasets, use Power Query to merge tables and identify duplicates without slowing down your workbook.

Efficient Spreadsheet Management

Highlight Cross-Sheet Duplicates Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced multi-sheet formulas for conditional formatting, making it simple to highlight duplicates across multiple tabs without performance lag.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
2. Highlight the target column: Select the range of cells where you want the duplicate highlights to appear.
3. Add a new rule: Click on 'Conditional Formatting' under the Home tab, then select 'New Rule' > 'Use a formula to format cells'.
4. Apply the cross-sheet formula: Paste your COUNTIF formula, choose your highlight color, and click OK to instantly view duplicates.

Fully compatible with Microsoft Excel formulas including COUNTIF, OR, and XMATCH.Lightweight architecture ensures smooth performance even with thousands of rows of data.Intuitive interface for managing complex conditional formatting rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula highlighting the wrong cells?

This usually occurs if the cell reference in your formula does not match the first cell of your highlighted range. For instance, if you highlight A2:A100 but your formula starts with A1, the formatting will be offset by one row. Always ensure the relative reference matches the active starting cell.

Can I highlight duplicates across more than two external sheets?

Yes. You can expand the OR function to include as many COUNTIF statements as you need. For example: =OR(COUNTIF(Sheet2!A:A, A2)>0, COUNTIF(Sheet3!A:A, A2)>0, COUNTIF(Sheet4!A:A, A2)>0).

Will referencing entire columns like B:B slow down my Excel file?

Referencing entire columns inside complex conditional formatting rules can cause performance issues in large workbooks. If you experience lag, try restricting the formula to the exact data range, such as 'D Subs'!$B$2:$B$1000 instead of 'D Subs'!B:B.

How do I remove the conditional formatting rule later?

Go to the Home tab, click Conditional Formatting, and select 'Clear Rules'. You can choose to clear rules from the currently selected cells or from the entire sheet.

Document & File Issues