How to create a custom list for sorting and filtering in Excel

Do you have a list of data you use frequently in Excel? Sick of repeatedly typing them in, or having to copy and paste from another workbook? What if I told you that you could save these entries in Excel. The Custom List feature in Excel allows you to insert the list of entries into a workbook anytime you need them. The process of inserting a custom list is simplified by the fact that the AutoFill tool is the method used, which many users may already be familiar with.

If you aren’t familiar with the AutoFill tool, I recommend you check out my post on how to use the AutoFill tool in Excel.

If you are familiar with AutoFill, then you may know that AutoFill can fill in patterns, such as the days of the week and months of the year. Type any day or month into a cell and use the AutoFill tool to have Excel fill in the rest of the pattern. This is a huge time saver for many users.

As an extension to filling in the days and months, you can set up your own custom lists. Your custom lists are then available to be inserted into a worksheet using AutoFill at any time. This feature is wonderful for lists such as staff names, departments, product names, inventory lists and much more.

Note: You cannot edit the built-in custom lists in Excel. Excel already has the days of the week and months of the year set up. These cannot be edited or deleted.

Create a custom list in Excel

Let’s take a look at how to create a custom list and use AutoFill to enter that lists into a worksheet.

1. Open Microsoft Excel.
2. Type in the list you wish to save or locate it within an existing workbook.
3. Each entry should be in its own cell.
4. One advantage of typing the list into the worksheet first is that you can use the sorting features to get your list in alphabetical order if that is the preferred format.
5. Highlight the list within your worksheet:

6. Click the File tab and select Options from the bottom of the list.
7. From the Excel Options dialog box select Advanced from the categories on the left:

8. Scroll to the bottom of the Advanced screen, click the Edit Custom Lists button in the middle of the screen:

9. The Custom Lists dialog box will appear.
10. You will see there are already entries for the days of the week and months of the year, including the abbreviated versions.

11. Within the Custom lists section, NEW LIST should already be selected.
12. If you don’t type your list into the worksheet beforehand, you could manually enter each entry into the List entries box on the right, you would, however, have to manually put them into the order you want them displayed.
13. Because we already have our list in the worksheet, which we highlighted before we opened this window, we can directly import the information straight into the Custom lists window.
14. At the bottom of the dialog box you will see the Import list from cells section which already has the cell references for the list you created and highlighted, click the Import button to import them:

15. Click OK on the Custom Lists dialog box.
16. Click OK again on the Excel Options dialog box.

Insert a custom list in Excel

Now that the custom list has been created and saved into Excel, how do we insert the list when we need it? The AutoFill tool is going to help us insert the custom list into any Excel worksheet or workbook we need it.

1. Create a new workbook (use the shortcut Ctrl + N on the keyboard).
2. Click in ANY cell within the worksheet.
3. Type in one of the entries you included in your custom list, it doesn’t have to be the first item in the list.
4. Place your mouse cursor over the bottom right corner of the cell so that the plain cross cursor appears.
5. Click and drag the AutoFill tool either vertically or horizontally and Excel will automatically fill in your custom list.
6. You will see a prompt on the screen where Excel shows you how far through the custom list you are:

7. In my example, I have started the pattern a few entries in, not at the start. If I want to insert the full list, I can use AutoFill to backwards fill the list to include the entries I initially missed:

8. Remember a custom list can be filled either vertical, as I have done in my example, or horizontally. The list will be available in ANY Excel workbook, new or old, as the Custom List feature is part of the Excel program as a whole and not specific to just one workbook.

Edit a custom list

If you need to edit any entries, follow these steps:

1. Click the File tab and select Options from the menu.
2. From the Excel Options dialog box select Advanced from the categories on the left.
3. Scroll to the bottom of the Advanced screen and click the Edit Custom Lists button in the middle of the screen.
4. Select your custom list from the ones displayed.
5. Your list entries will appear on the right side of the screen.
6. You can edit an existing entry such as fixing a typo or to delete an entry. To delete an entry, highlight it and press Delete on the keyboard – not the button to the right). To add a new entry, create a new line in the position you want, then type it in manually:

7. Once you are happy with the changes, click OK.
8. Click OK again to return to the workbook.
9. Test your list by typing in one entry and using AutoFill to complete the rest, make sure your change is visible.

Delete a custom list

If you no longer need a custom list, you can easily delete it from Excel.

1. Click the File tab and select Options from the menu.
2. From the Excel Options dialog box select Advanced from the categories on the left.
3. Scroll to the bottom of the Advanced screen and click the Edit Custom Lists button.
4. Select your custom list from the ones displayed.
5. Click the Delete button to the right side of the list entries, this will delete the entire list.
6. Now click OK twice to return to the workbook.

Custom lists are a great time-saving feature of Excel. They provide a easy way to reuse information which you deal with on a regular basis. Comment below if you’ve found a unique way of using custom lists in your workbooks.