Excel: Sort a Report into a Custom Sequence

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: My manager wants me to sort a report geographically. My annual report typically lists results from the United States first, then Europe, and then Australia. I need to sort so that the countries appear as United States, England, France, Germany, and Australia.

  1. Sort using a custom list.

    Strategy: You can use a custom list by following these steps:

    1. Go to a blank section of the worksheet. Type the countries in the order you want them to appear in a column. Select the range of cells.
    2. Choose File, Options, Advanced. Scroll to near the bottom of the dialog. The Edit Custom Lists button is now found at the bottom of the General category. In Excel 2007, this button was at the top of the first screen of the Options dialog. Click Edit Custom Lists.
    3. Provided you selected the data in step 1, the reference box next to the Import button already contains the cells that contain your list. Click Import.

  2. Type the countries in their desired geographic sequence.

  3. Adding a new custom list.
    1. Click OK twice.
    2. Select Data, Sort. In the Sort dialog, choose Country from the Sort By dropdown. In the Order dropdown, choose Custom List.
    3. Excel will again display the Custom Lists dialog. Select the USA, England, France list and click OK.
    4. When Excel shows USA, England, France, Germany in the Order dropdown, click OK to sort.

  4. Sort by the custom list.

    Results: The data is sorted by the country order.

    Additional Details: If there is a value in the column that is not in your custom list, it is sorted alphabetically after the entries in the list. If you sort in descending order, these unlisted entries will come first, in Z"“A order.

    Gotcha: Excel remembers that the column was most recently sorted by the "œUSA, England"¦" custom list. If you click the AZ button, it will automatically sort by using this same custom list. If you need to return to alphabetical order, you will have to select Data, Sort and choose A to Z in the Order dropdown.