How to Combine Repeated Data in Excel

Wrangling duplicate entries in Excel is one of those tasks every user faces, whether you're a marketer tracking campaign leads or a manager summarizing weekly sales. Seeing the same product, name, or ID listed multiple times can be frustrating, but finding a way to combine that repeated data is essential for accurate analysis. This guide will walk you through several effective methods to consolidate and combine duplicate rows in your spreadsheets.

Graphed

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.

Build Your First Agent

First, What Does "Combining Repeated Data" Mean?

Combining repeated data simply means taking multiple rows that share a common piece of information and merging them into a single, summarized row. Usually, this involves aggregating numerical data (like summing sales figures) or joining text data (like merging notes).

For example, imagine you have this sales log:

Your goal is to get a clean summary, combining the sales for each unique product, like this:

Let's look at a few practical, easy-to-follow methods to achieve this in Excel.

Method 1: Use the Consolidate Feature

Excel has a built-in feature called Consolidate that is perfect for this exact task. It’s a bit of a hidden gem but is incredibly fast once you know how to use it. This method works best when your data is structured in a simple table without too much complexity.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Step-by-Step Guide to Consolidate

1. Select a Destination Cell: Start by clicking a blank cell where you want your new, summarized table to appear. It’s best to choose a cell on the same sheet away from your original data or on a new worksheet entirely.
2. Open the Consolidate Window: Navigate to the Data tab on the Ribbon. In the Data Tools group, click on the Consolidate icon.
3. Choose Your Function: In the Consolidate dialog box that appears, you first need to pick how you want to combine the data. The default is Sum, which is usually what you want. However, you can also select from other functions like Count, Average, Max, or Min from the dropdown menu.
4. Add Your Data Range: Click inside the Reference box. Then, click and drag to select your entire data set, including the column headers (e.g., 'Product' and 'Sales'). After selecting your data, click the Add button. Your selected range will now appear in the 'All references' box below.
5. Define Your Labels: At the bottom of the dialog box, under Use labels in, check both the Top row and Left column boxes. This tells Excel to use the headers from your top row and the unique items from the first column (your products, in this example) to organize the consolidated data.
6. Click OK: Once you've done that, click OK. Excel will instantly generate a new summary table in the location you chose, with all the repeated data combined according to your chosen function.

Pro-Tip: There's also an option to Create links to source data. If you check this box, Excel will create a summary table that directly links back to the original cells. If the original data changes, you can update your summary table. However, it also adds expandable row groups, which can look a bit cluttered. For a simple, static summary, leave this box unchecked.

Method 2: Use a PivotTable

When it comes to summarizing, aggregating, and analyzing data in Excel, PivotTables are the undisputed champion. They are more powerful and flexible than the Consolidate feature and offer a dynamic way to interact with your data. Don't be intimidated if you're new to them, creating a basic PivotTable for combining data is surprisingly simple.

How to Create a PivotTable for Combining Data

1. Prepare Your Data: Ensure your data is in a proper tabular format—no empty rows or columns within the dataset. Each column should have a unique header.
2. Insert the PivotTable: Click on any single cell inside your data range. Go to the Insert tab on the Ribbon and click on PivotTable.
3. Confirm the Details: The Create PivotTable dialog will pop up. Excel usually auto-selects your data range correctly. You can choose to place the PivotTable in a New Worksheet (recommended for keeping things clean) or an Existing Worksheet. Click OK.
4. Arrange the Fields: On the new worksheet, you'll see a blank PivotTable on the left and a PivotTable Fields pane on the right. This pane lists all your column headers. Now, you just need to tell the PivotTable what to do:

That's it! The PivotTable will automatically sum the sales for each product, giving you the exact summary you need. The best part is its flexibility. Want to see the average sales instead of the sum? Simply click on 'Sum of Sales' in the Values area, select Value Field Settings, and choose 'Average' or any other calculation.

You can also drag the 'Region' field into the Columns area to get a breakdown of sales by product and region. PivotTables make exploring your data effortless and are by far the most robust way to handle this task.

Graphed

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.

Build Your First Agent

Method 3: Use Excel Formulas for a Custom Solution

For those who prefer the direct control and dynamic nature of formulas, combining data is just a matter of using the right functions. This method is slightly more hands-on but provides a solution that automatically updates as your source data changes. The primary combination you'll use is a function to get a unique list of items, and another to perform the calculation.

Step 1: Get a List of Unique Items

First, you need a list of the unique items you want to summarize (e.g., one instance of 'Laptop', 'Keyboard', and 'Monitor').

• If you have a newer version of Excel (Microsoft 365 or 2021): The UNIQUE function is your best friend. In a blank cell, type:
• If you are on an older version of Excel: You can use the Remove Duplicates feature. First, copy the column with your repeated data and paste it somewhere else. With the pasted data selected, go to the Data tab and click Remove Duplicates. Click OK, and you'll be left with a unique list.

Step 2: Aggregate the Data with a Formula

Once you have your unique list, you can use a formula to sum the corresponding values. The SUMIF function is perfect for this.

=SUMIF(criteria_range, criteria, sum_range)

• criteria_range: The column in your original data with the repeated items (e.g., the 'Product' column, A:A).
• criteria: The unique item you want to sum up (e.g., the first cell of your unique list, G2).
• sum_range: The column in your original data with the numbers to add (e.g., the 'Sales' column, C:C).

So, next to the first item ('Laptop') in your unique list, you would type:

=SUMIF(A:A, G2, C:C)

Press Enter, then click on the corner of the cell and drag the fill handle down to apply the formula to the rest of your unique products. This dynamically calculates the total sales for each item. If you need to count occurrences, use COUNTIF, and for averaging, use AVERAGEIF.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

How to Combine Repeated Text Data

What if you aren't adding numbers but joining text? For instance, you have multiple entries for one project and want to combine all the related notes into a single cell.

Let's say your data looks like this:

You want to get a single row for 'Launch Campaign' with all of its notes combined. For this, the TEXTJOIN function is incredibly powerful.

First, create your unique list of projects like we did in the formula method. Then, next to your first unique project (e.g., in cell D2), use this formula:

=TEXTJOIN(", ", TRUE, IF($A$2:$A$5=D2, $B$2:$B$5, ""))

Here’s what this does:

• ", " is the delimiter — it puts a comma and a space between each joined note.
• TRUE tells the function to ignore any empty cells.
• The IF function checks which project names in your original table ($A$2:$A$5) match the unique project name in your list (D2). If they match, it grabs the corresponding note from column B, if not, it returns an empty string.
• Finally, TEXTJOIN stitches together all the matching notes it found.

Note: If you are using an older version of Excel, you may need to press Ctrl + Shift + Enter to enter this as an array formula.

Final Thoughts

Choosing the right method for combining data in Excel really depends on your specific needs. The Consolidate feature offers a quick and simple solution, PivotTables provide unmatched flexibility for deeper analysis, and formulas like SUMIF and TEXTJOIN give you precise, dynamic control. Pick the one that feels most comfortable for you and best suits your project.

While these Excel techniques are great for managing data that's already in a spreadsheet, the bigger challenge often lies in getting all your data in one place to begin with. Many teams spend hours manually exporting reports from Google Analytics, Salesforce, Shopify, and various ad platforms just to consolidate them manually. That’s why we built Graphed. It automates the entire process by connecting directly to your data sources, letting you create dashboards and get insights in seconds with simple, natural language prompts — no more CSVs or spreadsheet wrangling needed.