Create your own custom sort in Excel

Posted on:  03/22/2017
Create your own custom sort in Excel

Excel has built-in custom lists. You can sort data by days of the week or months of the year. Or create your own custom lists to sort by any other characteristic that doesn’t sort well alphabetically, such as July to June; October to September; S, M, L, XL.

With custom lists, you could sort your worksheet in any way possible. By default, the custom list in Excel has four custom list shown below.

  1. The days of the week abbreviated starting with Sunday.
  2. The days of the week spelled out starting with Sun.
  3. The months of the year abbreviated starting with January.
  4. The months of the year spelled out starting with Jan.

Built-in custom list for sorting

Steps to create a custom sort list in Excel:

  1. In a column of a worksheet, type the values you want to sort by in the order you want them, from top to bottom.
  2. Select the cells your typed, and then click File > Options > Advanced.
  3. Click on Advanced on the left side, and then click Edit Custom List.
  4. Note: for Excel 2007 this is under General, click Edit Custom Lists.
  5. In the Custom Lists box, click Import. 
  6. Click OK twice. 

Photo gallery of creating a custom sort list

Custom sort 1

Custom sort 2

Custom sort 3

Custom sort 4

Why create a custom list sort? You may need to sort by months from October to September, or July to June. You may need to sort by products but not alphabetically. You may need to sort Monday through Sunday.

YouTube video on creating a custom list for sorting

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories