How to do a Multiple Level Data Sorting in Excel

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.

When working with data in Excel, sorting the data is one of the common things you might have to do.

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:

1. Select the entire data set that you want to sort.
2. Click the Data tab.
3. Click on the Sort Icon (the one shown below). This will open the Sort dialog box.
4. 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.
5. Click on Add Level (this will add another level of sorting options).
6. In the second level of sorting, make the following selections:
   • Then by (Column): Sales
   • Sort On: Values
   • Order: Largest to Smallest
7. 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:

1. Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
2. Click on the Data tab.
3. In the Sort and Filter group, click on the Z to A sorting icon. This will sort the sales data from largest to smallest.
4. 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.
5. Click Sort.
6. Select the Region column.
7. In the Data tab, click on the A to Z sort icon.
8. In the Sort Warning dialog box pop-up, make sure ‘Expand the selection’ is selected.
9. 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:

• Sort Data using Custom Lists
• Automatically Sort Data in Alphabetical Oder using Excel Formulas.
• How to Sort Data in Excel using VBA.
• How to do a Multiple Level Data Sorting in Excel
• Sort Worksheets in Excel using VBA (alphabetically)
• How to Sort by the Last Name in Excel
• How to Flip Data in Excel
• How to Undo Sort in Excel