Data Import & Export

How to Highlight Duplicate Serial Numbers Across Multiple Excel Sheets

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to identify and highlight duplicate serial numbers that appear across a large number of separate customer worksheets (approximately 94 sheets).

Product: Excel
Device & OS: not provided
Scenario: Managing customer serial numbers distributed across nearly a hundred separate worksheets, where the serial numbers may not always be located in the exact same column.
Observed behavior: Using standard cross-sheet formulas is too cumbersome and negatively impacts calculation speed across 94 sheets, making it difficult to efficiently detect duplicates.

Before you start

Before attempting to consolidate 94 separate sheets, review your column headers across the worksheets to ensure the serial number columns share the exact same header name, which allows data queries to append them accurately regardless of their column position.

Solution 1Recommended

Consolidate Sheets with Power Query (Recommended)

Using Power Query to append all serial numbers into a single master table is the most efficient and scalable method, avoiding the severe performance issues caused by cross-sheet formulas.

When dealing with nearly 100 sheets, formulas recalculating across the entire workbook can freeze the application. Power Query extracts and merges the data effortlessly, automatically handling situations where the serial number is in column A on one sheet and column D on another, provided the header names match.

Launch Power Query

Navigate to the 'Data' tab on the Excel ribbon, click on 'Get Data', select 'From File', and then 'From Workbook'. Choose your current file to import the data.

Transform and Append

In the Navigator window, select multiple items (your 94 sheets) and click 'Transform Data'. In the Power Query Editor, use the 'Append Queries' feature to stack all the sheet tables into one unified list.

Filter Columns

Remove unnecessary columns by right-clicking the 'Serial Number' column header and selecting 'Remove Other Columns', leaving only the consolidated serial numbers.

Load to Worksheet

Click 'Close & Load' to output this consolidated list into a brand new worksheet within your workbook.

Apply Conditional Formatting

Select the newly generated column of serial numbers. Go to the 'Home' tab, click 'Conditional Formatting', navigate to 'Highlight Cells Rules', and choose 'Duplicate Values' to instantly mark repetitions.

Same-Sheet Duplicates Addressed: By consolidating all data into a single table first, this method guarantees that duplicates originating from the very same sheet are also caught and highlighted.

Solution 2

Use Cross-Sheet Formulas (For Smaller Workbooks)

This method is functionally possible using native formatting rules, though it is only advisable for workbooks with a very small number of sheets due to heavy calculation loads.

Streamlined Data Consolidation

Easily Find Duplicates Across Worksheets in WPS Office

WPS Spreadsheet offers powerful data management tools designed to handle large workbooks seamlessly. You can easily consolidate data from dozens of sheets and apply intuitive conditional formatting to detect identical serial numbers without experiencing software lag.

1. Consolidate Your Data: Open your workbook in WPS Spreadsheet, go to the 'Data' tab, and utilize the 'Consolidate' function to gather your serial numbers into a single master column.
2. Select the Consolidated Range: Highlight the newly created master column containing all the aggregated serial numbers.
3. Highlight Duplicate Values: Navigate to the 'Home' tab, click 'Conditional Formatting', select 'Highlight Cells Rules', and click 'Duplicate Values' to format all overlapping entries.

Seamlessly consolidate large datasets from multiple worksheetsHighly compatible with all Microsoft Excel (.xlsx) formats and formulasLightweight architecture ensuring smooth performance even with 90+ sheetsIntuitive Conditional Formatting menu for instant visual highlights

microsoft office alternative - wps office

Frequently Asked Questions

Does highlighting duplicates across sheets also check for duplicates on the same sheet?

Yes. When you use the recommended Power Query method to consolidate your data into a single table, the Conditional Formatting 'Duplicate Values' rule will scan the entire unified list. This flags any repetition, regardless of whether the duplicates originated from different sheets or the exact same sheet.

Can I highlight duplicates if the serial numbers are in different columns on each sheet?

Yes, using Power Query handles this easily as long as the column headers are identical (e.g., 'Serial Number'). When appending queries, it aligns the data by the header name, completely ignoring whether it was in column A on one sheet and column F on another.

Why shouldn't I just use a cross-sheet COUNTIF formula for 94 sheets?

A cross-sheet formula requires the software to actively search through 94 separate grids simultaneously every time any cell is edited. This massive calculation load will cause severe software lag, making your workbook incredibly slow and prone to crashing.

Excel & Data Problems