Home > Common Problem > How to sort multi-level data in Excel

How to sort multi-level data in Excel

WBOY
Release: 2023-04-20 20:25:06
forward
3815 people have browsed it

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!

Sample Scenario

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

Solution 1: Perform a multi-level sort using the Sort Window

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

Step 4: That’s it. Your data is now sorted on multiple levels. enjoy!

如何在 Excel 中执行多级数据排序

Solution 2: Use the sort icon to perform multi-level sorting

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

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.

如何在 Excel 中执行多级数据排序

Step 5: That’s it. Together these two sortings result in a multi-level sorting and your table is now nicely sorted!

如何在 Excel 中执行多级数据排序

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!

Related labels:
source:yundongfang.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template