To keep related records together when sorting in Excel, select the entire data range (or convert it to a table), then sort by the column that identifies each group. Excel moves complete rows together, so names, dates, and amounts stay matched. The Data > Group command is different: it creates collapsible outline sections and does not group records for sorting.
This guide is for Excel for Microsoft 365 on Windows. The key is to sort complete records, not just one column. For example, if each row is a sale and several sales belong to the same customer, sort by Customer first and then by Date to keep each customer’s sales adjacent and ordered.
What You’ll Need
- A worksheet with one header row and one record per row. Use consistent column headings such as Customer, Date, and Amount.
- A column whose values identify the groups you want, such as department, customer, project, or category.
- Optional: a copy of the workbook, especially if the current order is important and you may want to restore it.
Step-by-Step
- Check that each row is a complete record. Put each field in its own column and keep all information for one record on the same row. For example, a sales list might have Customer in column A, Date in column B, Product in column C, and Amount in column D. Do not use separate rows for one record’s details if you expect those details to move together when sorting.
- Make sure the data has a single header row. Place a descriptive label at the top of each column, such as Customer or Date. Avoid blank rows or blank columns inside the data, and do not merge cells in the data area. A blank line between records can make Excel interpret the list as separate ranges, while merged cells can prevent a clean sort.
- Decide what you mean by a group. To put matching records beside one another, use a shared value in a column: all records for the same customer, for example, have the same Customer value. If you mean sections that can be expanded and collapsed, use Excel’s outline grouping instead; that feature is explained below and does not create sort categories.
- Optionally convert the range to an Excel table. Click a cell in the data, choose Insert > Table, confirm the range, and select My table has headers if the first row contains headings. Choose OK. Tables make it easier to identify the full dataset and provide filter arrows in the headers. If Excel selects the wrong range, correct it in the Create Table dialog before confirming.
- Select a cell in the dataset. Click any cell inside the data or table. If the range is not a table, check that Excel recognizes the full block of data, including every column you want to keep together. You can select the full range yourself by dragging across it, but never select only the group column when the other columns contain related information.
- Open the full Sort dialog. On the ribbon, choose Data > Sort. The Sort dialog gives you options for more than one sort criterion. If you use a table, you can also use a header’s filter arrow for a simple one-column sort; use Data > Sort when you need multiple levels or more control.
- Set the primary group field. In the Sort dialog, select the group-identifying column in the Sort by list, such as Customer or Department. For Sort On, choose Cell Values. Under Order, choose A to Z for text or Smallest to Largest for numbers, as appropriate. Use the opposite order if you want the groups in reverse order. If Excel displays a My data has headers option, make sure it is selected so the heading is not sorted into the records.
- Add a second sort level if useful. Choose Add Level, then select the column that should control the order inside each group. For example, choose Date and Oldest to Newest to list each customer’s sales in date order. Excel applies the first level to form adjacent groups, then sorts records that share that first-level value by the next level. Add further levels only when you need another tie-breaker, such as Product after Customer and Date.
- Confirm the selection and sort. Choose OK. If Excel asks whether to expand the selection, choose Expand the selection so that the other columns move with the sorted column. Choosing Continue with the current selection can separate names or values from their original records. After the sort, check a few rows to confirm that each record is intact and that the group order is what you intended.
- Restore the original order when needed. If you need to be able to return to the starting sequence, add a column before sorting, label it Original order, and fill it with 1, 2, 3, and so on for the records. Later, sort by that column from smallest to largest. This is more reliable than relying on a second sort to reverse the first one. You can remove the helper column after you have restored the order.
For example, suppose the rows list customer purchases. Sorting by Customer A to Z makes each customer’s records consecutive. Adding Date as the next level orders each customer’s purchases by date without mixing one customer’s rows with another’s. If two records have the same customer and date, add a third level such as Product or Invoice number to make their relative order predictable.
If you specifically want rows that are already adjacent to collapse under a minus button, select the detail rows and choose Data > Group in the Outline area. Excel adds outline controls at the left of the sheet. This is useful for hiding and showing sections, but it is not a replacement for sorting by a shared value. Outline groups are positional: sorting the rows afterward can change which records sit together, so establish and verify the sort order before creating outline groups.
Common Mistakes
- Sorting one column by itself. This rearranges the values in that column without reliably moving their associated fields. Select the whole dataset, then expand the selection if Excel prompts you.
- Using Data > Group to sort records. Group on the Data tab creates collapsible outline levels. To put records with matching values together, sort the data by the matching field.
- Leaving blank rows inside the list. Excel may treat a blank row as the end of the data region and sort only part of the records. Remove internal blank rows or select the entire range explicitly before opening Sort.
- Sorting headings as if they were records. Confirm that the header row is identified as a header. Otherwise, a heading such as Customer could move into the data and make the result confusing.
- Expecting identical labels to match when they do not. Extra spaces, inconsistent spelling, or different number and text formats can create separate sort positions. For instance, North and North followed by a trailing space may look alike but are not identical values.
Tips
- Use a table for lists that grow over time. Table filter arrows make quick sorting accessible, and the table’s columns make it easier to keep a record’s fields together. Add new records directly below the table so Excel can include them.
- Sort from the broadest grouping to the most detailed order: Department first, Employee second, and Date third, for example. Each additional sort level applies within ties from the levels above it.
- If you need a custom group sequence, create a helper column with a rank such as 1 for Urgent, 2 for Standard, and 3 for Low. Sort by that rank, then by any additional field. Alphabetical order alone may not match the sequence people expect.
- Keep an Original order column when the current sequence carries meaning, such as entry order or an approved review sequence. A normal sort does not preserve a dependable undo path once you make other workbook changes.
- On Excel for Mac, the Data tab and Sort dialog provide the same general sort-level workflow, though ribbon placement and dialog appearance can differ. In Excel for the web, use the Data tab’s sort controls; a desktop version may offer additional options depending on the workbook and feature.
Troubleshooting
- Only one column moved. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac), then repeat the sort with the full range selected. If prompted, choose Expand the selection. If the row relationships are already lost and undo is no longer available, restore a saved copy or use an Original order or unique record ID to match fields back to their records.
- Some records did not sort. Look for blank rows or columns, hidden sections, or data outside the selected range. Select the entire intended range and use Data > Sort again. If it is a table, check that every record is inside the table; resize the table if necessary.
- Groups appear in an unexpected order. Check the Sort by column and its Order setting. Text values sort alphabetically, not necessarily in a business-specific sequence. Use a rank helper column for a custom order, then sort by that column.
- Dates or numbers sort incorrectly. Values stored as text can sort differently from actual dates or numbers. Check a few cells’ formats and correct the underlying values so the column uses one consistent data type. A date displayed in a date format may still be text if it was imported that way.
- The headings were included in the sort. Reopen Data > Sort and confirm My data has headers is selected. If the setting is unavailable or the range is unclear, select the data including its heading row and open the Sort dialog again.
- Sort is unavailable or the result is blocked. Check whether the worksheet is protected or the data contains merged cells. Unprotect the sheet if you have permission, and unmerge cells in the sort range while preserving their contents. If a filter is active, clear or review it so you know whether hidden records are also part of the sort.
Frequently Asked Questions
Does Data > Group sort rows into categories?
No. It creates outline sections that can be collapsed or expanded. To place records with matching categories together, sort the full dataset by the category column.
How do I keep two or more rows together while sorting?
Put the information that belongs together in the same record row, then sort the entire range or table. If a related item spans multiple rows, add a shared group ID to every row and sort by that ID, followed by a detail field.
Can I sort by a group and then by date?
Yes. Choose Data > Sort, set the group field as the first level, choose Add Level, and set Date as the next level. Check that the date values are real dates and choose the desired date order.
Can I collapse each sorted group after sorting?
Not automatically based on matching values. Data > Group creates outline levels from selected adjacent rows. First sort so each group is contiguous, then select the rows for each section and apply outline grouping if you want collapsible sections.
Will sorting a filtered list keep records together?
A sort on a properly structured table or range moves complete records together. Review the filter because hidden rows may affect which records you see, and clear it if you need to inspect the full sorted list.
Related
- Excel Jungle Data Analysis: A Practical Field Guide
- Excel Data Analysis: Methods, Tools, and Examples
- Excel GIS Data Analysis: Maps, Methods, and Limits Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Calendar Share Request: How to Send and Manage OneTo share your Outlook calendar, open Calendar, choose Share Calendar or Share, enter the recipient, set their permission…
- Add Page Numbers in Word: Steps, Sections, and FixesIn Microsoft Word for Microsoft 365 on Windows, select Insert > Page Number, choose where the number should…
- How to Add Audio to a PowerPoint PresentationTo add audio in PowerPoint for Microsoft 365 on Windows, open the slide, choose Insert > Audio >…
- How to Auto Wrap Text in Google Sheets: A Quick GuideTo automatically show long text on multiple lines in Google Sheets, select the cells, then choose Format >…
- Google Sheets: How to Sort by Last NameYes. Google Sheets can alphabetize a list by last name, but it does not automatically identify surnames inside…
- Excel Data Model and SharePoint: How They WorkYes. You can save an Excel workbook that contains a Data Model in a SharePoint document library; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.