How to store a custom list in Excel

Do you have a list of data you use frequently in Excel? Sick of having to repeatedly type them in or copy and paste from another workbook? What if I told you that you could save these entries as a custom list in Excel and insert them into a workbook anytime you need them? What if you could use the AutoFill tool whenever you wanted to add them to your worksheet?

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 replicate 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 produce the remainder of the pattern. A huge time saver for many.

As an extension to creating the days and months, you can also set up your own custom lists. The list is then available to be added to a worksheet using AutoFill at any time. This works wonderfully for lists such as staff names, departments, product lists, inventory lists and much more.

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 and select Options from the menu.
7. From the Excel Options dialog box select Advanced from the categories on the left:

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

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. Under the Custom lists, NEW LIST should already be selected.
12. If you didn’t type your list into the worksheet 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.
17. Create a new workbook (use the shortcut Ctrl + N on the keyboard).
18. Type in ANY one of the entries you included in your list, it doesn’t have to be the first item in the list.
19. Place your mouse cursor over the bottom right corner of the cell so that the plain cross cursor appears.
20. Click and drag the AutoFill tool either vertically or horizontally and Excel will automatically fill in your custom list.
21. You will see a prompt on the screen as you fill the list further where Excel shows you how far through the list you are:

22. In my example, I have started the pattern a few entries in, not at the start, if I need to have the full list, I can use AutoFill to backwards fill the list back up to include the entries I initially missed:

23. Remember a custom list can be auto-filled either vertical, as I have done in my example, or vertically. 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 within a custom list, 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 great way to be able 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.