Paradigm: PixieMe/Shutterstock

Sorts are a unproblematic task in Microsoft Excel if all you need is an ascending or descending sort. Excel is smart enough that it can interpret the data range. Not all sorts are so easy. Fortunately, Excel offers ii types of custom sorts: past irregular terms and by multiple columns. The recent article, How to create a custom sort when a regular sort won't do in Excel, shows you how to create a unique list of sorting elements, such every bit Monday, Tuesday and then on, or Small, Medium and Large. In this article, I'll show you lot how to sort by multiple columns. For instance, you might want to sort a sales sheet by personnel and then region. The end issue groups the personnel and then farther groups each of those groups by region.

SEE: 83 Excel tips every user should chief (TechRepublic)

In that location are two ways to initiate a custom sort:

  • 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.

Delight employ whichever you prefer throughout the article.

I'm using Microsoft 365 on a Windows x 64-flake organization, just you lot can utilize older versions. For your convenience, you can download the demonstration .xlsx and .xls files. Excel Online supports sorting by multiple columns.

How to sort by two columns

Grouping is a common database job, and sorting past multiple columns is substantially the same affair. You have a principal sort on a specific column and and so a secondary sort within the results of the primary sort. Yous're not limited to two columns either, simply nosotros'll keep the examples uncomplicated.

Effigy A shows a uncomplicated data set of sales data for several people and the respective regions for each tape. Let's suppose you want to review the full commissions per personnel broken down by the regions. This requirement will demand a sort by multiple columns; the chief sort volition be on the Personnel column, and the secondary sort will exist on the Region column.

Figure A

We'll sort by personnel and region.

Y'all can sort by personnel or region, but using the Sort options alone, you can't sort past both. In this example, you demand to build a custom sort equally follows:

  1. Click anywhere inside the data set, preferably in the Personnel column, but doing then isn't necessary.
  2. In the Editing grouping (on the Home tab), click the Sort & Filter pick, and cull Custom Sort from the dropdown list.
  3. If you clicked inside the Personnel cavalcade, the commencement Sort By setting in the result dialog should exist Personnel. If y'all didn't, cull Personnel now. The Sort On and Club options should be set to Cell Values and A To Z, respectively because these are the default settings. If necessary, brand changes.
  4. At this bespeak, you lot've satisfied the master sort. To build the secondary sort, click the Add together Level choice.
  5. From the Then Past dropdown, choose Region. Once more, the Sort On and Order columns should exist set to the defaults, Cell Values and A To Z (Figure A).
  6. Now you take both sort columns prepare, so click OK to execute the sort.

As yous tin can see in Figure B, Excel groups the names in the Personnel cavalcade in an ascending alphabetic sort. And then, the regions are sorted within each name group. James has iii regions, Central Northwest, and Southwest. And so, drop downwards to Martha and Rosa. Their region groups are the same, Northwest and Southwest.

Effigy B

This sort was rather easy. At that place are simply two columns and they're contiguous. Let'south complicate the requirements just a chip with a second example.

How to add a 3rd column to sort in Excel

There are very few repeated dates, but at that place are a few repeated months. Let's create a new custom sort on three columns: Date, Personnel and Region. To do so, we'll outset take to add a helper column—i that returns the month. To do and then, insert a column betwixt Date and Value, enter the following office in C3 and re-create it to the remaining cells:

=Month(B3)

=Calendar month([@Appointment]) (if you're using a Table object)

Then, set the format to General. Doing so will brandish values that correspond the month. For instance, 1 is January, iv is April, 10 is Oct, so on. Now, let's ready up a new custom sort:

  1. Click anywhere inside the data set, preferably in the Appointment column, but doing so isn't necessary.
  2. In the Editing group (on the Home tab), click the Sort & Filter option, and choose Custom Sort from the dropdown list.
  3. If yous clicked inside the Date column, the first Sort By setting in the result dialog should be Appointment. If yous didn't before, choose Engagement now. The Sort On and Social club options should exist set to Cell Values and Oldest To Newest, respectively. If necessary, make those changes.
  4. At this indicate, you've satisfied the primary sort. To build the secondary sort, click the Add Level pick.
  5. From the Then By dropdown, choose Personnel. Again, the Sort On and Order columns should be ready to the defaults, Cell Values and A To Z.
  6. Click Add Level, choose Region from the Then By dropdown (Effigy C), and click OK to execute the sort.

Effigy C

Equally you can see in Effigy D, January 31 is the first date grouping. James is the only person with a sale in January, but he has 2 and the regions are alphabetically sorted. Luke and Martha are sorted within the February 28 group, and both have one region, Northwest.

Figure D

The information set up is simple on purpose and so you lot can easily discern the dissimilar groups equally a issue of the multiple column sorts. For each grouping, merely add a new sort level.