To sort Excel data by multiple columns, select a cell in the data, choose Data > Sort, then use Add Level to set the first sort column and each tie-breaker. If you mean a worksheet formula rather than the Sort command, use SORTBY for multiple sort keys, such as =SORTBY(A2:C5,B2:B5,1,C2:C5,-1).
Multi-column sorting keeps each row together while arranging records by a primary rule and then secondary rules. It is useful for lists such as sales by region and date, names by department and surname, or inventory by category and quantity. The key is to select the whole record range and define the sort levels in priority order.
What sorting by multiple columns means
A multi-level sort applies one rule first, then uses the next rule only to order records tied under the preceding rule. For example, sorting a staff list by Department and then Last Name groups employees by department and alphabetizes names within each group. The second sort does not override the first; it resolves ties inside it.
Think of the levels as a hierarchy. The first level has the greatest influence, the second matters only when values in the first level match, and a third level breaks remaining ties. A useful order for a sales table might be Region ascending, Sales descending, then Customer ascending. That produces regional groups, puts the largest sales first within each region, and alphabetizes customers when both region and sales match.
Sorting is different from filtering. A sort changes the order of records shown in the range or table; a filter hides records that do not meet criteria. Neither operation should separate values from their related row when the data range is selected correctly.
Sort multiple columns with the Sort dialog
In Excel for Microsoft 365 on Windows, follow these steps:
- Click a cell inside the complete data range. If the data has blank rows or columns splitting it into separate blocks, select the intended range manually.
- Choose Data > Sort. The Sort dialog lets you create and rearrange several levels.
- If the first row contains headings, select My data has headers. This keeps headings out of the sorted records and displays their names in the Sort by lists.
- Under Sort by, choose the primary column. Choose what to sort on, usually Cell Values, and select an order such as A to Z, Smallest to Largest, or Oldest to Newest.
- Choose Add Level to add a tie-breaker. Set its column and order, and repeat for any further levels.
- Check that the levels are in the intended priority order, then select OK.
Excel for Mac has a Sort command on the Data tab as well, although the ribbon layout and dialog appearance can vary by release. In Excel for the web, use the Data tab’s sort options; the full custom-sort dialog and available choices can differ from the desktop app. If you need several levels and do not see the options you need in the web version, open the workbook in desktop Excel.
Example: sort a sales list by region and amount
Suppose columns A through C contain Item, Region, and Sales, with records in rows 2 through 5. To group regions alphabetically and rank sales from highest to lowest inside each region, make Region the first level with A to Z order, then add Sales as the second level with Largest to Smallest order. If two records have the same region and sales, add Item as a third level with A to Z order to make their relative order predictable.
The level order matters. If Sales is the first level instead, Excel sorts the entire list by sales before considering region, so records from different regions can be intermingled. That is appropriate when the overall ranking is the goal, but not when the main goal is to keep each region together. Before clicking OK, ask which column should define the largest groups; that column usually belongs at the top of the level list.
When you need a reproducible result, include enough levels to distinguish records. Excel does not need a tie-breaker to sort successfully, but rows tied on every chosen level may retain an order that is not meaningful to your task. A unique ID or another stable field can serve as a final level if consistent ordering matters.
Use a formula to return sorted data
The worksheet function SORT returns a sorted array, but it sorts by one index in the selected array. It does not provide a list of multiple sort keys. In Microsoft 365 or Excel 2021 and later, use SORTBY when you need multiple keys. Its arguments pair the array to return with one or more sort arrays and their sort orders. A sort order of 1 means ascending; -1 means descending.
Assume A2:C5 contains these records: Pen, East, 120; Book, West, 200; Clip, East, 150; and Pad, West, 90. With the complete records in A2:C5, regions in B2:B5, and sales in C2:C5, enter =SORTBY(A2:C5,B2:B5,1,C2:C5,-1) in an empty cell. The result is Clip, East, 150; Pen, East, 120; Book, West, 200; Pad, West, 90. It orders regions ascending, then sales descending within each region.
With the same layout, =SORT(A2:C5,3,-1) sorts the selected records by the third column of the array, Sales, in descending order. Its result is Book, West, 200; Clip, East, 150; Pen, East, 120; Pad, West, 90. This is a single-key sort, not a region-then-sales sort. Both functions spill their results into neighboring cells, so leave enough empty space for the returned array. If cells in the spill area are occupied, Excel can return a spill error. These functions do not rearrange the source records; they display a sorted result separately.
Prepare the data before sorting
Excel can sort reliably only when it can identify the records and interpret each field consistently. A tidy rectangular table is easier to sort than a worksheet with explanatory notes, blank spacer rows, or multiple unrelated blocks mixed into the range. Consider converting a dataset to an Excel table with Insert > Table or Ctrl+T on Windows. On Mac, use Control+T or the Table command on the ribbon, depending on keyboard settings and version. Tables include filter and sort controls in their headers and generally expand as records are added.
- Use one header row with a distinct label for each column. Avoid merged cells in the data range.
- Keep one kind of information in each column. For example, separate a date from a note about the date rather than mixing both in one field.
- Check that values intended to be numbers or dates are stored consistently. Numbers stored as text can sort differently from numeric values.
- Remove accidental blank rows inside the records, or select the full intended range explicitly before sorting.
- Keep associated fields in the same range. Sorting only the Sales column can detach sales figures from their items and regions.
When you select a cell in a contiguous block and start sorting, Excel usually identifies the surrounding data. It may ask whether to expand the selection if it detects adjacent data outside the selected column. Choose Expand the selection when those neighboring cells belong to the same records. Choose to continue with the current selection only when you intentionally want to sort that column independently, which is uncommon for a record table.
Other sort options and sort orders
The Sort dialog can order data by more than ordinary text or numbers. Depending on the selected range and Excel version, you can sort by cell values, cell color, font color, or conditional-formatting icon. These choices can help prioritize flagged records, but color sorting is not a substitute for a clear status column: a text field such as Open, Review, or Complete is easier to audit and maintain.
For a non-alphabetical sequence, Excel supports custom lists such as weekdays or months. In the order control, choose a custom list when available, or use the custom-list settings in Excel to define a sequence specific to your work. This is useful for priorities like High, Medium, Low, where alphabetical order would be wrong. The Sort dialog also includes options such as sorting left to right rather than top to bottom, and a case-sensitive option for text. Most ordinary tables should remain top to bottom and not require case-sensitive sorting.
For a one-off sort on a table, a header’s filter arrow can sort that column ascending or descending. That is convenient for a simple sort, but use Data > Sort when you want to inspect and manage multiple levels explicitly. Custom order, color-based sorting, and orientation settings are also easier to review in the dialog.
Common sorting problems and fixes
Rows no longer match their original values. This usually happens when only one column was sorted. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then select the full dataset and sort again. Undo may not be available after later actions or after closing and reopening the workbook, so keep a backup when rearranging important data.
The header is sorted into the records. Open the Sort dialog and select My data has headers. If Excel does not recognize the header row, verify that the range begins at the headings and that the headings are in a single row.
Numbers or dates appear in an unexpected order. Mixed data types are a common cause. For example, a value such as 100 stored as text may not behave like a numeric 100. Check number formats and underlying values, and convert inconsistent entries to one data type before sorting. A display format alone does not always convert text into a number.
Some records seem to be missing from the sort. Look for blank rows or columns that may have caused Excel to treat part of the worksheet as a separate block. Select the complete range manually or make it a table. Also check for active filters, which can hide records without deleting them.
The result changes after an edit. A formula-based SORTBY result recalculates when its source data changes, while a command-based sort rearranges the data in place and stays in that order until sorted again. Choose based on whether you want a live sorted view or to reorganize the original records.
Choose the Sort command or a formula
Use the Sort dialog when the goal is to reorder the original rows, apply custom lists, sort by color, or create a result that stays put until you sort again. It works well for occasional cleanup and for tables that people edit directly. Because it changes the row order, a user should understand that the source data has been rearranged.
Use SORTBY when you want a separate, automatically updated sorted view while preserving the source order. This is useful for reports, dashboards, or a view that should update as records change. The formula’s output is an array, so it cannot spill into occupied cells. Formula-based sorting also depends on function support: SORT and SORTBY are available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions. In an older version, use the Sort dialog instead.
Neither option is universally better. For collaborative data entry, keep a clear original table and consider showing a separate formula result for readers. For a finalized one-time list, sorting the table directly may be simpler. If preserving the original sequence is important, add an original-order index column before using the Sort command so that you can later restore that sequence.
Frequently Asked Questions
Can Excel sort by more than two columns?
Yes. In Data > Sort, choose Add Level for each additional rule. The level at the top is applied first, and lower levels break ties. You can also supply several sort keys to SORTBY in supported Excel versions.
Does the SORT function sort by multiple columns?SORT sorts by one row or column index within its array. For multiple criteria, use SORTBY, which accepts multiple sort arrays and sort orders. For example, it can sort by Region ascending and then Sales descending.
How do I sort by last name when names are in one column?
If each cell contains a full name, Excel treats the entire cell as one text value; it does not infer the surname as a separate field. Split names into separate columns, or add a helper column that extracts the surname, then sort by that column. Check how the data handles middle names and suffixes before relying on a simple split.
Can I sort by color or a custom order?
Yes. In the Sort dialog, choose cell color, font color, or a conditional-formatting icon when that option is available. For sequences such as High, Medium, Low or Monday through Sunday, use a custom list rather than ordinary A-to-Z order.
Will a formula sort change my original data?
No. SORT and SORTBY return a sorted array in another area of the sheet and leave the source rows in place. The Data tab’s Sort command, by contrast, changes the order of the selected records.
Related
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
- Excel Filter by Named Range: What Works and HowYes—but Excel’s standard Filter dropdown does not accept a named range as a direct filter condition. You can…
- Show Formulas in Excel: Keyboard Shortcut GuideTo show formulas in Excel for Microsoft 365 on Windows, press Ctrl+` (Ctrl plus the grave accent key,…
- Excel Formula for Dividing Two Cells: ExamplesTo divide two cells in Excel, use a forward slash in a formula: with the dividend in A1…
- Calculating r in Excel: Formula and ExamplesTo calculate Pearson’s correlation coefficient, or r, in Excel, use =CORREL(A2:A6,B2:B6) when the paired x values are in…
- Sort Excel by Column for the Entire WorksheetTo sort a column without separating its rows, click a cell in your data, choose Data > Sort,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.