VBA & Macro Problems

How to Highlight Duplicate Values Across Multiple Worksheets in Excel

Partner Editor Sep 28, 2026 870 views

Question details

The user needs a method to identify and highlight duplicate invoice numbers in column B across multiple Excel worksheets.

How to Highlight Duplicate Values Across Multiple Worksheets in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking and highlighting duplicate data entries dynamically across yearly worksheets in a single workbook.
Observed behavior: Excel's standard conditional formatting does not natively support highlighting duplicate values across multiple worksheets, requiring a macro or workaround.

Before you start

Before running a VBA script that applies fill colors across multiple sheets, save a backup copy of your workbook. Macros clear the Undo history, meaning you cannot revert the color changes automatically if the script highlights the wrong cells.

Solution 1Recommended

Use a VBA Macro with a Dictionary to Highlight Duplicates

This solution uses a VBA macro that tracks values in a Scripting.Dictionary across all worksheets and applies a background color to any value that appears more than once.

A VBA solution is highly recommended for dynamically tracking duplicates across different tabs. When you add new yearly worksheets, the macro will automatically include them in the scan without requiring code updates.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click 'Insert' from the top menu, then select 'Module' to create a blank workspace for your script.

Paste the VBA Code

Write or paste a VBA script that utilizes 'CreateObject("Scripting.Dictionary")' to loop through 'For Each ws In ThisWorkbook.Worksheets'. Set it to read Column B, ignoring blanks and summary sheets, and apply an 'Interior.Color' to duplicate instances.

Run the Macro

Press F5 or click the 'Run' button. The script will instantly scan all specified worksheets and highlight duplicate invoice numbers.

Use a VBA Macro with a Dictionary to Highlight Duplicates

Dynamic Updates: This VBA approach ensures that as you add new tabs for upcoming years, the script will automatically process them without manual formula adjustments.

Solution 2

Consolidate Data into a Summary Sheet

If you prefer not to use VBA, you can consolidate your data into one master sheet and use Excel's built-in conditional formatting.

Seamless Excel Alternative

Easily Run VBA Macros and Highlight Duplicates in WPS Office

WPS Spreadsheet provides full support for VBA macros and advanced conditional formatting. It allows you to track and highlight duplicate entries across multiple worksheets effortlessly without heavy processing lags.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open the workbook containing your multiple data worksheets.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon to access advanced tools.
3. Open the VBA Editor: Click on the 'VBA Editor' icon or press Alt + F11 to open the macro environment.
4. Insert and Run the Code: Insert a new module, paste your dictionary-based VBA script, and click 'Run' to highlight cross-sheet duplicates automatically.

Full compatibility with Microsoft Excel VBA macros (.xlsm)Advanced conditional formatting rules for heavy data analysisFree and lightweight alternative for comprehensive data processingFamiliar UI with zero learning curve for Excel users

Frequently Asked Questions

Can standard Conditional Formatting highlight duplicates across different sheets?

No, Excel's built-in Conditional Formatting for duplicate values only evaluates data within the same worksheet or a specifically selected contiguous range. It cannot natively compare values spanning across multiple tabs.

How do I save a workbook that contains a VBA macro?

You must save the file as an Excel Macro-Enabled Workbook. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu to ensure your code is retained.

How can I exclude certain summary sheets from the VBA macro duplicate check?

Within your VBA code loop, you can add an IF statement (e.g., `If ws.Name <> "Summary" Then`) to explicitly skip specific worksheets when the macro evaluates Column B for duplicate values.

Excel & Data Problems