How to Find Duplicate Rows Based on Multiple Columns in Excel

When dealing with large datasets, identifying duplicate rows across multiple columns is essential. Whether you’re managing product orders, c...

When dealing with large datasets, identifying duplicate rows across multiple columns is essential. Whether you’re managing product orders, customer data, or any other information, Excel provides powerful tools to find and manage duplicates. Let’s explore how to do it step by step:

1. Understanding the Problem

Consider a scenario where you have an order table with columns like “Product,” “Part Ordered,” and “Quantity.” You want to find rows where both the “Product” and “Part Ordered” values are duplicated. Here’s how to tackle this:

2. Using COUNTIFS Function

The COUNTIFS function allows you to count rows that meet specific criteria. In our case, we’ll use it to identify duplicate rows. Follow these steps:

    1. Add a New Column:

        o Insert a new column (let’s call it “Duplicate Count”) next to your data.

        o In cell D2 (assuming your data starts in row 2), enter the formula:

        =COUNTIFS(B:B, B2, C:C, C2)

        o This formula counts how often each row appears based on the values in columns B (Product)                 and C (Part Ordered).

    2. Copy the Formula Down:

        o Copy cell D2 and paste it down the entire column to calculate the duplicate count for each                         row.

    3. Identify Duplicate Rows:

        o Rows with a duplicate count greater than 1 indicate duplicates.

        o Use conditional formatting to highlight these rows for better visibility.

3. Conditional Formatting

       Now that you’ve counted the duplicates, let’s highlight them:

        1. Select Your Data:

           o Highlight the entire table (including the new “Duplicate Count” column).

        2. Apply Conditional Formatting:

            o Go to the Home tab.

            o Click on “Conditional Formatting” > “New Rule.”

            o Choose “Use a formula to determine which cells to format.”

            o Enter the formula:

             =$D2>1

            o Set the formatting style (e.g., background color) for duplicate rows.

    3. Review the Results:

        o Rows with a duplicate count greater than 1 will now be highlighted.

4. Clean Up Duplicates

        You’ve successfully identified duplicate rows. Now, you can decide how to handle them:

        • Remove Duplicates: Filter your data to show only duplicate rows and delete them.

        • Keep One Instance: If you want to keep one instance of each duplicate, manually review and delete the extras.

Remember, this method works latest versions of excel. Use it to efficiently manage your data and maintain accuracy. 🚀📊