To combine repeated labels and total their numbers in Excel, create a PivotTable: select your data, choose Insert > PivotTable, place the repeated field in Rows, and place the numeric field in Values. Excel groups matching labels and summarizes their values. If you only want to keep one copy of each entry, use Data > Remove Duplicates instead. Before starting, decide whether you need to add, count, or simply delete duplicates, because those actions produce different results.
What You’ll Need
- A worksheet with a header row and consistent columns, such as Product and Amount.
- A clear rule for what counts as a duplicate: matching a product name, an ID, or every field in a row.
- A backup copy of the original data, especially before deleting rows or changing the source.
- For the formula method, Excel for Microsoft 365 or Excel 2021 for the UNIQUE function. The PivotTable and Remove Duplicates methods work in current desktop versions of Excel.
Step-by-Step
- Choose what consolidation means for your data. If repeated customer or product names have separate amounts that should be added together, use a PivotTable or a SUMIF formula. To calculate how many times each label appears, use a count summary. If repeated rows are accidental and you only need one copy, remove duplicates. For records such as orders, matching a customer name alone does not necessarily mean the rows are duplicates; each order may be a separate transaction.
- Make a safe, clean copy of the source. Save the workbook, then copy the data to a new worksheet or duplicate the worksheet tab. Keep one header row, with a different header for each column, and remove completely blank rows inside the dataset. Check that numbers are stored as numbers rather than text. If the data is still being updated, consider converting it to an Excel table by selecting a cell and choosing Insert > Table. Confirm that My table has headers is selected.
- Use a PivotTable to group duplicates and summarize values. Click inside the source data or table, then choose Insert > PivotTable. Confirm the table or range and choose New Worksheet for the destination. Select OK. In the PivotTable Fields pane, drag the field you want to group by, such as Product, into Rows. Drag the numeric field, such as Amount, into Values. Excel normally uses Sum for numeric values, displaying one row per product and a total for each. For example, source rows Apples 10, Pears 6, Apples 5, Pears 4, Apples 2 produce Apples 17 and Pears 10.
- Check the PivotTable’s calculation. If the Values area says Count of Amount instead of Sum of Amount, click a value in the PivotTable, right-click, and select Summarize Values By > Sum. If you need the number of source records for each label, choose Count instead. For averages or other summaries, choose the relevant option in the same menu. Format totals as currency or a number if needed, using Home > Number.
- Create a formula summary if you want a result that updates in cells. This example assumes product names are in A2:A100 and amounts are in B2:B100, with headers in row 1. Enter this in E2 to produce a distinct list of product names:
=UNIQUE(A2:A100)
With the same source layout, and the distinct names spilling from E2, enter this in F2 to total each name:
=SUMIF($A$2:$A$100,E2#,$B$2:$B$100)
For the example rows above, the results next to Apples and Pears are 17 and 10. The # after E2 refers to the full spilled list created by UNIQUE, so the totals spill alongside it. Leave the cells below and to the right of these formulas empty; occupied cells in the spill area cause a #SPILL! error. UNIQUE and dynamic arrays are available in Microsoft 365 and Excel 2021. In older versions, use a PivotTable or create a unique list with Remove Duplicates, then use SUMIF for each listed label.
- Remove repeated entries when you do not need to aggregate their values. Select a cell in the copied dataset and choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare. To keep one row per Product, select only Product; to remove only identical complete records, select all relevant columns. Keep My data has headers checked if the first row contains headings, then select OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the result because this action deletes rows from the selected range.
- Refresh or verify the finished result. Compare a few grouped totals with the source rows and check the grand total. If the source data changes, right-click the PivotTable and choose Refresh. A formula summary recalculates when its source cells change, but the fixed A2:A100 range will not include new rows beyond row 100; enlarge the ranges or use an Excel table so added records are included. Save the workbook after confirming the output.
Common Mistakes
Removing rows when you meant to add values. Remove Duplicates keeps one selected record; it does not sum amounts from discarded records. Use a PivotTable or SUMIF when every repeated row contributes to a total.
Selecting the wrong comparison columns. If you select every column, two records with the same product but different amounts are not duplicates. If you select only Product, Excel treats all rows with the same product as duplicates, even when their other fields differ. Choose columns according to the actual identity rule for your data.
Overlooking inconsistent text. Excel may treat entries with leading or trailing spaces as different labels. For instance, Apples and Apples followed by a space can form separate groups. Standardize labels before summarizing; a helper column using TRIM can remove ordinary extra spaces. Also check spelling variations such as North East and Northeast.
Summarizing the wrong field. A PivotTable may count a field instead of summing it if the numbers are stored as text or the Values field is configured as Count. Check the source data type and the label in the Values area before relying on the totals.
Overwriting the original data. Removing duplicates cannot be reversed with a simple formula change. Work on a copy and verify the selected range before confirming the dialog.
Tips
- Use a unique ID rather than a name when records represent people, products, or accounts. Two people can share a name, and one person can have multiple valid records.
- Sort the copied data before using Remove Duplicates if you want a particular record to survive. Excel keeps the first occurrence it encounters in the range.
- Turn the source into a table before building a PivotTable if rows will be added over time. After adding rows, refresh the PivotTable and confirm its source includes the new records.
- Keep a separate summary sheet for PivotTables and formula results. This makes it easier to compare grouped totals with the source without changing the underlying records.
- When consolidating dates, group or standardize them intentionally. Dates that include different days will appear as separate items unless grouped by month, quarter, or year in the PivotTable.
- In Excel for Mac, the same core features are available; use the Insert and Data tabs for PivotTable and Remove Duplicates commands. Ribbon layouts can differ slightly by version. Excel for the web also supports these common tasks, though dialog and pane layouts may vary.
Troubleshooting
The PivotTable shows separate groups for what look like identical labels: inspect the source for extra spaces, spelling differences, nonprinting characters, or inconsistent punctuation. Clean the source in a helper column, then build the PivotTable from the cleaned values. If necessary, copy the cleaned results and use Paste Special > Values before summarizing.
The PivotTable is counting instead of adding: verify that the source values are numeric. A number imported as text may not sum correctly. Convert the source values to numbers, refresh the PivotTable, and set the field to Summarize Values By > Sum. If blanks or text are meaningful, correct those entries rather than forcing a total.
The formula returns #SPILL!: clear any cells in the area where the UNIQUE list or SUMIF results need to appear. Also check that the formula is not inside an Excel table, because dynamic array results need room to spill into neighboring worksheet cells.
The formula returns zero or misses some entries: check that the names in the criteria list match the source labels and that the amount range contains numbers. Confirm that all three SUMIF ranges cover the same number of rows. If you added data below row 100, extend the example ranges or use a table-based source.
Remove Duplicates removes more or fewer rows than expected: reopen the dialog and review the checked columns. Selecting only a category field removes repeated categories even when the rest of each row differs; selecting all columns removes only rows that match across all selected fields. Restore the backup if the wrong records were deleted.
Frequently Asked Questions
Can Excel combine duplicate names and add their amounts automatically?
Yes. A PivotTable groups repeated names and sums the associated numeric field. A SUMIF formula paired with a unique list also returns one total for each name.
Does Remove Duplicates add the values from duplicate rows?
No. It deletes repeated records based on the columns you select and keeps one occurrence. Use a PivotTable or SUMIF when you want to combine amounts rather than discard rows.
Can I consolidate duplicates based on one column while keeping other details?
You can tell Remove Duplicates to compare only one column, but it will keep one entire row and discard the other rows for that value. If those other details matter, decide which record should remain or summarize the details separately before deleting anything.
Will a PivotTable update when I add more source rows?
Refresh it after adding rows. If the new rows are outside the PivotTable’s source range, change the source or use an Excel table as the source so it can expand as records are added.
Can Excel treat entries with different capitalization as duplicates?
Excel’s standard duplicate removal and PivotTable grouping generally do not distinguish labels only by letter case. Spaces, spelling, and other character differences can still create separate groups, so standardize those values when necessary.
Related
- Excel Won’t Remove Duplicates? Causes and Fixes
- Excel Won’t Insert a Row? Causes, Fixes, and Workarounds
- Excel Print Area Greyed Out: Why It Happens and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Text in Cell: Exact or Partial MatchesTo count cells that contain particular text anywhere in their contents, use =COUNTIF(A2:A100,"*apple*"); the asterisks allow any characters…
- Excel CHAR Function Examples for Line Breaks and SymbolsExcel’s CHAR function converts a character code into its corresponding character; for example, with a first name in…
- Excel CONCATENATE Formula With a Space: Clear ExamplesTo join two Excel cells with a space between them, use the ampersand operator and put a space…
- Proper Case in Excel: Change Names with the PROPER FunctionTo change names to proper case in Excel, enter =PROPER(A2) in a blank column beside the first name,…
- Excel NETWORKDAYS: Count Workdays Between Two DatesTo count working days between two dates in Excel, use NETWORKDAYS. With the start date in A2 and…
- Upper Quartile Formula in Excel: How to Find Q3 CorrectlyIn Excel, use =QUARTILE.INC(A2:A20,3) to find the upper quartile, or third quartile (Q3), of the numbers in A2:A20.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.