Sort It Your Way With Excel Custom Lists

You know how to sort an Excel list alphabetically, and with Excel 2007 you can even sort an Excel list by colour. Did you know that you can also create a custom list in Excel and use that to sort your data, instead of sorting in alphabetical or numerical order?

Instead of sorting the products in this table alphabetically, we’ll create a custom list of products, and use it when sorting the list.

CustomSort01

Create a Custom List in Excel

You can create a custom list in Excel by importing a list from a worksheet, or by typing a new list. In this example, there is a worksheet named Lists, and it contains a product list. We’ll import that list, to create the custom list.

CustomSort02

To open the custom list window:

  • Select the cells that contain the list items
  • On the Ribbon, click the File Tab (or the Office Button in Excel 2007)
  • Then click Options.
  • Excel 2010 and later: Click the Advanced category, then scroll down to the General section
  • Excel 2007: Click the Popular category, then look in the Top Options section
  • Click Edit Custom Lists

To add a custom list:

  • In the Custom Lists dialog box, the list address — $A$2:$A$5 — should appear in the Import range box. If not, you can click in the Import range box, and type a range, or select a range on the worksheet.
  • CustomSort04
  • To add the selected range as a custom list, click the Import button.
  • The list items will appear in the List entries section of the Custom List dialog box, and at the end of the list of existing Custom Lists.
  • CustomSort05
  • Click OK to close the Custom Lists dialog box, and click OK to close the Excel Options window.

Use the Custom List

You can use the custom lists when sorting, and you can also use them with the AutoFill feature. Type any item from a custom list in a cell, then use the Fill handle to complete the list.

CustomSort07

Sort the Excel List in Custom Order

To sort your list based on your custom list, follow these steps:

  • Select a cell in the table that you want to sort.
  • On the Ribbon’s Data tab, click Sort
  • In the Sort dialog box, select a Column from the first drop down, and select Values from the Sort On drop down.
  • In the Order drop down, click Custom List
  • In the Custom List dialog box, select your custom list, and click OK
  • CustomSort06
  • Click OK to close the Sort dialog box

The list is sorted in the order of the items in your custom list.

Watch the Excel Sort Video

To see the steps for adding an Excel Custom List, then sorting by that Custom List, watch this short Excel video tutorial.

____________

You may also like...

7 Responses

  1. Sudeepa says:

    How do you count the number of a single month in two columns

  2. Sanjay Kumar says:

    Custom sort is not resuable. We cannot save it.
    If I sort it by A to Z or Z to A, the custom sort that I created disappeared.
    What is the use of custom sorting if it cannot be reused?

  3. benton Gaynes says:

    Hi

    I am trying to sort a list of numbers, some of which may end with a letter. I cannot find an excel sort function
    example- I cannot sort this list and get ascending or descending numbers.

    1150a
    1230
    1375
    1363a
    1448c

    Thanks

  4. Randy says:

    I want to sort by starting with 1. However, several of my cells have 0 in them and they always come first when sorting smallest to largest. Any ideas?

  5. TonyL says:

    Thank you Debra: I have been using Excel (and Lotus prior for twenty years) and never knew this. Is there away to apply the same customization to Pivot Table columns? Thank you. TonyL
    I gave an Aging PT where I want the column labled “Current”, to come before <30, 60, etc. Thank you.

  1. June 5, 2013

    […] block at posts #22 #23, how to incorporate a custom list in your sort. See if this article helps. http://blog.contextures.com/archives…-custom-lists/ If this matches your needs, record a macro as you go over the steps. Then, in the generated code, […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *