Excel 2013

Custom sorting

Sometimes you may find that the default sorting options can't sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order.

To create a custom sort:

In our example below, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we'll create a custom list to sort from smallest to largest.

1. Select a cell in the column you wish to sort by. In our example, we'll select cell D2.
   
   Selecting cell D2
2. Select the Data tab, then click the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Select the column you want to sort by, then choose Custom List... from the Order field. In our example, we will choose to sort by T-Shirt Size.
   
   Selecting Custom List... from the Order: field
4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists: box.
5. Type the items in the desired custom order in the List entries: box. In our example, we want to sort our data by T-shirt size from smallest to largest, so we'll type Small, Medium, Large, and X-Large, pressing Enter on the keyboard after each item.
   Creating a Custom list
6. Click Add to save the new sort order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK.
   
   Clicking OK to select the custom list
7. The Custom Lists dialog box will close. Click OK in the Sort dialog box to perform the custom sort.
   Clicking OK to sort the worksheet
8. The worksheet will be sorted by the custom order. In our example, the worksheet is now organized by T-shirt size from smallest to largest.
   
   The worksheet sorted by t-shirt size

To sort by cell formatting:

You can also choose to sort your worksheet by formatting rather than cell content. This can be especially helpful if you add color coding to certain cells. In our example below, we'll sort by cell color to quickly see which T-shirt orders have outstanding payments.

1. Select a cell in the column you wish to sort by. In our example, we'll select cell E2.
   Selecting cell E2
2. Select the Data tab, then click the Sort command.
   Clicking the Sort command
3. The Sort dialog box will appear. Select the column you wish to sort by, then decide whether you'll sort by Cell Color, Font Color, or Cell Icon from the Sort On field. In our example, we'll sort by Payment Method (column E) and Cell Color.
   Choosing to sort by cell color
4. Choose a color to sort by from the Order field. In our example, we'll choose light red.
   
   Choosing a cell color to sort by
5. Click OK. In our example, the worksheet is now sorted by cell color, with the light red cells on top. This allows us to see which orders still have outstanding payments.
   
   The worksheet sorted by cell color