Have the Fill Handle Fill Your List of Part Numbers

September 24, 2021 - by Bill Jelen

Problem: Sure, the fill handle is good for filling months, days, and sequential numbers. But what about other lists I have to type lists of product lines, company regions, sales rep names, and so on.

Strategy: No matter what job you do, you probably have some annoying list of items that you have to type over and over. If your list contains from two to 96 items, you can add your list of items to the Custom Lists dialog. You can then fill items from the defined custom lists by using the fill handle.

Say that you work at the Bigger Burrito Co., and you constantly need to type the flavors of burrito filling. Here’s how you can simplify this task.

1. Type the list in a column. (Or, find an existing range with the list.) Either way, select the list before going to step 2.
2. In Excel, choose File, Options, Advanced. Scroll down to General and choose Edit Custom Lists.
3. Click the Import button in the Custom Lists dialog in order to import your custom list.

Five selected cells say Baracoa, Chicken, Carnitas, Steak, and Veggie. I must have been eating at Chipotle Restaurants when I created this list. — Figure 92. Type this list for the last time.

In the Custom Lists dialog, click Import to bring the list of products into the Custom Lists collection. The dialog already includes lists such as Sun, Mon, Tue. — Figure 93. Import the list from a range.

Note that if you later change the flavors in this list, you can edit the list in this dialog. Make sure to click the Add button to commit the changes. After you add the custom list, you can type any item from the list in a cell and then drag the fill handle. Excel will fill in the remaining items from the list. If you go too far, the list will repeat.

Additional Details: Say that you want to store a list of names, and the first name in the list is a really long name, such as John Jacob Jingleheimer Schmidt. Rather than having to type this name to start the list, you could make the first item in the list the heading. So, perhaps you could type Class1 or MktgDept and drag the fill handle to get the correct list.

Additional Details: The custom list is flexible with regards to case. If you type the first item in all caps, the list will fill as all caps. If you use lower case, the list will fill as lower case.

Additional Details: Custom lists are stored in your computer’s registry. It is therefore very difficult to transfer a list from one computer to another. One method is as follows:

1. Set up a custom sort using your custom list. (See How to Sort a Report into a Custom Sequence)
2. Move the workbook to the new computer.
3. Do a sort on the new computer. In the Order column, select Custom Lists. Click Add.

Since the above process is fairy convoluted, it might be easier to copy the lists into a blank workbook on the old computer, and then import the lists on the new computer.

This article is an excerpt from Power Excel With MrExcel

Title photo by Sean Whelan on Unsplash