Excel has some useful features that allow you to save time and be a lot more productive in your day-to-day work.
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:
- Click the File tab
- Click on Options. This will open the ‘Excel Options‘ dialog box
- Click on the Advanced option in the left-pane
- In the General option, click on the ‘Edit Custom Lists’ button (you may have to scroll down to get to this option)
- In the Custom Lists dialog box, import the list by selecting the range of cells that have the list. Alternatively, you can also enter the name manually in the List Entries box (separated by comma or each name in a new line)
- Click on Add
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:
- Select the entire dataset (including the headers)
- Click the Data tab
- In the Sort and Filter group, click on the Sort icon. This will open the Sort dialog box
- In the Sort dialog box, make the following selections:
- Sort by Column: Priority
- Sort On: Cell Values
- Order: Custom Lists. When the dialog box opens, select the sorting criteria you want to use and then click on OK.
- Click OK
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:
- If you have a list that you need to enter manually (or copy-paste from some other source), you can create a custom list and use that instead. For example, this could be department names in your organization, or product names or regions/countries.
- If you’re a teacher, you can create a list of your student names. That way, when you are grading them the next time, you don’t need to worry about entering the student names manually or copy-pasting it from some other sheet. This also ensures that there are fewer chances of errors.
- When you need to sort data based on criteria that are not in-built in Excel. As covered in the previous section, you can use your own sorting criteria by making a custom list in Excel.
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:
1 thought on “How to Create Custom List in Excel (Easy Step-by-Step Guide)”
Regarding the ‘Pro tip’, I’m not sure this bit works: ‘The benefit of this is that you can change or expand the named range and it will automatically get adjusted as the custom list’.