TIPS for CLICKS: Custom Lists

Using Excel Custom Lists

What are Custom Lists used for? They are used mainly for SORTING or FILLING any regular Excel lists and tables in a specific user-defined order. Using the lists may save you a lot of time when frequent sorting or filling your lists is needed when working with Excel data.

Excel provides couple of built-in lists by default, as you can see below: 

 

How you can use them? Here is the way (if you haven't used them never before):

• in your selected cell type in any entry from the custom list, e.g. Jan, or Mon, etc.
  
• click on the small box at the bottom right corner and drag it any way you want to - down, up, right, left - to populate any number of cells, in rows or columns, with the list items.

There are also many "hidden" custom-like lists you can built and use easily as needed. Below are some examples of such lists that Excel accepts and readily auto fills for you:

 

In most cases you can use them by typing in just the first item, like e.g. "Lesson 1", "abc_a1", "Qtr 1", and dragging the cell's corner in any direction. But you can also use lists like the List5 or List6, typing in first two or three cells to initiate your custom list and then selecting them and dragging the lowest cell's corner in any direction.

 

Excel doesn't care about the case of items in the list, so if you type the first item e.g. in Upper case, all the following items will be displayed in the Upper case.

Keep in mind that if you'd like to built a list of numbers only, like e.g. List9 above, the list must be formatted as text prior to including it as a Custom List.

Creating your own Custom Lists

To create and add your own Custom lists to those provided already in Excel you need to Edit Custom Lists. To do that follow these steps:

• type the values of the list you need to use quite frequently, like e.g. currency codes of some countries, in a column of your worksheet, as shown below
  
• select the cells with your entries
• go to File>Options>Advanced>General, find there the shaded Edit Custom Lists field and click on it
  
• in the box click Import, click OK; your list has been added to the Custom lists box and is ready to use.
  

 

Alternatively, you could also add your list by clicking NEW LIST in the Custom lists box,  entering your list in the List entries box, one by one (pressing Enter key after each entry), and clicking Add when completed all entries.

Sorting by your Custom Lists

Now, you can use any of available custom lists for sorting your datasets. Click on one or more columns of your table, then click on Sort & Filter > Custom sort... > drop down the Order menu > Custom List... and find the list you'd like to sort by. Finish by clicking OK. Include as many columns as you need for sorting.