Are you spending too much time entering data in Excel? Discover the power of Excel custom lists. This feature lets you autofill and sort a series with your own custom entries. In this tutorial, I’ll show you how to create a custom Excel list that provides functionality similar to the built-in lists. And you can use these in any workbook. Start using these lists today and experience a productivity boost.
What Are Custom Lists
Custom lists are a type of autofill list that can be used either vertically or horizontally, so you don’t have to worry about switching columns and rows. When you drag the fill handle, you see your pre-defined entries appear. Think of them as your version of Excel’s built-in lists for months, days, etc. The difference is you define the values that pertain to your work or reports. Maybe it’s employee names, product SKUs, or sports teams. The customization is up to you.
In addition to not having to type the list each time you need it, the lists can be used across workbooks. And you can even do custom sorts. This is helpful to people who do similar reports on a frequent basis.
Custom Lists vs. Dependent Lists
One question you may want to answer is which type of list you need. When you think about Excel’s built-in lists, there are some common denominators. They tend to be used frequently and are known entities. And you can use them in any workbook.
In contrast, Excel Dependent Lists are conditional. The list of items you see is dependent on another cell reference value. For example, if you do department reporting, you might want to create a custom list that includes all of them. However, if you needed to do a regional sales commission report, you might make a dependent list that filters to show the sales agents for a specific sales region.
Consider Your List Contents
Another consideration for these lists is the data format. These lists do not work for a list of just numbers, such as zip codes which can drop leading zeros if you’re not careful. To get around this issue, you need to format the list values as text. However, you can have entries that have both numbers and letters, such as parts numbers.
How You Enter Items Impacts Sort Order
One item to remember is that custom lists can be used as a sort criterion. This means you want to think about their order. For example, if I’m a coin collector, I may designate Penny, Nickel, Dime, and Quarter denominations. When I sort inventory, I probably want that same order instead of an alphabetical sort of Dime, Nickel, Penny, and Quarter.
How to Create a Custom List in Excel (Direct)
There are three methods for creating these lists. The first option is when you have a few values, such as Small, Medium, and Large. In this case, you can add the entries directly.
- Click File.
- Click Options. (Microsoft 365 users need to click More..)
- From the Excel Options dialog, click Advanced.
- Scroll down to the General section.
- Click the Edit Custom Lists… button.
- On the Custom Lists tab, click NEW LIST.
- Click into the List entries: box.
- Add your list values. You can either separate the values by a comma or Enter button.
- Click the Add button.
- Your entries will now show on the left-hand side.
- Click OK twice.
How to Create a Custom List (Highlight Range)
If I have a longer list, I prefer to type them into a cell range first, such as Column A. Alternatively, you could use a row. You want adjacent cells.
- Type in your values.
- Highlight the list of items. These entries will become the basis for your list.
- Click the File menu at the top.
- From the left pane, select Options. This will be toward the bottom.
- Under Excel Options, click Advanced.
- Scroll down to the General section.
- Look for the Edit Custom Lists… button.
- The Customs Lists dialog box will open.
- You should see your highlighted data range by the Import button.
- Click the Import button. Your highlighted values will show under the List Entries section.
- Click OK.
- This will open the Excel Options dialog again.
- Click OK again on the Excel Options panel. Your list has been added.
Create Custom Lists from a Named Range
Another option is to use a Named Range as your data source. This might be handy if you have a list that might change and you’d prefer to edit the named range.
For example, let’s say you’re a college football fan, and you’ve created named ranges for the conferences. The process is similar to the above. Instead of typing your entries, you type in the named range value, SEC, in the Import list from cells: text box and then click the Import button.
And then, when the SEC adds more teams, you can add the new teams to your named range. However, adding new items to the named range won’t impact the existing custom list. You’ll need to reimport the SEC-named range. It will not overwrite your previous one.
How to Use Your Custom List
- Type your first value, such as Boston, into a cell.
- Click the cell so the border shows.
- Move your mouse over the lower right corner till the + appears.
- Left-click and drag over the cells you want to be filled. You can go down or across. As you drag the fill handle, you should see labels showing the list of items.
How to Edit List Values
Sometimes, you may need to remove or add a list item. For example, if you have an employee list, you may need to make changes as people move. One exception is you can’t edit the built-in default Excel lists. The process is very similar to manually adding entries.
- Navigate to the Custom Lists dialog box.
- Click the list you wish to edit. Its entries should show on the right side.
- Click into the List entries: section.
- Make your change, such as adding, deleting, or correcting spelling.
- Click OK twice.
If you wish to delete the custom list, then click the Delete button on the right. Excel will prompt you with a dialog message box stating the list will be permanently deleted.
Once you get the hang of creating Excel custom lists, you’ll start thinking of additional areas where you can use them. Perhaps, you’re the commission of a fantasy sports league and need to enter transactions around the same players each week. Or, you’re getting a jump on your holiday gift list and need to define friends and family. Excel custom lists can make data entry faster and more consistent regardless of your intent.