Using a custom sort order

Excel 365 2016 2013 2010 2007
In some cases you may want to sort your data in non-standart ways. For example, if your data consists of month names, you usually want it to appear in month order rather than alphabetically.

If you want to sort your data, choose Data -> Sort.... Excel displays the Sort dialog box:

Sort in Excel 2003

You can click the Options... bottom to open the Sort Options dialog box:

Sort Options in Excel 2003

Select the appropriate list from the drop-down list labled First Key Sort Order. Excel, by default, has four "custom lists", and you can define your own. Excel's custom lists are as follows:

  • Abbreviated days: Sun, Mon, Tue, Wed, Thu, Fri, Sat
  • Days: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
  • Abbreviated months: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  • Months: January, February, March, April, May, June, July, August, September, October, November, December

Note that the abbreviated days and months do not have periods after them. If you use periods for these abbreviations, they are not recognized (and are not sorted correctly).

You may want to create a custom list. For example, your company may have several stores, and you want the stores to be listed in a particular order (not alphabetically). If you create a custom list, sorting puts the items in the order that you specify in the list. You must use the Data -> Sort command to sort by a custom list, on the Options button to specify the custom list.

To create a custom list, select Tools -> Options... and then choose the Custom Lists tab:

Options in Excel 2003

Select the NEW LIST option and make your entries (in order) in the List Entries box. Or you can import your custom list from a range of cells by using the Import button.

Custom lists also work with the autofill handle in cells. If you enter the first item of a custom list and then drag the cell's autofill handle, Excel fills in the remaining list items automatically.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.