How to Group the Same Numbers in Excel: Sort and Count

To group identical numbers in Excel, sort the entire data range by the column containing those numbers: select a cell in the range, choose Data > Sort A to Z (or Smallest to Largest), and keep the full rows together when Excel asks. Repeated values will appear next to one another. If you also need a count or summary for each number, use a COUNTIF formula or a PivotTable.

Sorting is usually the right choice when you want matching numbers together but still need to see each original record. It does not combine or remove duplicate rows. The steps below are for Microsoft Excel for Microsoft 365 on Windows; Mac and web differences are noted where relevant.

What You’ll Need

An Excel worksheet with numbers in one column. The values can be part of a larger table with other details in adjacent columns.
A clear header row, such as Order number or Score. A header helps Excel identify the column when you sort.
A saved copy of important data before making a large change. Sorting changes row order, so a copy gives you a straightforward way to return to the original arrangement.

Step-by-Step

Check how your data is arranged. Put each record on one row, with the number you want to group in a single column. For example, a sales list might have a customer number in column A, a customer name in column B, and an amount in column C. Make sure there are no completely blank rows or columns splitting the data. If the numbers are in several separate columns, decide which one should determine the grouping; Excel sorts by one chosen column at a time.
Save a copy if you may need the original order. Choose File > Save As and save another workbook, or duplicate the worksheet by right-clicking its tab and choosing Move or Copy, then selecting Create a copy. This is useful if the current order represents a sequence you will need later. Sorting can be undone immediately with Ctrl+Z on Windows or Command+Z on Mac, but a saved copy is safer if you continue editing.
Select a cell in the number column. Click a cell that contains one of the values to group. If the data is a well-formed block with headers, Excel can usually detect the surrounding range. For extra control, select the entire data range, including every column you want to keep together, and exclude unrelated notes or totals. In an Excel Table, clicking a cell in the table is generally enough because the table structure identifies the rows and columns.
Sort the values into order. On the ribbon, choose Data > Sort A to Z or Data > Sort Smallest to Largest, depending on which command Excel displays. The exact wording can vary with the selected data. For a more controlled sort, choose Data > Sort. In the Sort dialog, select the number column in Sort by, choose Cell Values under Sort On, and choose Smallest to Largest under Order. Click OK. Largest to Smallest also groups equal values; it just reverses the order of the groups.
Keep each record’s columns together. If you selected only the number column while neighboring columns contain names or other details, Excel may ask whether to expand the selection. Choose Expand the selection, then click Sort. This moves the whole row with its number. If you choose Continue with the current selection, only that column moves, which can attach numbers to the wrong names or amounts. When sorting a formatted Excel Table, its rows remain together automatically.
Check that the groups are correct. Look down the sorted number column and confirm that matching values are adjacent. Check a few corresponding rows in the other columns to ensure their details still belong to the right number. For instance, if the value 204 occurs three times in the original list, there should be three rows with 204 together after sorting. Sorting groups the rows but does not delete repeated entries or combine their values.
Optional: count how often each number appears. With the values in A2:A8 and a count column beside them, enter this formula in B2 and fill it down. If A2 contains 12 and 12 occurs three times in A2:A8, the result in B2 is 3. Every row with the same number will show the same count.

With numbers in A2:A8 and the count for the value in A2 needed in B2, use =COUNTIF($A$2:$A$8,A2).

If you want a sorted copy of a single number list instead of changing the source data, Excel for Microsoft 365 and Excel 2021 can use a dynamic-array formula. With the original values in A2:A8 and an empty output area starting at C2, enter the formula below. For the example list 12, 5, 12, 8, 5, 12, 8, it spills 5, 5, 8, 8, 12, 12, 12 down the column. This sorts the values but does not carry along other columns; sort the complete range through the ribbon when you need full records grouped.

With the values to sort in A2:A8 and the output starting in C2, use =SORT(A2:A8).

Common Mistakes

Sorting only the number column. This is the most damaging mistake when each row contains related information. Always expand the selection or sort the complete range so names, dates, and amounts travel with their number.
Including a total row in the sort. A total can move into the middle of the records and make the result confusing. Keep totals outside the selected range, or use a Table with its total row handled separately.
Assuming sorting removes duplicates. Sorting only brings identical values together. It preserves every row. If you intend to remove duplicate records, first decide which columns define a duplicate and use Data > Remove Duplicates on a copy. That command deletes rows, so it is not a substitute for grouping.
Mixing numbers and text that looks like numbers. A value stored as the number 7 may not sort exactly like text containing 7. If a group appears in an unexpected place, check the cell format and convert the entries consistently before sorting.
Choosing the wrong field in the Sort dialog. If the dialog is set to sort by a name, date, or another column, the identical numbers will not necessarily end up together. Confirm the Sort by field before clicking OK.

Tips

Use a PivotTable when the goal is a summary. Select your data, choose Insert > PivotTable, confirm the table or range, and choose where to place the PivotTable. In the PivotTable Fields pane, drag the number field to Rows. Drag a field with a value on every record to Values. Excel will show one row per number and usually count that field. To total amounts instead, drag the amount field to Values and check that its summary is Sum. A PivotTable summarizes the data rather than rearranging the original rows.
Add a second sort level for a tidy group. Choose Data > Sort, set the number field as the first level, then select Add Level and choose another field, such as date or name. Excel groups matching numbers first, then orders records within each group according to the second field. Use additional levels only when they make the grouped results easier to read.
Turn the range into a Table for ongoing work. Select the data and choose Insert > Table, making sure My table has headers is selected when appropriate. Table header filters let you sort a field while keeping each record’s columns together. New rows added to the Table are included in later sorts and summaries.
Keep an original-order field if you will need to restore the sequence. Add a column such as Original position and fill it with 1, 2, 3, and so on before sorting. Later, sort by that field to return to the starting order. This is especially helpful when the original row order has meaning but you also need grouped views.
Choose the method that matches the task. Sort when you want all source rows visible and adjacent by number. Use a PivotTable to see one summary row per number. Use a count formula when you want a frequency beside each original record. These approaches produce different results, so do not use a summary tool if you need every original row intact.

Troubleshooting

Some matching numbers are separated. Check for hidden spaces or mixed data types, such as some entries stored as text and others as numbers. Select the affected cells and look for Excel’s error indicator or use the warning options to convert numbers stored as text. For imported data, clean or convert the column consistently, then sort again. Also check that you sorted by the intended field.
Names or amounts no longer match their numbers. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Then select the full data range and repeat the sort, choosing Expand the selection if prompted. If you have already made more changes, restore the saved copy and compare it with the current sheet before continuing.
Excel sorts 100 after 20, or otherwise seems to sort as text. The cells may contain text rather than numeric values. Check whether entries are left-aligned or show a warning, but do not rely on alignment alone because formatting can change it. Convert the column to numbers consistently, confirm that decimal and thousands separators match your regional settings, and sort again.
Blank entries or headers are being sorted as data. Confirm that the header is included and that the Sort dialog’s My data has headers option is set correctly. If blank cells are legitimate records, decide whether they belong in the sorted range; otherwise remove or handle them before sorting. Keep a header at the top by excluding it from the data rows only if you are using a sort method that does not recognize headers.
The PivotTable shows Sum when you expected Count. In the PivotTable, open the menu for the field under Values, choose Value Field Settings, and select Count. If Excel offers Sum automatically, check that the field contains consistent numeric data; mixed text and numbers can affect the default summary.

On Mac, the same sorting options are available on the Data tab, though ribbon layout and dialog wording can differ slightly. Excel for the web also supports sorting and PivotTables, but some advanced dialog options may be arranged differently. If a command is hard to locate, use the app’s search box to find Sort or PivotTable.

Frequently Asked Questions

Can Excel automatically group identical numbers?

Sorting the full range by the number column is the simplest way to put identical values next to each other. Excel does not automatically combine the rows just because their numbers match. For an automatically updating sorted list, use the SORT formula in Microsoft 365 or Excel 2021, but remember that a formula sorting one column will not move other record details.

How do I group numbers without changing the original data?

Make a copy of the worksheet or use a PivotTable to summarize the values without rearranging the source rows. For a separate sorted list of one column in Microsoft 365 or Excel 2021, place the SORT formula in an empty area. A formula result is a view of the values, not a replacement for sorting full records.

How can I count each repeated number?

Use COUNTIF to count how many times a value appears in a range. If the numbers are in A2:A8 and you want the count for A2, enter =COUNTIF($A$2:$A$8,A2) in a nearby cell and fill down. Each row then displays the frequency of its number.

Does grouping numbers delete duplicate rows?

No. Sorting places identical values together but leaves every row in place. A PivotTable can display a single summary row per value, while Data > Remove Duplicates deletes duplicate records from the selected data. Make a copy before using Remove Duplicates if you might need the original entries.

Can I group by number and then sort within each group?

Yes. Open Data > Sort, set the number column as the first sort level, and add a second level for the field to order within each group, such as date or name. Excel sorts by the first field before applying the next level.

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.