Speaking of Excel, sorting is very important, very, very important. We all want our data to be sorted in multiple ways to get the expected results we are looking for. Sorting based on a single column is straightforward and easy, but what if you want your data to be sorted based on multiple columns? While this may sound impossible, with Excel it's just a piece of cake. You don’t need any coding, you don’t need to be an Excel Guru, you just need this Geek Page article to sort out all your multi-level sorting problems!
In this article, we explain how to sort data in an Excel file based on multiple columns easily with 2 different methods. The first method uses the built-in sort window, while the second method uses the 2 sort icons available in Excel. No matter which method is used, the result is the same. You just have to choose your preferred method! So what are you waiting for? Let’s jump right into the article!
In this article, we have created a sample table to explain the multi-level sorting technique. In the table below, I have 3 columns namely Name, Subject and Marks.
I wish to sort the table based on Name and Marks so that Names starts from Sort from minimum to large, Marks sort from large to minimum. So the end result should show the names in ascending order and I should be able to see the highest score that person got first. See the screenshot below to get a better idea of the scenario.
This method uses the Sort window readily available in Excel.
In the next steps, let’s see how to easily sort data based on multiple columns using this simple method.
Step 1: First select the data to be sorted.
Now click on the Data tab on the top ribbon and click on the Sort button.
Step 2: Now in the Sort window, set your first sorting criterion.
Select the first column you wish to sort on. You can select a column name from the Sort by drop-down menu.
Next, set the order in which you want your data to be sorted. Select A through Z from the Order drop-down menu.
After setting the first sorting criterion, click the "Add Level" button at the top.
NOTE : For this method to work, you must sort the columns from smallest to largest. That is, as a first criterion you need to give the column you want to sort from smallest to largest . As a second criterion, you need to give the largest column to the smallest column. Additionally, you can add as many levels as you want.
Step 3: Next, set the second criterion the same way. Select the column from the Then by drop-down menu and set the order from largest to smallest in the Order drop-down menu.
Click the OK button.
Step 4: That’s it. Your data is now sorted on multiple levels. enjoy!
This method is also very simple. In the previous method, you must first sort the smallest to largest columns. This approach is just the opposite. In this approach you have to sort largest to smallest column first. Let's see how to perform multi-level sorting using the sort icon.
Step 1: First, select the columns that need to be sorted from large to small. In the example scenario, I want the Marks column to be sorted from largest to smallest , so I select the Marks column.
Now, click on the Data tab at the top.
Next, under the Data option, click the Z -> A sort icon.
Step 2: When you get the "Sort Warning" dialog box, click the radio button corresponding to the Expand Selection and then click the "Sort" button.
Step 3: If you look at the Excel sheet now, you can see that Marks are from Maximum to Sorted by smallest . Now let's move on to sorting the Name column.
Next select the Name column. Click the Data tab at the top, then click the A -> Zsort icon.
Step 4: In the "Sort Warning" dialog box, select again with Expand SelectionThe radio button corresponding to the option, and then click the "Sort" button.
Step 5: That’s it. Together these two sortings result in a multi-level sorting and your table is now nicely sorted!
We’d love to know which method you chose as your favorite. Our favorite is the first method because it's more straightforward and a one-step solution.
The above is the detailed content of How to sort multi-level data in Excel. For more information, please follow other related articles on the PHP Chinese website!