How to Group Names in Excel: Sort, Count, or Collapse

To group names in Excel, sort the list by the name or category column; to make rows collapsible, select adjacent rows and choose Data > Group. If you need a count of each name, create a PivotTable. The right method depends on whether you want to organize records, summarize repeated names, or hide and show sections of a worksheet.

Before changing a list, make a copy if the original order matters. Keep each person’s related details in the same row: sorting just the name column can separate names from their phone numbers, departments, or other information. The steps below use Excel for Microsoft 365 on Windows, with notes for other versions where the controls differ.

What You’ll Need

An Excel workbook with a list of names. A header row, such as Name or Department, makes sorting and PivotTables easier.
Any related information you want to keep with each name, such as a team, location, or ID. Keep it in adjacent columns on the same rows.
A clear idea of the result you want: an alphabetized list, a summary of repeated names, or collapsible sections of worksheet rows.

Step-by-Step

Choose what you mean by grouping. If you want names next to similar names, sort the list. If you want a count of how often each name appears, use a PivotTable or a counting formula. If you want to hide and reveal sets of rows, use Excel’s outline grouping. These methods create different results: sorting changes order, a PivotTable summarizes data, and an outline controls which rows are visible. You can use more than one method on the same data, but start with a copy if you are unsure.
Check the structure of your list. Put one record on each row and use a separate column for each type of information. For example, use columns called Name, Department, and Email, with the headers in row 1 and records below. Remove completely blank rows from the middle of the list, and make sure every column has a header. If there are no headers, add them before sorting or creating a PivotTable so Excel can identify the fields reliably.
Sort names into an alphabetical group. Click a cell inside the data, then choose Home > Sort & Filter > Sort A to Z, or use Data > Sort A to Z. When Excel asks whether to expand the selection, choose Expand the selection so the other columns stay with each name. To sort by a category first and names second, choose Data > Sort, select the category column under Sort by, and set the order. Choose Add Level, select the name column under Then by, and set A to Z. Select OK. Each category will then appear as a block, with names alphabetized within that block.
Make a PivotTable to summarize repeated names. Click inside the list and choose Insert > PivotTable. Check the table or range shown, choose New Worksheet, and select OK. In the PivotTable Fields pane, drag Name to Rows and drag Name to Values. Excel normally counts text entries in the Values area. If it shows Sum or another calculation instead, open the field menu in Values, choose Value Field Settings, select Count, and select OK. The PivotTable now lists each distinct name once with its number of appearances. If your list has a Department column, drag Department above Name in Rows to organize the names under departments. A PivotTable summarizes the source; it does not combine or delete the original records.
Count each name beside the original list, if that is more useful. With names in A2:A100, put a heading such as Count in B1. In B2, enter =COUNTIF($A$2:$A$100,A2), then fill the formula down the column. The result in each row is the number of times that row’s name occurs in A2:A100. For example, if Mina appears twice in that range, the formula beside each Mina row returns 2. The dollar signs keep the searched range fixed when you copy the formula. This formula counts exact cell entries for ordinary names, but it does not distinguish uppercase from lowercase.
Create collapsible groups of rows. First sort the list so the rows you want to group are next to one another. Select the row numbers for the first block, not just the cells in the Name column. Choose Data > Group in the Outline area. If Excel asks whether to group Rows or Columns, choose Rows and select OK. A bracket and a minus button appear beside the selected rows. Click the minus button to collapse the block; click the plus button to show it again. Repeat for other separate blocks. This creates an outline, not a permanent category label, so use a category column as well if readers need to know what each block represents.
Review the result and keep the source data intact. Check that related details still match the right names after sorting, that the PivotTable counts look plausible, or that the outline buttons hide and reveal the intended rows. Save the workbook after checking it. To remove a row outline, select the grouped rows and choose Data > Ungroup; to remove an entire outline, use Data > Ungroup > Clear Outline. Clearing an outline removes the collapse controls, but it does not delete the underlying records.

Common Mistakes

Sorting only the name cells: This is one of the easiest ways to mismatch names and details. Always expand the selection, or select the whole data range before sorting. Turning a well-structured range into an Excel table with Insert > Table can also help keep records together when you sort.

Expecting sorting to create collapsible sections: Sorting only changes the row order. To hide or reveal rows, use Data > Group after sorting. Conversely, grouping rows does not calculate how many times each name appears; a PivotTable or COUNTIF formula is needed for that.

Grouping rows that are not adjacent: An outline group applies to a continuous block of rows. If the same department appears in several separated places, sort by department first, or create a separate group for each continuous block. Do not select scattered row numbers and expect Excel to create one logical group.

Using inconsistent names: Excel treats entries such as “Sam Lee,” “Sam Lee ” with a trailing space, and “Samuel Lee” as different text in a PivotTable. Review spelling, spaces, and naming conventions before relying on a count. A PivotTable’s summary is only as consistent as its source values.

Including blank rows or missing headers: Empty rows can interrupt a sort or make Excel select an incomplete range. Blank headers can also make PivotTable fields confusing. Check the list from top to bottom and give each column a distinct header before using these tools.

Tips

Use a category column to make groups understandable and repeatable. For instance, a Team column with values such as Sales, Support, and Design is clearer than relying on row position alone. Sort by Team and then Name to create a readable list, or use those fields in a PivotTable to show teams and their members. This also makes it easier to refresh a summary when new records arrive.

For a simple visual divider without hiding rows, apply a filter or use borders and fill colors after sorting. Filters are useful when you want to display only one team at a time: click in the data and choose Data > Filter, then use the arrow in the Team header to select the value. A filter hides rows temporarily, while outline grouping gives you expandable row sections. Choose based on how someone will use the worksheet.

When using a PivotTable, refresh it after changing the source list. Click inside the PivotTable and choose PivotTable Analyze > Refresh. If new records are added beyond the original source range, the PivotTable may not include them; use PivotTable Analyze > Change Data Source to check or update the range. Converting the source to an Excel table before creating the PivotTable helps it accommodate added rows more smoothly.

For a workbook shared with others, keep a clear source sheet and put the summary on a separate sheet. Label the summary, state whether it shows counts or members, and avoid deleting duplicate source rows unless you have confirmed they are true duplicates. Two people can share a name while representing different records, so a repeated name alone is not proof that a row should be removed.

Troubleshooting

The sort mixes up details: Undo immediately with Ctrl+Z on Windows (Command+Z on Mac), then select the complete data range and sort again. If you already made other changes, restore a saved copy if available. Check that no data column was left outside the selected range.

The PivotTable counts are wrong or missing names: Check the PivotTable source range and confirm that the name column is in Rows and Values. In Value Field Settings, choose Count. Look for blank names, extra spaces, spelling variations, or records outside the source range. Refresh the PivotTable after correcting the source.

The Group command is unavailable or no buttons appear: Select worksheet row numbers and confirm that the sheet is not protected. Make sure you selected a continuous range of rows, then try Data > Group again. If the outline symbols are hidden, check the worksheet’s outline display settings or use the level buttons beside the row numbers. Excel for the web may have fewer outline controls than the desktop app; open the workbook in desktop Excel if you cannot create or manage the grouping there.

The formula returns 0 or an unexpected count: Verify that the formula range includes all relevant names and that the reference in the second argument points to the name on the same row. Check for blank cells or inconsistent spellings. If you add names beyond row 100, extend the range in the formula or use a table-based range that grows with the list.

The group collapses the wrong rows: Choose Data > Ungroup for the affected rows, sort the data into the intended order, and create the group again using the row numbers. If several levels of outlines are present, use the outline level buttons to show the amount of detail you need, then confirm the bracket covers the correct rows.

Frequently Asked Questions

How do I group names by department in Excel?

Sort by the Department column and then by Name using Data > Sort and Add Level. For a summarized layout, create a PivotTable and place Department above Name in the Rows area. If you also want to collapse worksheet rows, sort first and use Data > Group on each continuous department block.

How can I group duplicate names and see how many there are?

Create a PivotTable with Name in Rows and Name in Values, then set the Values calculation to Count. Or use COUNTIF beside the original list. Neither method deletes the source records, which is useful when repeated entries might represent separate people or events.

Can I group names without sorting them?

You can make outline groups without sorting, but each group must be a continuous set of rows. If matching names or categories are scattered through the sheet, sort or filter the data first, or use a PivotTable to summarize them without changing the source order.

How do I ungroup names in Excel?

Select the grouped row numbers and choose Data > Ungroup. To remove all outline levels, choose Data > Ungroup > Clear Outline. Ungrouping removes the expand-and-collapse structure; it does not remove names or other worksheet data.

Does Excel combine identical names when I group them?

No. Sorting and outline grouping leave the original rows in place. A PivotTable displays each distinct name as a summary label, but the source records remain unchanged. If you intend to delete duplicate records, verify that the full records—not just the names—are duplicates first.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.