Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Consolidate Duplicate Data in Excel

Cody Schneider••6 min read

Cleaning up a messy spreadsheet is one of the most critical steps for accurate analysis. When the same customer, product, or lead exists on multiple rows, your data tells a fragmented story, making it impossible to see the whole picture. This guide will walk you through several practical methods in Excel to consolidate duplicate data, turning a scattered list into a clean, single source of truth.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

What Exactly Does It Mean to Consolidate Data?

First, let's clarify what we mean by "consolidating." This isn't just about clicking "Remove Duplicates." That feature gets you a unique list, but you lose all the valuable information from the rows you removed. True consolidation is a smart merge: combining all relevant information from multiple rows into one single, master row.

Imagine you have this data scattered in your sheet:

If you just remove duplicates based on Jane's email, you'll be left with only the first row - losing her scarf and gloves purchases. Consolidation aims to create a single row that looks more like this:

This consolidated view gives you a complete picture of Jane's relationship with your business, which is far more valuable for any kind of reporting or analysis.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 1: Find and Identify Your Duplicates

Before you can consolidate, you need to find where your duplicates are. Using an identifier column like an email address or customer ID, here are two simple ways to flag them.

Method A: Highlight Duplicates with Conditional Formatting

Conditional Formatting is the fastest way to get a visual sense of your duplicates. It works best for quick spot-checks on smaller datasets.

  • Select the entire column containing your unique identifier (e.g., column B for Email).
  • Navigate to the Home tab on the Excel ribbon.
  • Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.
  • Choose a formatting style (the default red fill works well) and click OK.

Now, every cell in that column that has an identical counterpart somewhere else will be highlighted, giving you an instant map of where the duplicates are located.

Method B: Count Duplicates with a COUNTIF Formula

For a more functional approach that lets you sort and filter, a helper column with the COUNTIF formula is your best bet.

  • Insert a new column and name it something like “Duplicate Count.”
  • In the second row of this new column (e.g., E2), enter the following formula, assuming your emails are in column B:
  • Press Enter, then click the small square (the "fill handle") at the bottom-right of the cell and drag it down to apply the formula to the rest of the column.

This formula counts how many times each email in column B appears. If your “Duplicate Count” is higher than 1, then you have identified that the email repeats. You can now easily filter this column to focus your attention on the duplicate rows for more efficient data management.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Method 2: Manually Consolidate with 'Sort & Filter'

For smaller datasets, manual consolidation might be feasible. Here's how it works:

  1. Sort Data by Identifier: Use your “Duplicate Count” column and sort your data based on your unique identifier, such as customer email. This will group duplicates together, making them easier to manage.
  2. Choose a Primary Row: For each set of duplicates, decide on a "master" row to retain. Copy any additional information from duplicate rows into this master row.
  3. Delete Redundant Rows: Once data consolidation is complete, delete the redundant rows that have already been accounted for in your master row.

Pros: Simple to execute for small datasets. No advanced skills required. Cons: Extremely time-consuming and prone to human error. Not practical for large datasets or ongoing data management tasks.

Method 3: Summarize Duplicates with a PivotTable

Sometimes, a summarized report is all you need. Creating a PivotTable can help you achieve this efficiently without modifying your source data.

  • Select your entire data range.
  • Go to the Insert tab and click PivotTable. Opt to place it in a new worksheet.
  • In the PivotTable Fields pane, drag and drop fields to these areas:

Pros: Fast, error-proof, and updates quickly with new data. Cons: Creates a summary rather than a detailed, consolidated dataset. Not ideal for database-style use.

Method 4: Automate Consolidation with Power Query

For large datasets or repeated tasks, Power Query is your go-to solution. It can automate consolidation, making your data management tasks much more efficient.

Step 1: Load Data into Power Query

  • Format your Excel data range as a Table (Insert → Table), ensuring it has a header row.
  • Select a cell within your table, navigate to the Data tab, and click From Table/Range to open the Power Query Editor.

Step 2: Group Your Data

Power Query will enable you to combine duplicate rows into single entries.

  • In the Power Query Editor, navigate to the Home tab and select Group By.
  • Choose your unique identifier (e.g., "Email") as the grouping field. For new columns:

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 3: Combine Text Data

Use Power Query's advanced editor and type:

= Table.Group(Source, {"Email"}, 
    {{"Total Spend", each List.Sum([Spend])},
     {"Products", each Text.Combine([Product], ", ")}})

This approach aggregates your dataset into a neatly consolidated table.

Step 4: Export the Results

After editing, select Close & Load To... to export the results as a new worksheet. Refresh the sheet as new data comes in.

Pros: Fully automated and handles large datasets. Minimizes manual errors and ensures consistency. Cons: Initial learning curve to grasp Power Query's features.

Final Thoughts

Understanding how to consolidate data effectively in Excel can greatly improve your decision-making by providing a single source of truth for your data. Excel offers a variety of tools to aid this process, from basic manual sorting to advanced techniques like Power Query.

Graphed takes data consolidation one step further by allowing you to integrate and analyze data from multiple sources in a seamless, intuitive way. This tool offers a unique advantage in a business world that relies increasingly on data-driven decisions. Sign up and explore its capabilities to elevate your data management strategies.

Related Articles