Excel Group By and Remove Duplicates: How to Choose

How-to basics · Updated November 19, 2026

To delete repeated records in Excel, select your data and choose Data > Remove Duplicates, then select the columns that define a duplicate. To combine records into summaries instead, use a PivotTable or Power Query’s Group By; these are different tasks, and grouping does not automatically delete duplicate rows.

Grouping and removing duplicates are different jobs

The phrase group by can mean several things in Excel. You might want to calculate one total per customer, arrange rows under expandable headings, or simply keep one copy of each repeated record. The right tool depends on the outcome you need:

  • Remove Duplicates deletes repeated rows from the selected range or table, leaving one occurrence of each combination of chosen columns.
  • Group By in Power Query creates a summarized result, such as total sales for each customer. It does not edit the source table unless you choose to load the result over it.
  • A PivotTable groups records into categories and calculates summaries that you can rearrange interactively.
  • Data > Group creates collapsible row or column outlines. It hides and reveals detail; it does not merge matching records or calculate totals.

For example, if a sales list has several orders for the same customer, removing duplicates on the Customer column leaves one order row per customer and discards the others. A PivotTable grouped by Customer can instead show each customer’s total sales while accounting for every order. Choosing the wrong operation can therefore lose information or produce a misleading summary.

Choose the result you actually need

Before changing the data, ask what a row represents and what the output should contain. If each row is a transaction and you need to preserve every transaction, do not remove duplicates based only on customer name. If you need a list of customers with no repeats, removing duplicates on the customer field may be appropriate. If you need customer totals, group and aggregate the transactions.

  • Use Remove Duplicates when repeated records are errors or when you intentionally want a unique list.
  • Use a PivotTable for a quick, adjustable summary that readers can explore by category, date, or other fields.
  • Use Power Query Group By for a repeatable transformation that you can refresh when the source data changes.
  • Use outline grouping when rows are already arranged in a meaningful order and you want to collapse detail.

Decide which fields define sameness, too. Two orders may share a customer but differ in date and order number. Those are not duplicate transactions merely because the customer name matches. In contrast, two rows with the same invoice number might be duplicates even if a note or formatting differs. The chosen columns are your definition of a duplicate, not Excel’s judgment about what the records mean.

Remove duplicate rows in Excel

In Excel for Microsoft 365 on Windows, work on a copy if you may need the original records. Click inside the data or select the range, then follow these steps:

  1. Make sure the data has a header row and that each column has a clear heading. Convert the range to a table with Insert > Table if you want a structured, filterable data set.
  2. Choose Data > Remove Duplicates.
  3. In the dialog box, confirm whether the data has headers. Select only the columns that together identify a repeated record.
  4. Choose OK and read the message reporting how many duplicate values were removed and how many unique values remain.

If you select only Email, Excel compares rows using that column and removes later rows whose email matches an earlier one. It removes the corresponding row from the selected table or range; it does not merely clear the email cell. If you select Email and Date, only rows matching on both fields count as duplicates. A different value in either selected field means the row is retained.

Excel keeps the first occurrence it encounters and removes later matching rows. If you want a particular record to survive—for example, the newest customer details—sort the data into the desired order before removing duplicates, or use a method that lets you review and choose which record to keep. Sorting changes row order, so preserve a copy when order matters.

Define duplicates with a concrete example

Imagine a contact list with columns Name, Email, and City. Two rows may have the same name but different email addresses; removing duplicates using Name alone would treat them as one and delete a potentially valid contact. If email is the unique identifier, choose only Email. If a business defines a record by both email and region, select both columns. Excel then compares the combination, not each field independently.

Suppose an order table contains Order ID, Product, and Quantity. If every Order ID should appear once, select Order ID only. If an order can contain several products, however, the real line-item key may be Order ID plus Product. Selecting Order ID alone could erase legitimate product lines. Write down the intended key before running the command; this small check prevents many accidental losses.

Remove Duplicates is a data-cleaning operation, not a review screen. It does not let you compare every conflicting row before deciding which one is correct. If duplicate records have different addresses, quantities, or dates, investigate the conflict first. Consider filtering, sorting, or making a separate review list so the decision is based on the records rather than simply on which one appears first.

Summarize with a PivotTable

For an interactive summary, select a cell in a clean table and choose Insert > PivotTable. Confirm the source range or table and choose where to place the PivotTable. In the field list, drag the category you want to group by—such as Customer—into Rows. Drag a numeric field such as Sales into Values. Excel commonly sums numeric fields and counts text fields, but check the displayed calculation: open the value field settings if you need a different summary such as Count or Average.

A PivotTable leaves the source records intact and presents an aggregation. For example, five transactions for Northwind can appear as one customer row with their sales added together. You can add Month to Columns or Product beneath Customer in Rows to explore the same source at another level. Refresh the PivotTable after its source data changes; a PivotTable is not always updated immediately when new rows are entered, depending on the source and setup.

Grouping dates in a PivotTable can organize them into periods when Excel recognizes the source values as dates. If dates appear as separate text labels or grouping is unavailable, check that the source column contains real Excel dates and does not contain blanks or invalid entries. A PivotTable is a good choice when people need to change the layout or inspect subtotals without rebuilding the source data.

Use Power Query Group By for a repeatable result

Power Query is useful when the same cleanup or summary must be repeated as new data arrives. On Windows, select a cell in the source table and choose Data > From Table/Range. In the Power Query Editor, choose Home > Group By. Select one or more grouping columns, then choose an operation and the column it should use, if applicable. For example, group by Customer and choose Sum on Sales to produce total sales by customer. Select OK, then use Home > Close & Load to return the result to Excel.

Power Query creates a transformed query result rather than deleting rows from the original worksheet table. When the source changes, refresh the query to rebuild the grouped output. This makes it useful for monthly files or recurring reports, but it also means the output is a separate result that should be refreshed and checked. If you need the full detail later, keep the source data and avoid replacing it with the grouped summary.

Power Query’s Group By can also count rows or calculate other supported aggregations, such as average, minimum, or maximum. The available choices and interface can vary with Excel version and platform. On Mac and Excel for the web, Power Query features and editing options may differ from Windows; if the Group By workflow is not available in your edition, a PivotTable is often the more portable way to summarize records.

Use a formula when you need a live unique list

For a list that updates as source values change, Microsoft 365 and Excel 2021 or later include the dynamic array function UNIQUE. With names in A2:A7—Ada, Ben, Ada, Cara, Ben, and Dev—enter this formula in an empty cell:

=UNIQUE(A2:A7)

The spilled result is Ada, Ben, Cara, and Dev, in the order of their first appearances. Unlike Remove Duplicates, the formula leaves the source untouched and recalculates when its referenced values change. Leave empty cells below the formula so its results have room to spill. If another value blocks the output area, Excel shows a spill error; clear the obstruction or move the formula.

UNIQUE returns distinct values or rows, depending on the range supplied, but it does not summarize associated numbers. For totals by category, use a PivotTable or Power Query Group By. Also, do not assume a unique list is a full audit of messy text: inconsistent spaces, spelling, or data types can make entries appear different. Clean or standardize the source before deciding that the result represents distinct real-world items.

Common problems and safe checks

A frequent surprise is that rows that look alike are not identical in the columns selected. Trailing spaces, inconsistent punctuation, different date values, or numbers stored as text can affect matching and grouping. Conversely, two rows that differ in a column you did not select may still be treated as duplicates. Check a few examples before removing anything, and make sure the selected columns reflect the intended record key.

  • Keep a recoverable copy: Duplicate removal changes the selected data. Undo may work immediately afterward, but it is not a substitute for a backup, especially after further edits or saving.
  • Check the header setting: If Excel treats a header as data, it can affect the result. Confirm the dialog’s header option before proceeding.
  • Review the scope: If only part of a table or range is selected, verify that the command is operating on the complete record set you intend to clean.
  • Do not confuse highlighting with deletion: Home > Conditional Formatting can highlight repeated values for review, but highlighting does not remove rows.
  • Check totals after grouping: Confirm that the chosen value field and operation match the question. A count of transactions is not the same as a sum of sales.

On Excel for the web, the Data tab includes a Remove Duplicates command in current versions, though the exact interface can change. On Mac, command locations and Power Query capabilities can differ by release. If you cannot find a feature, check the Data tab and the help for your installed edition rather than assuming that a missing command means the workbook data is incompatible.

Frequently Asked Questions

Does Excel Group By remove duplicates?
No. Group By combines records into groups and can calculate summaries. Remove Duplicates deletes later matching rows from the selected data. Use Group By to aggregate; use Remove Duplicates to keep one occurrence.

How do I remove duplicates based on two columns?
Choose Data > Remove Duplicates, then check both columns in the dialog. Excel treats two rows as duplicates only when the values match across both selected columns.

Can I see duplicates before deleting them?
Yes. Conditional formatting can highlight repeated values, or you can sort and filter the data to inspect it. Those methods help you review records but do not remove them. Keep a separate copy before making a destructive cleanup.

Does Remove Duplicates keep the first or last row?
It keeps the first occurrence it encounters and removes later matching rows. Sort first if a particular record should appear first, and verify the order before running the command.

What is the difference between UNIQUE and Remove Duplicates?
UNIQUE returns a formula-driven distinct list while leaving the source data unchanged. Remove Duplicates edits the selected data. Use UNIQUE for a live list, and use Remove Duplicates when you intend to clean the records themselves.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.