Sorting a dataset by several criteria can become confusing when each column has a different priority. Many Excel users need to organize records by multiple columns but risk incorrect results, such as separated rows, wrong priority order, or inconsistent sorting. This guide shows how to sort multiple columns in Excel using Custom Sort, preserve complete records, and choose the right method for different spreadsheet situations.
To sort multiple columns together in Excel, select the full data range, open the Data tab, choose Sort, and add multiple levels in the Sort dialog box. Set the first column as the primary sort, use Then by levels for secondary criteria, choose ascending or descending order for each level, and apply the sort so all related cells in each row move together.
How to sort multiple columns in Excel using custom sort
The most reliable way to sort a table by several criteria is to use Excel’s Custom Sort feature. This lets you define the priority order instead of applying separate sorts that may change the relationship between columns.
For example, imagine a sales worksheet with columns for Region, Salesperson, Revenue, and Order Date. You may want to group regions alphabetically, sort salespeople within each region, and then display the highest revenue values first inside each salesperson group.
Follow these steps to create a multi-level sort:
- Select the entire dataset, including every column connected to the records you want to organize.
- Open the Data tab on the Excel ribbon.
- Select Sort to open the Sort dialog box.
- Confirm that the My data has headers option is selected if your first row contains column names.
- In the Sort by dropdown, choose the first column that controls the overall order.
- Choose the sorting direction, such as A to Z, Z to A, Smallest to Largest, or Largest to Smallest.
- Select Add Level to create another sorting rule.
- Choose the second column under Then by and set its order.
- Add additional levels if the dataset requires more criteria.
- Select OK to apply the complete sort.
A three-level example makes the priority clearer:
Situation: A sales table contains Region, Salesperson, and Revenue columns.
Steps:
- Select the complete table range containing Region, Salesperson, Revenue, and related fields.
- Open the Sort dialog and set Region as the first level with A-Z order.
- Add Salesperson as the second level with A-Z order, then add Revenue as the third level with Largest to Smallest order.
Result: The table is grouped by Region first, names are arranged within each region, and higher revenue records appear first when Region and Salesperson values match.
Note: The position of each sort level changes the final output. Excel applies the first level before later levels, so the first sorting rule should represent the most important grouping requirement.
Understanding sort levels and how they work together
A sort level is a rule that tells Excel which column to organize first, second, third, and so on. When multiple columns contain related information, the first level creates the broad structure while later levels refine the order inside matching groups.
The first level is the primary sort. It determines the largest grouping in the final dataset. For example, sorting by Department first creates department groups before any employee names or salaries are considered.
The second and later levels only affect rows that have identical values in previous levels. If Region is the first level and several rows all contain "West," Excel then uses the Salesperson level to organize only those West records.
Excel processes sort levels from top to bottom in the Sort dialog box. A common mistake is placing a detailed column above a broader category column, which produces an unexpected arrangement because the priorities are reversed.
When deciding the order of sort levels, use this rule: place the column that defines the main grouping at the top, then add columns that refine the order inside those groups. For example, Region → Salesperson → Revenue follows a broader-to-narrower structure.
Preparing your data to avoid sorting mistakes
A correct sort depends on selecting the correct dataset before opening the Sort dialog box. Sorting only part of a table can separate values that belong together.
Before applying a multi-column sort, check these preparation points:
- Select the full data range, including every column that belongs to each record. For a customer table, this usually means including ID, name, address, date, and order information together.
- Confirm that the header row is included and that Excel recognizes column names instead of sorting them as normal data values.
- Look for blank columns inside the dataset because an empty column can cause Excel to interpret the data as separate ranges.
- Check hidden rows or columns that may affect what appears after sorting.
- Verify that values use consistent data types. For example, numbers stored as text may appear in a different order from actual numeric values.
A safer way to check the selection is to click inside the dataset before sorting and verify that all connected columns are highlighted when the range is selected.
Choosing between custom sort, individual column sort, and formulas
Different sorting methods solve different problems. The correct choice depends on whether the data rows must stay connected, whether the order needs to update automatically, and whether the sorting sequence follows normal alphabetical or numerical rules.
Table Title
| Sorting Method | Best For | Preserves Rows | Customizable Order | Use Cases |
|---|---|---|---|---|
| Custom Sort | Sorting one dataset by multiple priority levels | Yes, when the full table range is selected | Yes | Sort Region A-Z, then Salesperson A-Z, then Revenue largest to smallest |
| Individual Column Sort | Sorting separate lists where columns are not related | No, if columns are sorted independently | Limited | Alphabetizing a standalone name list or ordering values in an unrelated column |
| SORT or SORTBY Formula | Creating an automatically updating sorted view without changing source data | Yes, in the formula output range | Yes | Maintaining a live sorted report from changing source data |
The decision rule is simple:
| If | Then |
|---|---|
| The order of multiple columns determines the final arrangement of the same dataset | Use Custom Sort and define primary, secondary, and additional sort levels. |
| Each column contains an independent list with no relationship between rows | Sort the individual column separately because row preservation is not required. |
| The sorted result must update automatically while keeping the original data unchanged | Use SORT or SORTBY formulas to create a dynamic sorted output. |
Using custom sort for multi-level criteria
Custom Sort is the best choice when several columns describe the same record. It changes the order of complete rows while respecting the priority rules you define.
For example, a project tracker may need to show Department first, Priority second, and Due Date third. A Custom Sort keeps each task’s owner, status, and notes attached to the correct record.
Sorting individual columns independently
Sorting one column alone is only appropriate when the values have no relationship with neighboring columns. An example would be a standalone list of product codes that does not connect to prices, categories, or inventory counts.
In a normal table, sorting only one selected column creates a data integrity risk. The visible sign of this problem is when an ID, name, or date no longer matches the information beside it.
Using SORT and SORTBY formulas instead of built-in sort
Formula-based sorting is useful when the original dataset should remain unchanged and a separate sorted view is needed. Functions such as SORT and SORTBY are available in newer Excel versions, including many Microsoft 365 environments.
This approach is different from Custom Sort because the formula creates a new sorted output area rather than rearranging the original rows. It is useful for reports that need to refresh automatically when source data changes.
Creating custom sort orders beyond alphabetical and numerical
Alphabetical sorting does not always match business rules. Categories such as High, Medium, and Low have a meaningful order, but A-Z sorting would place them differently.
Excel allows you to create a custom list and use it as a sorting level.
For a priority tracking worksheet, you can sort using a sequence such as High, Medium, Low:
- Open the Sort dialog box from the Data tab.
- Select the Priority column in the Sort by dropdown.
- Choose Custom List in the order option.
- Define the required sequence, such as High, Medium, Low.
- Apply the sort to arrange records according to that custom order.
The result is a table where High priority items appear first, followed by Medium and Low items, without changing the actual values stored in the Priority column.
This method also works for ordered categories such as months, workflow stages, or department-specific sequences where alphabetical order would create the wrong arrangement.
Preserving row relationships and data integrity
When sorting Excel spreadsheets, protecting row relationships is the most important step. A customer record, sales transaction, or employee entry is made of multiple connected cells across a row.
In practical spreadsheet workflows, the easy-to-miss step is verifying that every related column is included before applying the sort. A correct multi-column sort moves complete records together; it does not rearrange isolated columns.
Use this post-sort check:
- Compare the first record before and after sorting to confirm that all related fields moved together.
- Compare the last record before and after sorting to verify that the row structure remained intact.
- Confirm that key fields such as IDs, names, dates, or totals still match their original records.
- Check that the selected sort range included every column connected to the dataset.
If a mistake occurs, use Undo immediately rather than manually moving cells back into place. Manual repairs can create additional mismatches that are harder to detect.
Troubleshooting incorrect multi-column sort results
Incorrect sorting usually comes from one of three causes: incomplete selection, wrong sort levels, or inconsistent data formats.
A useful troubleshooting process is to check the visible symptom first, then identify the likely cause.
| Problem | Diagnostic Sign | Fix |
|---|---|---|
| Only one column appears sorted | Names, IDs, or related values no longer match their original records | Undo the action and repeat the sort using the full data range instead of a single column. |
| Headers are mixed into sorted data | Column titles appear among normal records | Reopen Sort and confirm that the header row is recognized. |
| Records appear in the wrong priority order | A secondary rule seems to control the entire table | Move the most important sorting rule to the top of the Sort dialog box. |
| Numbers or dates sort incorrectly | Values appear out of normal numeric or date sequence | Check whether some entries are stored as text instead of numbers or dates. |
A common practitioner pattern for diagnosing sort failures is to compare the Sort dialog box settings with the expected result. If the output does not match the rules shown in the dialog box, the issue is usually an incorrect level order or incomplete data selection.
Sorting multiple columns with tables and filters
Excel tables provide another way to manage sorting because each column includes a filter dropdown. This works well for quick sorting tasks, especially when working with structured Excel tables.
To sort a table:
- Click any cell inside the Excel table.
- Use the filter dropdown on a column header.
- Choose an ascending or descending sort option.
- For multiple criteria, open the Data tab and use Custom Sort instead of applying separate column sorts.
Table sorting is useful for routine organization, but complex datasets still benefit from the Sort dialog box because it clearly displays all sorting levels in one place.
When several criteria must be applied repeatedly, save the worksheet with the intended table structure and sorting workflow so future updates follow the same process.
Apply Custom Sort to a copy of your dataset today and add three levels—primary category, secondary identifier, and final value order—so you can verify that the records remain connected while producing the exact arrangement your report requires.
FAQ
Can you sort multiple columns in Excel without using the sort dialog box?
Yes, you can use table filter dropdowns for quick sorting tasks, but the Sort dialog box is the most reliable option when multiple criteria must be applied. It lets you define primary, secondary, and additional sort levels in the correct priority order.
What is the difference between sorting multiple columns and sorting one column multiple times?
Sorting multiple columns uses defined levels where the first column controls the main grouping and later columns refine matching records. Sorting one column multiple times can change priorities unpredictably and may not preserve the intended relationship between columns.
How do you reset a multi-column sort to the original data order?
To return to the previous arrangement, use Undo immediately after applying an incorrect sort. Manual rearranging is more likely to create mismatches between related cells, so reversing the sort action is the safer approach when possible.
Can you sort multiple columns alphabetically if they have different data types?
Yes, but each column should use consistent data types for reliable results. Values stored as text may sort differently from actual numbers or dates, which can cause unexpected ordering in the final table.
Is there a keyboard shortcut to open the sort dialog in Excel?
The sorting workflow can be accessed from the Data tab by selecting Sort. The article does not specify a keyboard shortcut, so the exact shortcut depends on the Excel environment and available commands.