Sorting by Custom Lists

 

In this article, we will learn how to sort data by custom lists in Microsoft Excel.

Custom list: – Excel does not have ready made sorting option as per our requirement. So through custom list, we can create sorting option as per our requirement.

Let’s take an example and understand how we can make the custom sort list and how we can use it.

We have data in range A1:B24 in which column A contains Agents’ name and column B contains Revenue. In this data set, we want to put custom filter for the Agent name and then sort it.

First we will remove duplicates from the agent list:-

Follow below given steps:-

  • Copy the range A2:A24 and paste it to a new column in sheet.

image 1
 

  • Select the range.
  • In the Data tools tab > Remove Duplicates.
  • Only unique list we will have.

image 2

 

Or we can use Advance filter option:-

  • Go to Data menu, and select Advanced Filter from the sort & filter‘s group.
  • Advanced Filter dialog box will appear.
  • Select Copy to another location.
  • In “Copy to” tab, select the cell where you want to copy the unique record.
  • Select unique records only, and then click on OK.

image 3

 

  • Unique records will get copied in the range D2:D18.

image 4

 

How to storing custom list?

Follow below given steps:-

  • Select the range D2:D18.
  • Go to File tab.

image 5

 

image 6
 

  • Click on options, and ‘Excel Options’ dialog box will appear.

 
image 7
 

  • Click on Advanced witha right click on Edit Custom Lists.

 
image 8
 

  • ‘Custom Lists’ dialog box will appear.

 
image 9
 

  • Click on Import, and all the entries will display in List Entries.

 
image 10
 

  • Click on Add button, and then click on OK.

 
How to implement or use custom list in the sheet?

  • Select one of the cells in the list.
  • In the Data tab, select Sort, and ‘Sort Options’ dialog box will appear.

image 11
 

  • Sort by Agent’s name in Column.

image 12
 

  • In order, sort by Custom List.

image 13
 

  • ‘Custom List’ dialog box will appear. Now, click the list of agent’s name, and then click on OK.

image 14
 

  • Data will get sorted as per the custom list.

image 15
 
This is the way we can sort the data through custom list in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

 



5 thoughts on “Sorting by Custom Lists

  1. I want to build a custom list in Excel using years (ex: 2004,2005,2006,2007)etc. for admissions data. After building it I use the autofill feature and only 2004 comes across. I try to import it from the custom list window but nothing happens. Any thoughts?????

  2. “Hi Tricia,
    Does it work if you try just numbers (starting at 1)? In other words, can you make any auto fill list work? To be honest, there is no need to build a custom list starting at 2004. Excel will always auto fill numbers, starting from anypoint if you increment by 1 (never tried more complex patterns). If you can make it work for 1, 2, 3, 4, … then try replicating your actions precisely bu substituting 2004 for the 1, 2005 for the 2 and going from there. You might even want to delete your custom list just in case it is entered incorrectly, since it is not really necessary.
    HTH,
    Alan.”

  3. “Hi,
    I am trying to sort demographics category in a pivot table using the custom lists, but nothing happens.
    The order I want is: All, Gender, Age, Occupation, etc…
    But it keeps sorting it alphabetically. I did it before, but can’t remember how!
    Thanks in advance for your help.
    Yasmeen.”

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>