How to do Multiple Level Data Sorting in Excel (2 Methods)

Sorting is a basic need when we are working with data. And if you have large data sets, you may need to do a multiple level data sorting in Excel, which means that you first want you data to be sorted by one column and then by another. Something as shown below:

Multiple Level Data Sorting in Excel - Data

There are 2 ways you can do this.

Method 1 – Using Sort Dialogue Box

  1. Select the data set
  2. Go to Data –> Sort & Filter –> Sort
    Multiple Level Data Sorting in Excel - Data Sorting
  3. In the Sort Dialogue box, make the following selections
    • Sort by (Column): Sales Rep Name (this is the first level of sorting)
    • Sort On: Values
    • Order: A to Z
    •  If you data has headers, ensure that My data has headers option is checked
      Multiple Level Data Sorting in Excel - Data Sorting Level 1
  4. Click on Add Level (this adds another level of options for sorting)
    Multiple Level Data Sorting in Excel - Adding another level of sorting
  5. In the second level of sorting, make the following selections:
    • Then by (Column): Region
    • Sort On: Values
    • Order: A to Z
      Multiple Level Data Sorting in Excel - Added another level of sorting
  6. Click OK

This sorts the data first by Sales Rep Name and then by Region. Here I have taken an example of sorting by text only, but this would work with numbers as well. Also, you can have as many sorting levels as you want.

 

Method 2 – Using Sort Icons (Faster and Easier!!)

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 that you sort second level first, and then move to the first level sorting column.

And yes, it’s faster and easier.

Here is how to do it:

  1. Select the column that you want to be sorted last (in this case, select Region first)
  2. Go to Data –> Sort and Filter and click on the Sort A to Z icon. This will sort the Region Data with Canada at the top and US at the bottom
    Multiple Level Data Sorting in Excel - Sorting Icon
  3. Now select the Sales Rep column (the one that you want to sort first), Go to Data –> Sort and Filter and click in the Sort A to Z icon
    Multiple Level Data Sorting in Excel - Sorting Icon
  4. That’s it. The data is sorted first Sales Rep Name and then within with Region Name. If you have three levels of sorting, start by sorting the third column, followed by second and then first

This simple technique gives you the same result as that by sort dialogue box, but it takes only a couple of clicks. In case you are sorting on anything other than values, you can use Method 1.

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)