How to sort using more than one column

In this lesson we'll look at how to sort data in a table based on values in more than one column. In Excel, this is referred to as a "custom sort."

Let's take a look.

Here we have a table that contains monthly sales data for a list of customers. In addition to monthly sales, it includes the customer name, city, state, date of first order, and total orders.

As we've seen previously, we can easily sort data like this using the quick sort buttons on the Data tab of the ribbon. For example, we could sort by the date of the first order, or by the state. But what if we want to sort by state, then city; or by state, and then orders? For that, we need to use custom sort.

To begin a custom sort in a table, select a cell anywhere in the table data, and click the Sort button. Excel will then open the Sort dialog box. The Sort dialog allows you to sort one or more columns, referred to as "levels." For each level, you select a column, the type of information to sort on, and a sort direction.

Note that Excel will try to guess if a table contains headers or not. In this case, Excel has correctly assumed headers, and on the worksheet you can see that headers are not selected. You can control this behavior by checking and unchecking this box.

To sort by State, then City, Choose State for the column. For Sort On, the default of "value" is fine. Excel also allows you to sort based on cell formatting, which we'll look at in a future lesson. Leave the Order at the default value of A to Z.

Now add a new level for City. The only thing we need to change is the column. When you press OK, the table is sorted first by State, then by City.

Now let's sort the table by State, then by Orders in descending value. Again, we choose custom sort. Excel remembers the last sort, so we just need to change the 2nd level from City to Orders, and change the sort order to Largest to Smallest.

Now the table is sorted first by State, and second by Orders.

Note that you can also perform a custom sort from the Home tab of the ribbon, in the Sort & Filter menu.