In this tutorial, I cover how to do a multi-level sorting in Excel. You can watch the video below, or you can read the tutorial below it.
In most of the cases, you need to sort a single column.
But in some cases, there may be a need to sort two columns or more than two columns.
For example, in the below dataset, I want to sort the data by the Region column and then by the Sales Column. This will allow me to see which sales rep has done well in which regions.
While it's straightforward to sort data by one column in Excel, when it comes to sorting by two columns, you need to take a couple of additional steps.
In this tutorial, I will show you two ways to do a multiple level data sorting in Excel (i.e., sort by two columns)
Multi-Level Sorting Using Dialog Box
When you sort data using the sort dialog box, you get an option to add multiple levels to it.
Here are the steps to do multi-level sorting using the dialog box:
- Select the entire data set that you want to sort.
- Click the Data tab.
- Click on the Sort Icon (the one shown below). This will open the Sort dialog box.
- In the Sort Dialogue box, make the following selections
- Sort by (Column): Region (this is the first level of sorting)
- Sort On: Values
- Order: A to Z
- If your data has headers, ensure that ‘My data has headers' option is checked.
- Click on Add Level (this will add another level of sorting options).
- In the second level of sorting, make the following selections:
- Then by (Column): Sales
- Sort On: Values
- Order: Largest to Smallest
- Click OK
The above steps would give you the result as shown below. This sorts the data first by Region and then by Sales column. Note that since it sorts the Region column first when the Sales column is sorted, the Region column remains unchanged.
In this example, I have sorted the data for two columns. You can have more than two-column sorting as well. All you need to do is add these sorting levels and specify the details.
Note: While this method is longer and takes a few more steps (as compared with the multi-sorting method covered next), I recommend using this as it is less confusing.
Multi-Level Sorting Using Sort Icons
Not many people know this way of doing a multiple level data sorting in Excel.
This technique works the same way with a minor difference – you sort the second level first and then move to the first level sorting column.
Here are the steps to do it:
- Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
- Click on the Data tab.
- In the Sort and Filter group, click on the Z to A sorting icon. This will sort the sales data from largest to smallest.
- The above step would make a Sort Warning dialog box pop-up. Make sure ‘Expand the selection' is selected. This makes sure the entire dataset is sorted, and not just data in the Sales column.
- Click Sort.
- Select the Region column.
- In the Data tab, click on the A to Z sort icon.
- In the Sort Warning dialog box pop-up, make sure ‘Expand the selection' is selected.
- Click Sort.
The above steps would sort the data just like it did in the first method.
While this method works fine, I recommend using the sort dialog bo method.
Sort dialog box makes it less error-prone (as you can see which levels getting sorted in which order). Also, there are more ways to sort data with the dialog box. For example, you can sort a column based on the cell/font color and you can also use your own custom sorting criteria.
You May Also Like the Following Excel Tutorials:
- Creating Your Own Sorting Criteria.
- Automatically Sort Data in Alphabetical Oder using Excel Formulas.
- How to Sort Data in Excel using VBA (A Step-by-Step Guide).