How to Group Like Items in Excel

To group like items in Excel so matching entries appear together, select your data and choose Data > Sort, then sort by the column that identifies each item, such as Category or Product. If you also want totals or counts by item, create a PivotTable instead. These approaches organize matching records without accidentally merging or deleting the original rows.

What You’ll Need

An Excel workbook with a consistent table of data: one header row and one record per row.
A column that identifies the groups, such as product, department, region, or category.
Optional: a numeric column, such as quantity or sales, if you want grouped totals.

The steps below are for Excel for Microsoft 365 on Windows. Excel for Mac has the same basic sorting and PivotTable options, although some ribbon controls may be arranged differently. Excel for the web supports sorting and PivotTables, but some advanced desktop commands are not available there.

Step-by-Step

Check and prepare the data. Keep each record on one row, with field names in the first row. For example, use columns named Product, Category, Quantity, and Sales. Do not insert blank rows or columns inside the list; they can make Excel treat part of the data as a separate range. Check that category names are consistent. Excel treats “Office,” “office,” and “Office ” (with a trailing space) as different text values in some operations. If you have a heading above the list, leave a blank row between it and the table so Excel can identify the actual header row.
Turn the range into a table (recommended). Click a cell in the data, then choose Insert > Table, confirm the range, and make sure My table has headers is selected before clicking OK. On Windows, Ctrl+T opens the Create Table dialog; on Mac, use Command+T. A table helps Excel keep all columns together when you sort and automatically includes new adjacent rows. If you prefer not to make a table, select the complete data range, including every column and the headers.
Open the full Sort dialog. Click inside the table or selected range and choose Data > Sort. If a warning appears asking whether to expand the selection, choose Expand the selection. This is essential: sorting only the category column can detach labels from their quantities, prices, or other details. In the Sort dialog, select My data has headers if Excel has not already detected the header row.
Choose the field that defines the groups. In the Sort by list, choose the column that contains the shared item names or categories, such as Product or Department. For Sort On, choose Cell Values, and for Order choose A to Z or Z to A. Click OK. All rows with the same value will now sit together, while the rest of each row stays intact. For instance, rows for notebooks will appear together, followed by rows for pens and folders.
Add a second sort level if it makes the groups easier to read. Reopen Data > Sort and choose Add Level. Keep Category as the first level, then choose Product or Date as the second level. Excel sorts by the first field, then sorts records within each matching first-level group by the second field. Use Add Level again if you need a third ordering rule. Avoid sorting by a field that should not control the arrangement, such as a changing transaction number, unless that is your intention.
For counts or totals, summarize with a PivotTable. Click a cell in the table and choose Insert > PivotTable. Confirm the Table/Range and choose New Worksheet, then select OK. In the PivotTable Fields pane, drag the field you want to group by, such as Category, into Rows. Drag a descriptive field into Values to count records, or drag a numeric field such as Quantity or Sales into Values to calculate a total. Excel generally counts text fields and sums numeric fields by default. This creates a compact summary while leaving the source records unchanged.
Set the PivotTable calculation you need. If a field in Values shows a sum but you need a count, open its drop-down in the Values area, choose Value Field Settings, select Count, and click OK. To change a count to a sum, choose Sum instead; that option is appropriate only for numeric data. Rename a summary heading by selecting its cell and typing a clearer label, such as Number of Orders or Total Sales. The PivotTable displays one row per category and the chosen result beside it.
Refresh the summary after changing the source. A PivotTable does not always update immediately when you edit or add source data. Click inside it and choose PivotTable Analyze > Refresh, or right-click inside the PivotTable and choose Refresh. If new records were added outside the original source range, convert the source to a table first or update the PivotTable data source using PivotTable Analyze > Change Data Source. Review the result after refreshing to make sure the new rows are included.
Use outline grouping only when you want collapsible rows. The Data > Group command is different from sorting records by matching text. It creates an outline that lets you collapse or expand selected rows or columns. First arrange the rows in the order you want, select the detail rows to hide or show together, then choose Data > Group > Group. Use the outline level buttons or the minus/plus controls beside the rows to collapse or expand them. This is a manual display feature; it does not find every matching label and group those rows automatically.

Choose the method that matches your goal: sorting puts matching records next to each other, a PivotTable creates a category-level summary, and outline grouping hides or reveals selected sections. You can sort first and create a PivotTable later, but a PivotTable is usually the clearest option when the main purpose is comparing group totals or counts.

Common Mistakes

Sorting just one column. This can scramble the relationship between a category and its related details. Sort the entire table or choose Expand the selection when Excel asks.
Expecting Sort to calculate totals. Sorting changes row order; it does not combine duplicate records or calculate a total for each name. Use a PivotTable for summaries.
Using Data > Group to find matching labels. That command creates a manual outline, not automatic category grouping. Sort or use a PivotTable when rows should be grouped by a shared value.
Leaving inconsistent names in the group field. Spelling variations, extra spaces, and different labels can create separate groups. Standardize the source values before summarizing.
Leaving blank rows in the middle of the list. Excel may stop at a blank row and sort only part of your data. Remove unnecessary breaks or explicitly select the complete range first.
Assuming a PivotTable updates itself. Refresh it after changing the source. If new rows are outside a fixed range, update the source or use an Excel table as the source.

Tips

Use clear, unique column headers. A field named Category is easier to recognize in a Sort dialog or PivotTable Fields pane than a blank or repeated heading.
Sort the group field first and a useful detail field second. For example, sort by Department and then Employee Name to keep departments together and make each department easy to scan.
For a quick one-column sort, use the filter arrow in a table header and choose Sort A to Z or Sort Z to A. Excel sorts the full table, not just that column. Use Data > Sort when you need multiple levels or a custom order.
For categories that need a specific sequence, such as High, Medium, Low, alphabetical order may not be suitable. In the Sort dialog, choose Custom List under Order and select or create the intended sequence.
Keep a copy of the original data when you are unsure whether a sort or cleanup is reversible. Sorting itself does not delete records, but later edits to a summarized view can be harder to trace back to source rows.
To verify a summary, compare its grand total with the total of the corresponding source values, and check that the number of PivotTable categories makes sense. A surprising extra group often points to inconsistent labels or blank entries.

Troubleshooting

Some rows did not move with the rest. Undo the sort immediately (Ctrl+Z on Windows; Command+Z on Mac), then repeat it with the whole table selected or choose Expand the selection. If you sorted only one column and continued editing afterward, inspect the records carefully before trying to repair them; undo may no longer restore the original row relationships.

The same item appears as two groups. Check the source values for leading or trailing spaces, spelling differences, punctuation, or inconsistent abbreviations. Edit the source so equivalent items have the same text, then refresh the PivotTable or sort again. If the list is long, use Find to locate common variations. Do not replace values until you have confirmed they mean the same thing.

The PivotTable says Count when you expected Sum. Check that the source column contains real numbers rather than numbers stored as text, blanks, or labels mixed into the data. Correct the source values, refresh the PivotTable, and use Value Field Settings to choose Sum. If Sum is unavailable or produces an unexpected result, inspect the source cells and number formats; a number format alone does not convert text into a numeric value.

New records are missing from the PivotTable. Click the PivotTable and refresh it. If the added records still do not appear, use PivotTable Analyze > Change Data Source to check whether the selected range includes them. Converting the source list to a table before creating the PivotTable helps it include additional rows more reliably.

The Sort dialog does not show the expected column names. Confirm that the header row is included and that My data has headers is selected. Check for merged cells or blank header cells, which can interfere with selecting fields. Give every column a distinct, nonblank heading and reopen the dialog.

Frequently Asked Questions

Does grouping like items delete duplicate rows?
No. Sorting keeps every row, including repeated records. A PivotTable summarizes repeated values, but it does not remove the original records. If you actually need to delete duplicates, use Data > Remove Duplicates on a copy of the data and review the columns selected before confirming.

How do I group rows by more than one category?
For sorting, use Data > Sort and add levels, putting the main category first and the secondary category next. For a PivotTable, drag both fields into Rows; place the broader category above the more specific one to create nested groups.

Can I group similar words that are not exactly the same?
Excel’s regular sort and PivotTable grouping treat different text values as separate labels. Standardize the source first, for example by correcting spelling variations or assigning each variation a shared category in a separate column. Check the mapping before replacing data.

How do I show a total for each group?
Create a PivotTable, put the category field in Rows, and put a numeric field such as Sales in Values. If Excel chooses Count instead of Sum, open Value Field Settings and select Sum, then confirm that the source entries are numeric.

Can I collapse matching rows with the Group command?
Yes, but you must first arrange the rows and select the specific contiguous rows to outline. Choose Data > Group > Group to make them collapsible. The command does not automatically search the worksheet for matching item names.

Will a sort or PivotTable change the source data?
Sorting changes the order of source rows but does not change their values. A PivotTable presents a separate summary and leaves the source rows in place. Refresh the PivotTable after source edits so its displayed results reflect the latest data.

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.