Excel built in Lists

If you are already using some of Excel’s custom features then you probably are already in love with them. Few days back we discussed custom views option and how it can help us. Today we have another custom feature of Excel – Custom Lists. Custom lists helps us notably in two ways:

• by having our own autofill or auto fill series. (remember how quickly you can get a list of months just by dragging? That is basically built-in auto fill series in action)
• help you sort your way i.e. beyond ascending or descending and few other options.

First option deals mainly with automating the work and the second relates to data analysis and gives you control over sorting feature of excel and let you arrange the data you like for better analysis. But the question you must be asking how to get custom lists? Read on! 🙂

Excel built in Lists

When you type a month name in a cell and drag fill handler then other months are filled in the cell automatically. Same is the case with the dates of the week. This is Fill or Auto fill utility of excel you use when you drag the cell with fill handle. This happens because Excel has stored these month’s names and week’s days as a list. How convenient would it be if we can have our data stored as a list in Excel and populate worksheet just by dragging. Well this is exactly what you can achieve with Custom list functionality of excel.

The default lists embedded in Excel can be accessed but cannot be removed or altered. We will learn about accessing built-in lists in upcoming discussion.

Creating and populating custom lists in Excel

To create custom lists you have two ways:

• create custom list on the fly using right click or excel ribbon option
• create custom list using Excel’s options

Populating the custom list

To populate custom list i.e. putting data in the custom lists can be done in two ways:

• by manually typing the data
• by importing a range of cell – this option is only available if custom lists are created using Excel’s options

Creating custom lists using Excel Options

Preliminary considerations

Although you can manually keypunch the elements of custom list you are about to create but if your list is big then it would be better that you have the list prepared in advance. Another important thing to remember is the order of elements of entries of the list. If you are preparing the list in advance then you should prearrange the data in the order you desire. And the same order will be used to fill and sort the data later.

Step 1: Click File button. Excel’s backstage view is now active. On the left just above the Exit button click the options button.

Step 2: Excel options dialogue box will appear. Click the Advanced tab at the left

Step 3: Scroll down to General section and click Edit custom lists button. Custom lists dialogue box will appear

Step 4: You can populate custom either manually entering the list or use import function. Import function is much better and a quicker method and to very extent error free. To use import utility then carry on with step 5 otherwise you are already done if you are using manual method to create custom lists. By manual method it means that you are typing all the entries in the list entries box in Custom lists dialogue box.

Step 5: Click collapse dialogue button and this revert you back to worksheet where you can select the range of cells. Once done click the expand dialogue button to revert back to custom lists dialogue box.

Step 6: Click Import button and this will add the items as custom list as well. You don’t have to click Add button again to add it.

Step 7 (if needed): However, once imported if you feel you need to make some changes you can select the list you just imported. Make the correction and click Add button and the list will be updated.

Creating custom lists on the fly

This is considerably easier method as you don’t have to delve in Excel’s options to create custom lists.

Step 1: With data selected:

right click > Sort > Custom sort;

OR

Click Data tab > Sort button > Click order drop down > Custom list…

Step 2: A new dialogue box named “Custom Lists” will open

Step 3: Under List entries section you can manually type in each element of the list. To separate one entry from the other press Enter key

Step 4: Press Add button on the right of the box to add the list to custom lists collection.

Step 5: Press OK

Doing custom lists this way you have not only created a custom list but also sorted the data according to that list. And now you can use this custom lists to populate cells using auto fill function by dragging the cell content. Just type any entry of the list you created and drag and series will start filling from that entry onward.

One draw back in creating custom list using this method is that if your series or list have large quantities then you have to type in all the list yourself and you may end up making mistakes or typos. The better way is to import the list from range of cells. But import option is only available if you are creating custom lists using Excel options method discussed above.

Custom auto fill series with custom lists

Once you have custom list you can use auto fill functionality to fill the custom list as series by dragging up, down, left or right. It definitely increases the efficiency many times over as you are now relieved of typing the whole thing yourself.

Custom Sort using custom lists

Excel provides very little options to sort the data. For example smallest to largest or largest to smallest in case data is numerical or A to Z or Z to A if data is text based. In other words Excel’s built-in sort may not work for you always. But with custom lists made, you can sort the data beyond alphabetical order.

To do a custom sort using custom list work through following steps:

Step 1: Select the data you want to sort or have an active cell within the range that you needs to sort

Step 2: Either right click > sort > custom sort OR go to Data tab> Sort and filter Group > Sort button

Step 3: From the dialogue box that appear, from order coloumn dialogue box select custom lists.

Step 4: Custom lists dialogue box will appear from where you can select the list you have already created. Select the appropriate list

Step 5: Click OK button and PUM! your data is sorted according to the order of items as mentioned in custom list you created.

Custom Lists in Excel – Bonus Tip

If you have more cities for example Lahore, Islamabad, Karachi, Quetta, Peshawar and other cities and you want Lahore, Karachi and Islamabad cities to appear in the order and the rest of the cities after these three then have a custom list made up of these three city’s names and sort the data.

Having the data sorted this way the three cities specified in custom list will be displayed and the rest of the cities appear after these cities in alphabetical order. Following animation help you understand it better:

Liked it? Pin it!