How to sort data by multiple columns in Excel

Sorts are a simple 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 two types of custom sorts: by 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 as Monday, Tuesday and so on, or Small, Medium and Large. In this article, I’ll show you how to sort by multiple columns. For instance, you might want to sort a sales sheet by personnel and then region. The end result groups the personnel and then further groups each of those groups by region. There 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. Please use whichever you prefer throughout the article. I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use 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 task, and sorting by multiple columns is essentially the same thing. You have a primary sort on a specific column and then a secondary sort within the results of the primary sort. You’re not limited to two columns either, but we’ll keep the examples simple. We’ll sort by personnel and region. You can sort by personnel or region, but using the Sort options alone, you can’t sort by both. In this case, you need to build a custom sort as follows: Click anywhere inside the data set, preferably in the Personnel column, but doing so isn’t necessary. In the Editing group (on the Home tab), click the Sort & Filter option, and choose Custom Sort from the dropdown list. If you clicked inside the Personnel column, the first Sort By setting in the result dialog should be Personnel. If you didn’t, choose Personnel now. The Sort On and Order options should be set to Cell Values and A To Z, respectively because these are the default settings. If necessary, make changes. At this point, you’ve satisfied the primary sort. To build the secondary sort, click the Add Level option. From the Then By dropdown, choose Region. Again, the Sort On and Order columns should be set to the defaults, Cell Values and A To Z (Figure A). Now you have both sort columns set, so click OK to execute the sort. As you can see in Figure B, Excel groups the names in the Personnel column in an ascending alphabetic sort. Then, the regions are sorted within each name group. James has three regions, Central Northwest, and Southwest. Then, drop down to Martha and Rosa. Their region groups are the same, Northwest and Southwest. How to add a third column to sort in Excel There are very few repeated dates, but there are a few repeated months. Let’s create a new custom sort on three columns: Date, Personnel and Region. To do so, we’ll first have to add a helper 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: Click anywhere inside the data set, preferably in the Date column, but doing so isn’t necessary. In the Editing group (on the Home tab), click the Sort & Filter option, and choose Custom Sort from the dropdown list. If you clicked inside the Date column, the first Sort By setting in the result dialog should be Date. If you didn’t before, choose Date now. The Sort On and Order options should be set to Cell Values and Oldest To Newest, respectively. If necessary, make those changes. At this point, you’ve satisfied the primary sort. To build the secondary sort, click the Add Level option. From the Then By dropdown, choose Personnel. Again, the Sort On and Order columns should be set to the defaults, Cell Values and A To Z. Click Add Level, choose Region from the Then By dropdown (Figure C), and click OK to execute the sort. Figure C The data set is simple on purpose so you can easily discern the different groups as a result of the multiple column sorts. For each group, simply add a new sort level.