Excel 2007 - Creating Custom Lists

In a recent Introduction to Excel 2007 training session in Atlanta, I went over one of my favorite Excel features: lists.

Excel's lists feature is an incredible time saver. If Excel recognizes certain items as a list, you do not have to enter these items. All you do is enter the first item, click and drag and Excel will fill in the rest.

Here's an example. If you enter the word "January" in a cell, and use your mouse button to drag down from the lower right hand corner of the cell, Excel will recognize this as the beginning of a list. As you can see from the diagram below, a box pops up showing the subsequent months.




When you release the mouse, Excel will fill the cells with the months
as shown below.




Excel's fill series even works if the list moves from left to right.




Anything that Excel recognizes as a series can be used in this way.

Here are some examples:

• Days of the week,
• Years,
• Quarters (ex., Qtr 1, Qtr 2, etc.).

Here's where this feature gets even more powerful - did
you know that you can create your own list to use in Excel?

Two doctors were taking my Excel training class because they were opening a medical practice in Atlanta. One list they thought would be extremely helpful would be a list of their insurance companies.

Setting up a custom list of insurance companies would provide the doctors the following benefits:

• Save time - Because they wouldn't have to keep typing the same information over and over.
• Reduce typos - Since the list is stored in one place, there is no chance of reducing the typos.
• Increase efficiency - When an insurance company is added or removed, they simply have to edit the list.

Setting up a custom list.

Enter the values for your list in a range of cells (ex., Cigna, Aetna, Kaiser). Make sure to enter the items in the list in the order you need them to appear. For example, if it's important that the names of the insurance companies are in alphabetical order, type them in alphabetical order (ex., Aetna, Cigna, Kaiser).

Select the range of cells.




Click the Microsoft Office button.




When the menu expands, click the Excel Options button.




Click the Popular category.

Under Top Options for working with Excel, click Edit Custom Lists.





In the Custom Lists dialog box, you will notice the cell reference of the items that you selected is displayed in the Import list from cells box.




Click Import to add items in the list that you selected to the List Entries
box.




Click OK twice.


Now you can type any word in the list, click and drag, and Excel will fill in the rest.