Excel Group Duplicate Rows Without Losing Data

How-to basics · Updated November 19, 2026

To group duplicate rows in Excel, first decide what makes two rows duplicates, then sort by those columns or use Power Query to group them. If you only need to see which rows repeat, highlight or count duplicates instead; don’t use Remove Duplicates unless you want Excel to delete repeated records.

What “group duplicate rows” means

Grouping can mean several different things in a spreadsheet. You might want repeated records next to one another, a visible mark showing which records repeat, one summary row per repeated value, or collapsible sections that hide the underlying rows. Excel has different tools for each job, and choosing the wrong one can make it look as though data has disappeared.

It also matters which columns define a duplicate. Two rows may have the same customer name but different order numbers, so they are not duplicate orders. You might define a duplicate by one field, such as an email address, or by a combination of fields, such as product and date. Decide on that key before sorting, counting, or aggregating.

For example, in a list with Customer, Product, and Quantity columns, grouping by Customer places all of each customer’s purchases together. Grouping by all three columns instead treats only records with the same customer, product, and quantity as identical. Neither definition is universally correct; it depends on the question the data must answer.

Choose the right Excel method

Use sorting when the goal is simply to bring matching rows together while keeping every record visible. Use conditional formatting to flag duplicates without rearranging the list. Use a formula when you need a repeat count or a separate list of unique keys. For a reusable summary, use Power Query or a PivotTable. For collapsible sections, sort first and then use Excel’s outline tools.

  • Keep every row and see matches together: Sort by the duplicate-defining columns.
  • Find repeats without changing row order: Apply conditional formatting or add a count column.
  • Create one summary per key: Group in Power Query or build a PivotTable.
  • Hide and reveal blocks of rows: Create an outline after the matching records are adjacent.

These methods do not all produce the same output. Sorting changes order, formatting changes appearance, and aggregation combines records into summaries. Make a copy of the workbook before changing important source data, especially before deleting rows or transforming a query.

Sort duplicate rows so they sit together

In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Sort. In the Sort dialog, choose the first column that defines a duplicate, then select Add Level for each additional key column. Choose the desired order and select OK. If your data has headers, make sure My data has headers is selected.

When Excel asks whether to expand the selection, choose to expand it. That keeps each record’s fields on the same row. Sorting only one column can detach names, dates, or amounts from their corresponding records. If the list is an Excel Table, selecting a cell in the table and sorting through the Data tab keeps the table rows together as well.

For instance, sort first by Product and then by Date to place records for the same product together, with dates ordered inside each product group. Sorting does not remove or merge anything; it only changes row order. Excel for Mac has a Sort command on the Data tab too, though dialog details can vary by release. Excel for the web also supports sorting, but the desktop steps and available data tools can differ.

Highlight duplicates, including whole rows

To highlight repeated values in one column, select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel marks values that appear more than once in the selected range. This is useful for a quick scan, but applying that built-in rule to one column does not automatically shade every cell in each matching row.

To format entire rows based on a key, select the rows you want formatted and create a formula-based conditional formatting rule. For example, assume column A contains a required ID and columns A:C contain the records, with data in rows 2 through 100. Select A2:C100, then choose Home > Conditional Formatting > New Rule and select Use a formula to determine which cells to format. Enter:

With IDs in A2:A100, other row fields in B:C, and the selected formatting range A2:C100, use this formula to highlight every row whose ID occurs more than once:

=AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1)

Choose a format and confirm the rule. The dollar signs keep the lookup range fixed while the row reference adjusts for each row. The nonblank test avoids marking empty ID cells as duplicates. To define duplicates by a combination of fields, use COUNTIFS with one criteria range and matching value for each key field; for example, columns A and B can define a repeated pair. Be careful not to include a column that naturally differs between records, such as a unique transaction ID, or true matches will not be found.

Count repeats with a formula

A count column makes the number of occurrences visible and can be sorted or filtered. Suppose column A contains the key to check, with records in rows 2 through 100. Put this formula in B2 and fill it down through B100:

With IDs in A2:A100 and the formula entered in B2, this counts each ID across the full data range:

=COUNTIF($A$2:$A$100,A2)

If the ID in A2 appears three times, the formula returns 3 on each of those three rows. A result of 1 means that value appears only once in the specified range. Use a combination of columns when one field alone is not enough to define a duplicate. With categories in A and dates in B, and data in rows 2:100, a pair-count formula is:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

This counts rows where both the category and date match the current row. COUNTIF and COUNTIFS are available in current desktop Excel. Check that the ranges cover all relevant rows; a formula that stops at row 100 will not count records added below that boundary. If the data is an Excel Table, structured references can make formulas expand as new table rows are added.

Summarize duplicates with Power Query

Power Query is useful when you repeatedly need one grouped result from a larger source list. Select a cell in the data and choose Data > From Table/Range. If Excel prompts you to create a table, confirm that the header setting is correct. In the Power Query Editor, select the columns that define the group, then choose Home > Group By.

In the Group By dialog, choose the key columns and an operation. Count Rows gives the number of records in each group; other operations can summarize numeric columns, such as summing quantities. The resulting query is a summary, not the original row-by-row list. Choose All Rows if you want each group to retain a nested table containing its source records. When finished, use Home > Close & Load to return the output to Excel.

Power Query is particularly helpful for repeatable monthly or weekly work: refresh the query after the source table changes rather than rebuilding the summary manually. It does not change the original source list simply by grouping it, but the loaded output is a separate result that should be checked against the intended key and aggregation. Power Query support and some capabilities vary by Excel edition and platform, so the Windows Microsoft 365 steps above are the reference workflow.

Use a PivotTable for interactive summaries

A PivotTable is a good choice when you want to explore counts or totals by one or more fields. Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and where to place the PivotTable. In the field list, drag the field to group by into Rows, then drag a suitable field into Values. Excel commonly summarizes text fields by count and numeric fields by sum; use the value field settings to select the appropriate calculation.

To count orders by customer, put Customer in Rows and an order identifier in Values, then set the summary to Count if necessary. To group by customer and product, add both fields to Rows. The PivotTable displays a summary, not a merged version of the original records, and it does not establish that records are erroneous duplicates. Refresh it after the source data changes. A PivotTable is often easier to rearrange than a fixed report, while Power Query is better suited to producing a repeatable transformed table.

Create collapsible groups of matching rows

If “group” means folding repeated records under expandable outline controls, first sort the data so each group occupies consecutive rows. Then select the detail rows for a group and choose Data > Group in the Outline area. Excel adds controls that let you collapse or expand the selected rows. Repeat for other sections as needed.

This is a presentation tool, not a duplicate detector: Excel will not decide which rows belong together based on matching values when you manually group rows. For automatically generated subtotals and outlines, the Data > Subtotal command can insert a subtotal at changes in a sorted field. It works with a conventional range rather than an Excel Table; if the command is unavailable, check whether the data is formatted as a table. Subtotals add structure to the worksheet but do not remove the detail records.

Common mistakes and checks

  • Using Remove Duplicates too soon: The command permanently removes repeated rows from the selected data range. Make a copy first if the original records may be needed.
  • Choosing the wrong key: Matching names alone may group different people, while checking every column may miss repeats when a note or timestamp differs.
  • Sorting only part of a record: Expand the sort selection so values in different columns stay attached to the correct row.
  • Ignoring spaces or inconsistent text: “North” and “North ” may look alike but are different text values. Standardize data where appropriate before relying on exact matching.
  • Confusing a count with a duplicate list: A count identifies frequency; it does not combine the rows or show a summary of other fields.

Before sharing a grouped result, verify a few examples against the source. Check whether blank keys should be ignored, whether capitalization or spaces matter for the task, and whether the chosen aggregation answers the real question. For example, summing quantity is appropriate for units sold, but summing an order number usually has no useful meaning.

Frequently Asked Questions

How do I group duplicate rows in Excel without deleting them? Sort by the columns that define a duplicate, or use conditional formatting to flag matching rows. Both approaches preserve the source records. Power Query can create a separate grouped summary while leaving the original data available.

How do I group rows based on two columns? Use both columns as sort levels, as key fields in Power Query’s Group By dialog, or as criteria in COUNTIFS. The two values together define the match, so rows only belong to the same group when both fields match.

Can Excel automatically collapse duplicate rows? Excel does not automatically create collapsible groups merely because values repeat. Sort the rows first, then use Data > Group to create outline controls, or use Data > Subtotal for an outline based on changes in a sorted field.

What is the difference between grouping and removing duplicates? Grouping arranges or summarizes records while retaining the source records, depending on the method. Remove Duplicates deletes repeated records from the selected range and keeps one occurrence, so it is a data-cleaning action rather than a display or summary method.

Can I make a list of unique values from duplicate rows? In Microsoft 365 or Excel 2021 and later, UNIQUE can return distinct values or rows. With the source records in A2:C100, this formula returns distinct three-column rows:

=UNIQUE(A2:C100)

The result spills into neighboring cells and does not group the original rows in place. Make sure the spill area is empty; otherwise Excel may show a spill error. If the goal is a count or subtotal for each distinct key, use a PivotTable or Power Query instead.

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.