How To Create A Custom List In Excel

Thu 26th May 2011

This article describes how to create and use a custom list, and also shows you how to sort data by your custom list.

Have you ever typed the day of the week into an empty cell and then used the fill handle to fill the cells to show the other days of the week? This is an example of using a custom list in Excel.

You can fill days of the week or months of the year, using either the complete word such as Wednesday or February, or the short version such as Wed or Feb. Excel has several built in custom lists to help you easily create table headings or lists, and you can also create your own list. This article describes how to create and use a custom list, and also shows you how to sort data by your custom list.

What is a Custom List

Try typing in the word Monday in an empty Excel cell. Now hoover your mouse over the fill point (the small black cross on the lower right of the cell) so that the cursor becomes a small black cross. Then drag down and the cells are filled with the days of the week. The further you drag the more cells are filled, and the lists repeats if you drag more cells than there are words in the list.

Excel already has several in built custom lists, such as days of the week and months of the year, and you can see these by viewing the Custom Lists panel which we'll describe shortly. In addition to using an inbuilt custom list, you can also create your own. This might be a list of words you frequently use, such as a list of department or list of staff names. So next we'll describe how to create your own custom list.

How to Create a Custom List

Choose an empty part of an Excel worksheet and type your own list of words, for example North, South East and West in separate cells, either down a column or across a row. Then highlight all these cells. Next we need to import these cells into Excel as a custom list using the Custom Lists panel. To do this in Excel 2003 choose Tools, Options and select the Customs List tab. In Excel 2007 choose Office Button, Excel Options, Popular and click Edit Custom Lists. In Excel 2010 choose File tab, Options, Advanced, scroll down to General section and click Edit Custom Lists.

In the Custom List panel you'll see on the left hand side the existing custom lists such as days of the week and months of the year. In the lower part of the panel you'll see your highlighted cells showing just left of the Import button. Click the button and your cells are added to the custom lists in the left hand side of the panel. Click OK to finish. Once imported, your custom list is added to Excel permanently will be available for all Excel files.

How to use a Custom List

You might like to close the current Excel workbook, and there's no need to save it. Then open a new blank workbook and select any empty cell. Then type any one of the words in your custom list. Now use the fill handle and fill this cell down a column or across a row, and you'll see Excel adds all the words of your custom list in the original list order to the other cells. If you have any of the original words in capitals, such as the first letter, then the filled list will include the correct capital letter for that word. If you type in one of your list words all in capitals, even if the original was not in capitals, the filled list will be in capitals.

How to sort data by your own Custom List

Suppose you have a list of records in a table in Excel, and each record shows the area as either North, South, East or West. You decide to sort this list by area. However you then discover that Excel sorts the areas alphabetically, so the areas are sorted in the order East, North, South and West, which doesn't look too sensible. However you can sort your area data by your custom list order North, South, East, West. To do this ensure the table is highlighted. Then choose the sort option.

In Excel 2003 choose Data, Sort. In the sort panel ensure the Sort By shows Area. Then at the bottom of the panel click Options. In the Sort Options panel click the pop down just under First Key Sort Order and choose your custom list, then OK, and OK again to sort. In Excel 2007/2010 choose the Data tab and click the Sort button. In the Sort panel ensure the Sort By shows Area. Then click the pop down right of Order and choose Custom List, then OK, and OK again to sort. And voila, your list of records are sorted by area, in the correct order North, South, East and West.

Want to find out more about custom lists and many other interesting Excel features. Why not consider attending a training course. This can be a really effective way to increase your skills in using Excel.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1710-create-custom-list-in-excel.html