One such useful (and less-known) feature in the Custom Lists in Excel. Now, before I get to how to create and use custom lists, let me first explain what’s so great about it. Suppose you have to enter numbers the month names from Jan to Dec in a column. How would you do it? And no, doing it manually is not an option. One of the fastest ways would be to have January in a cell, February in an adjacent cell and then use the fill handle to drag and let Excel automatically fill in the rest. Excel is smart enough to realize that you want to fill the next month in each cell in which you drag the fill handle. Month names are quite generic and therefore it’s available by default in Excel. But what if you have a list of department names (or employee names or product names), and you want to do the same. Instead of manually entering these or copy-paste these, you want these to appear magically when you use the fill handle (just like month names). You can do that too… … by using Custom Lists in Excel In this tutorial, I will show you how to create your own custom lists in Excel and how to use these to save time.
How to Create Custom Lists in Excel
By default, Excel already has some pre-fed custom lists that you can use to save time. For example, if you enter ‘Mon’ in one cell ‘Tue’ in an adjacent cell, you can use the fill handle to fill the rest of the days. In case you extend the selection, keep on dragging and it will repeat and give you the day’s name again.
Below are the custom lists that are already in-built in Excel. As you can see, these are mostly days and month names as these are fixed and will not change.
Now, suppose you want to create a list of departments that you often need in Excel, you can create a custom list for it. This way, the next time you need to get all the departments name in one place, you don’t need to rummage through old files. All you need to do is type the first two in the list and drag.
Below are the steps to create your own Custom List in Excel: As soon as you click on Add, you would notice that your list now becomes a part of the Custom Lists.
In case you have a large list that you want to add to Excel, you can also use the Import option in the dialog box. Now that you have the list in Excel backend, you can use it just like you use numbers or month names with Autofill (as shown below).
While it’s great to be able to quickly get these custom lits names in Excel by doing a simple drag and drop, there is something even more awesome that you can do with custom lists (that’s what the next section is about).
Create Your Own Sorting Criteria Using Custom Lists
One great thing about custom lists is that you can use it to create your own sorting criteria. For example, suppose you have a dataset as shown below and you want to sort this based on High, Medium, and Low.
You can’t do this! If you sort alphabetically, it would screw the alphabetical order (it will give you High, Low, and Medium and not High, Medium, and Low). This is where Custom Lists really shine. You can create your own list of items and then use these to sort the data. This way, you will get all the High values together at the top followed by the medium and low values. The first step is to create a custom list (High, Medium, Low) using the steps shown in the previous section (‘How to Create Custom Lists in Excel‘).
Once you have the custom list created, you can use the below steps to sort based on it: The above steps would instantly sort the data using the list you created and used as criteria while sorting (High, Medium, Low in this example).
Some Examples Where you can Use Custom Lists
Below are some of the cases where creating and using custom lists can save you time: So this is all that you need to know about Creating Custom Lists in Excel. I hope you found this useful. You may also like the following Excel tutorials:
How to Sort by the Last Name in Excel How to SORT in Excel (by Rows, Columns, Colors, Dates, & Numbers) How to Sort By Color in Excel How to Sort Worksheets in Excel Automatically Sort Data in Alphabetical Order using Formula
