Using a custom sort order

Excel 2016 2013 2010 2007 2003
Excel proposes different ways to sort data. You may sort your data in alphabetical order, according to the background or text color of the data, etc.
In some cases, you may want to sort your data in non-standard ways. For example, if your data consists of month names, you usually want it to be ordered by month numbers rather than alphabetically.

If you want to sort your data, follow the next steps:

   1.   Open the Sort dialog box, do one of the following:

  • On the Home tab, in the Editing group, click the Sort & Filter list and choose Custom Sort...:
    Custom Sort in Excel 365 menu
  • Right-click any cell in the table and choose Sort -> Custom Sort... in the shortcut menu:
    Custom Sort in Excel 365 popup

   2.   In the Sort dialog box, use the drop-down lists to specify key columns, values, and sorting order:

  • To select data in the table or any data range with headers, check the My data has headers checkbox:
    My data has headers in Sort dialog box Excel 365
  • In the Sort by dropdown list, select a column or table header for sorting,
  • In the Sort On dropdown list, select one of the options:
    Sort On list in Sort dialog box Excel 365
    • Cell Values allows you to sort data by cell values
    • Cell Color allows you to sort data by cell background color
    • Font Color allows you to sort data by text color of the cells
    • Conditional Formatting Icon – see more about Conditional formatting
  • In the Order column, select the sorting order:
    • A to Z - in ascending alphabetical order or Z to A - in descending alphabetical order for text values,
    • Smallest to Largest or Largest to Smallest for the numerical values,
    • Oldest to Newest or Newest to Oldest for date or time values,
    • Custom List...:
    Sort dialog box in Excel 365
  • If you prefer to sort data by several columns:
    • Click the Add Level button to insert another line of sorting criteria
    • Copy the line of criteria by clicking the Copy level button
    • Delete the line of criteria by clicking the Delete level button
    • Change the order of the sorting criteria by clicking the Move Up and Move Down buttons:
    Change the order in Sort dialog box Excel 365
  • Click the Options... button to open the Sort Options dialog box:
    Sort Options in Excel 365 popup
    • Select the Case sensitive checkbox to sort analyzing the text case of cell values.

      For example, the custom sort order (see below):

      Case sensitive checkbox for sorting in Excel 365 popup
    • Select the Sort left to right option to change the sorting method from columns to rows:
      Sort left to right in Excel 365 popup

   3.   In the Custom Lists dialog box, choose the custom sort list or create a new list:

  • Excel, by default, has four "custom lists", and you can define your own. Excel's custom lists are as follows:
    Custom Sort Lists in Excel 365
    • 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).

    To create a custom list, select the NEW LIST option and make your entries (in order) in the List Entries box.

    For example, sort the Microsoft products in the prioritized order:

    Custom Sort List in Excel 365

See also this tip in French: Comment utiliser un ordre de tri personnalisé.

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.