To sort an Excel table by two columns in Microsoft 365 for Windows, click a cell in the table, choose Data > Sort, select the first column and its order, click Add Level, then select the second column and its order. Click OK: Excel sorts by the first column, and uses the second column to arrange records that share the same first-column value.
What sorting by two columns means
A two-column sort is a prioritized sort, not two separate sorts. Excel first groups or orders records according to your primary column. Within each group of equal values, it applies the secondary column. For example, sorting by Department from A to Z and then by Sales from largest to smallest puts departments in alphabetical order while showing the highest sales first within each department.
The order of the levels matters. If Department is the first level, Sales only affects the order of people in the same department. If Sales is the first level, the departments will instead be arranged according to sales, and Department will only break ties between equal sales figures. You can add further levels when you need more precise ordering, such as department, sales, and then employee name.
This approach is useful whenever one column alone does not provide the order you want. Common examples include sorting orders by delivery date and then order number, students by class and then surname, or products by category and then price.
Sort an Excel table using the Sort dialog
The Sort dialog is the clearest way to set multiple criteria because it displays the priority and direction of every level. Start from a cell inside the table; you do not need to select an individual column.
- Click any cell in the table. Confirm that the table has a header row with a distinct heading for each column.
- On the ribbon, choose Data > Sort. Excel opens the Sort dialog.
- If Excel has not identified the headings correctly, check or clear My data has headers. When the box is checked, the column headings appear as choices rather than being treated as records.
- Under Sort by, choose the primary column. Under Sort On, leave Cell Values for an ordinary text, number, or date sort.
- Choose an order, such as A to Z, Z to A, Smallest to Largest, Largest to Smallest, or Oldest to Newest, as appropriate for the data.
- Click Add Level. In the new Then by row, choose the secondary column and its order.
- Review the level order, then click OK.
In the Windows dialog, the levels are listed from highest priority to lowest. If you add them in the wrong order, select a level and use the move-up or move-down controls to change its priority. Avoid repeatedly sorting one column at a time to imitate a multi-level sort: later sorts can disrupt an earlier order. Setting all criteria in one dialog makes the intended hierarchy explicit.
Work through a concrete example
Suppose a table has Department, Employee, and Sales columns. You want departments alphabetically, with the strongest sales result first within each department. In Data > Sort, choose Department as the first level with A to Z, click Add Level, then choose Sales as the second level with Largest to Smallest.
For these five records, the resulting sequence is Sales—Bo—1200; Sales—Dee—900; Support—Cal—950; Support—Ana—820; Support—Eli—700. The 950 figure appears after the Sales records because Department is the primary sort. Within Support, however, Cal appears before Ana and Eli because Sales is descending. This illustrates why the secondary order does not rearrange records across different primary-column groups.
If two people in the same department have identical sales, both criteria tie for those records. You can add Employee as a third level, such as A to Z, to specify a further ordering. That makes the result easier to interpret and repeat, especially when many records have matching values.
Choose the right order and sort settings
Choose each level’s order based on the kind of value in that column. Text commonly uses A to Z or Z to A; numbers use smallest to largest or largest to smallest; dates use oldest to newest or newest to oldest. Excel offers orders appropriate to the selected column, so check that the displayed direction matches the question you are answering.
- Use a primary category such as region, team, or product type when you want records grouped before ranking within each group.
- Use a secondary number or date to rank records inside those groups, for example revenue descending or appointment date ascending.
- Add a third level when ties are common and a consistent tie-breaker is useful, such as customer name or record ID.
- Use a custom list when the desired order is meaningful but not alphabetical, such as High, Medium, Low. In the Sort dialog, choose Custom List when it is offered for the order.
Sorting by cell color, font color, or an icon is also possible through the Sort On choices when the data uses those formats. Those settings are different from sorting the underlying values: a colored cell may have a numeric value that does not correspond to its visual priority. For a routine two-column sort, Cell Values is usually the appropriate setting.
What happens to the table and its rows
Sorting a properly defined Excel table rearranges whole records, not just the cells in the selected column. That matters because a person’s name, department, and sales amount must remain on the same row after the order changes. Clicking inside the table before opening the Sort dialog helps Excel apply the sort to the table rather than to an unintended selection.
Excel tables have header filter arrows that can quickly sort one column, but those arrows are not the best interface for defining several priorities. Use Data > Sort to add and review levels. A table’s filter arrows can also hide records that do not meet a filter; sorting changes the order of records, whereas filtering controls which records are shown. These are separate operations and can be used together.
If the data is a normal range rather than a table, select a cell in the range and use Data > Sort. If Excel asks whether to expand the selection, choose Expand the selection so adjacent values in each record move together. Choosing Continue with the current selection can sort one column independently and mismatch the rows. Converting a well-structured range to a table with Insert > Table can make ongoing data management more convenient, but conversion is not required just to sort.
Fix common sorting problems
If the output looks wrong, first check the priority order in the Sort dialog. A frequent mistake is putting the ranking column first when the goal was to group by category. Reopen Data > Sort and confirm that the intended grouping column appears above the tie-breaker.
- Headings are sorted as records: Reopen the dialog and check My data has headers. Also verify that the table has one clear header row, rather than a blank or merged heading cell.
- Numbers appear in an unexpected order: Some entries may be numbers stored as text, or may contain spaces or other characters. Check their cell values and number formats; consistent numeric values sort more predictably.
- Dates do not sort chronologically: Dates stored as text may be ordered like words rather than as date values. Check whether Excel recognizes the entries as dates and convert inconsistent text dates before sorting.
- Rows no longer match: Undo immediately with Ctrl+Z on Windows, then repeat the sort from inside the table or expand the selection for a normal range. Do not sort only one column of a multi-column record.
- Some entries seem to be missing: Check for active filters, hidden rows, or records outside the table boundary. Clear the filter if you need to see all matching records and confirm that the table includes the complete data set.
Blank cells, duplicate values, and inconsistent spelling can also make a correct sort look surprising. For example, “North” and “North ” with a trailing space may behave like different text entries in related operations. Clean inconsistent source values when the intended groups should be identical. If the same sort needs to be run regularly, a third-level ID or name can make tied records easier to distinguish.
Use a formula for a separate sorted view
When you want to display a sorted copy without rearranging the source table, Microsoft 365 includes the dynamic-array function SORTBY. This can be useful on a separate worksheet or in an open area beside the original data. The formula returns a sorted result; it does not change the order of the source records.
Assume the example records are in A2:C6, with Department in A, Employee in B, and Sales in C, in the five-row example above. To return the rows ordered by Department ascending and Sales descending, enter =SORTBY(A2:C6,A2:A6,1,C2:C6,-1) in an empty cell. The result is Sales—Bo—1200, Sales—Dee—900, Support—Cal—950, Support—Ana—820, and Support—Eli—700. The sort-order value 1 means ascending and -1 means descending.
SORTBY is available in Microsoft 365 and Excel 2021 or later. It spills the results into neighboring cells, so those cells must be empty; otherwise Excel can show a spill error. Keep the source headers outside the formula range if you do not want them included in the output, and add a separate header row above the formula result if needed. For an ordinary table that should itself change order, the Sort dialog is usually simpler.
Windows, Mac, and Excel for the web
The steps above are for Microsoft 365 on Windows. Excel for Mac also provides a Sort command on the Data tab and supports multiple sort levels through its sort dialog; the dialog’s layout can differ slightly from Windows. Select a table cell, open the sort controls, add a level, and confirm the header setting rather than relying on a one-column header-arrow sort.
Excel for the web provides table sorting and filtering, but the ribbon and dialog layout can vary as Microsoft updates the web app. If the available controls do not make multiple levels easy to inspect, open the workbook in the desktop app and use Data > Sort. A SORTBY formula is another option for a separate, automatically recalculated sorted view in supported versions.
Frequently Asked Questions
Can I sort an Excel table by more than two columns?
Yes. In Data > Sort, use Add Level for each additional criterion, then put the most important criterion first. Later levels only resolve ties left by earlier levels.
Can I sort one column ascending and another descending?
Yes. Set the order separately for each level in the Sort dialog. For example, sort Department A to Z, then Sales Largest to Smallest.
Will sorting a table keep each row together?
Yes, when you sort the table itself, Excel rearranges complete table records. For a regular range, expand the selection when prompted so related columns move with the sorted column.
Why does my second sort seem to have no effect?
The secondary column only changes the order of records tied on the primary column. If every primary value is unique, there are no tied groups for the second level to rearrange.
Can I sort without changing the original table?
Yes. In Microsoft 365 or Excel 2021 and later, SORTBY can return a separately sorted dynamic-array view. Leave enough empty cells for the result to spill, and remember that it does not reorder the source table.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
- SUMPRODUCT IF Function: How to Sum with ConditionsNo, Excel does not have a separate SUMPRODUCTIF or “SUMPRODUCT IF” function. To sum values that meet conditions,…
- Excel Table Query: How to Filter, Transform, and Refresh DataIn Excel for Microsoft 365 on Windows, the usual way to query an Excel table is to select…
- Round Excel Cell Values: Formulas and Display OptionsTo round a number in an Excel cell and change the value used in calculations, use the ROUND…
- Excel IF Statements: Examples and How to Use ThemExcel IF statements let you test a condition and return one value when it is true and another…
- Excel Reverse HLOOKUP: Formulas and Better AlternativesExcel has no separate “reverse HLOOKUP” function. If you mean looking up a value in one row and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.