How to sort data by multiple columns in Excel

Classification is one of the simplest tasks in Microsoft Excel. Click; you’re done! At least until you need to sort on multiple columns. For this classification task, you will need a custom classification. That’s how.

Image: PixieMe / Shutterstock

Rankings are a simple task in Microsoft Excel if all you need is an ascending or descending ranking. Excel is smart enough to interpret the data range. Not all kinds of things are that easy. Fortunately, Excel offers two types of custom sorts: by irregular terms and by multiple columns. The recent article, How to create a custom ranking when a normal ranking doesn’t work in Excel, shows you how to create a single list of ranking items, such as Monday, Tuesday, etc., or small, medium, and large. In this article, I will show you how to sort by multiple columns. For example, you might want to sort a sales sheet by personnel and then by region. The end result groups the staff together and then groups each of those groups by region.

SEE: 83 Excel Tips Every User Should Master (TechRepublic)

There are two ways to start a custom classification:

  • On the Home tab, click Sort in the Editing group, and click Custom Sort.
  • On the Data tab, click Sort in the Sort & Filter group.

Use whichever you prefer throughout the article.

I am using Microsoft 365 on a Windows 10 64 bit system, but it can use older versions. For your convenience, you can download the demo files .xlsx and .xls. Excel Online supports multi-column sorting.

How to sort by two columns

Grouping is a common database task and multi-column sorting is essentially the same. It has a primary rank in a specific column and then a secondary rank within the primary rank results. You are not limited to two columns either, but we will keep the examples simple.

Figure a displays a simple dataset of sales data for various people and the respective regions for each record. Let’s say you want to review the total staff commissions broken down by region. This requirement will necessitate a multi-column sort; the primary classification will be in the Personal column and the secondary classification will be in the Region column.

Figure a

excelmulticolsort-a.jpg

We will classify by staff and region.

You can sort by staff or region, but using only the Sort options, you cannot sort by both. In this case, you need to create a custom sort as follows:

  1. Click anywhere within the dataset, preferably in the Personal column, but you don’t have to.
  2. In the Editing group (on the Home tab), click the Sort & Filter option and choose Custom Sort from the drop-down list.
  3. If you clicked inside the Personal column, the first Sort By setting in the results dialog should be Personal. If you didn’t, choose Personal now. The Sort By and Sort options should be set to Cell Values ​​and A through Z, respectively, because these are the default settings. If necessary, make changes.
  4. At this point, you have satisfied the main type. To create the secondary classification, click the Add Level option.
  5. From the Then By drop-down menu, choose Region. Again, the Sort By and Sort columns should be set to the default values, Cell Values ​​and A to Z (Figure a).
  6. You now have both sort columns configured, so click OK to run the sort.

As you can see in Figure B, Excel groups the names in the Personal column in ascending alphabetical order. The regions are then ordered within each group of names. James has three regions, the central northwest and the southwest. Then scroll over to Martha and Rosa. Their regional groups are the same, Northwest and Southwest.

Figure B

excelmulticolsort-b.jpg

The custom classification groups the regions within the group of names.

This guy was pretty easy. There are only two columns and they are contiguous. Let’s complicate the requirements a bit with a second example.

How to add a third column for sorting in Excel

There are very few repeating dates, but there are some repeating months. Let’s create a new custom classification in three columns: Date, Personnel, and Region. To do this, we will first need to add an auxiliary column, one that returns the month. To do so, insert a column between Date and Value, enter the following function in C3 and copy it to the remaining cells:

= Month (B3)

= Month ([@Date]) (if you are using a Table object)

Then set the format to General. Doing so will display the values ​​that represent the month. For example, 1 is January, 4 is April, 10 is October, and so on. Now, let’s set up a new custom sort:

  1. Click anywhere within the dataset, preferably in the Date column, but you don’t have to.
  2. In the Editing group (on the Home tab), click the Sort & Filter option and choose Custom Sort from the drop-down list.
  3. If you clicked inside the Date column, the first Sort By setting in the results dialog should be Date. If you haven’t done it before, choose Date Now. The Sort By and Sort options should be set to Cell Values ​​and Oldest to Newest, respectively. If necessary, make those changes.
  4. At this point, you have satisfied the main type. To create the secondary classification, click the Add Level option.
  5. In the Then By drop-down menu, choose Personal. Again, the Sort By and Sort columns should be set to the default values, Cell Values ​​and A to Z.
  6. Click Add Level, choose Region from the drop-down menu Then by (Figure C) and click OK to execute the sort.

Figure C

excelmulticolsort-c.jpg

Run custom sort on three columns.

As you can see in Figure D, January 31 is the first set of dates. James is the only person with an offer in January, but he has two and the regions are arranged alphabetically. Luke and Martha are classified within the February 28 group, and they both have a region, Northwest.

Figure D

excelmulticolsort-d.jpg

Personnel classify within the date group and regions classify within the personnel group.

The dataset is purposely simple so that you can easily discern the different groups as a result of the multiple types of columns. For each group, simply add a new classification level.

See also

Leave a Comment