MrExcel.com - Your One Stop for Excel Tips & Solutions

Sort a Report into a Custom Sequence

September 01, 2022 - by

Sort a Report into a Custom Sequence

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.

Four columns of data: Country, Region, District, and Sales Rep. Everything in unsorted in this figure.
Figure 655. 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.

In five blank cells, type the custom list: USA England France Gemnay Australia.
Figure 656. Type the countries in their desired geographic sequence.
Select, File, Options, Advanced, Edit Custom Lists. To the left of the Import button, choose the location of your list. Then, click Import.
Figure 657. Adding a new custom list.

  • 4. Click OK twice.

  • 5. Select Data, Sort. In the Sort dialog, choose Country from the Sort By dropdown. In the Order dropdown, choose Custom List.

  • 6. Excel will again display the Custom Lists dialog. Select the USA, England, France list and click OK.

  • 7. When Excel shows USA, England, France, Germany in the Order dropdown, click OK to sort.

The Sort dialog box shows Country being sorted by Values USA, England, France, Germanuy.
Figure 658. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by GeoJango Maps on Unsplash