Do you have a list of entries you use frequently in Excel? Sick of having to 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? What if you could use the AutoFill tool whenever you wanted to add them to your worksheet?

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 completed using AutoFill in any workbook/worksheet. This works wonderfully for lists such as staff names, departments, product lists, inventory lists, and much more.

Let’s get started

Let’s take a look at how to set up a custom list and use AutoFill to enter those lists into a worksheet.

  1. Open Microsoft Excel
  2. Type in the list you wish to save or locate 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 you use in
  5. Highlight the list within your worksheet

  1. Now click the File tab to go into Backstage view
  2. Select Options from the menu
  3. From the Excel Options dialog box select Advanced from the categories on the left

  1. 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:

  1. Now the Custom Lists dialog box will appear
  2. You will see there are already entries for the days of the week and months of the year, including the abbreviated versions.
  3. Under the Custom lists, NEW LIST should be selected
  4. 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
  5. 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
  6. At the bottom of the dialog box you will see Import list from cells which already has the cell references for the list you created and highlighted, click the Import button to import them

  1. Click OK
  2. Click OK again on the Excel Options dialog box
  3. You can now open a new workbook (use the shortcut Ctrl + N on the keyboard)
  4. Type in ANY one of the entries you included in your list, it doesn’t have to be the first item in the list
  5. Place your mouse cursor over the bottom right corner of the cell so that the plain cross cursor appears
  6. Click and drag the AutoFill tool either vertically or horizontally and Excel will automatically fill in your custom list
  7. 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

  1. 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 backward fill the list back up to include the entries I initially missed

  1. 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 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 deleting 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

  1. Once you are happy with the changes, click OK 
  2. Click OK again to return to the workbook
  3. 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. 


Reference: https://www.thetraininglady.com/custom-list-excel/