Excel Hacks Ebook [Electronic resources]

Hack 34 Create Custom Lists

By adding a custom list to Excel, you can type the first item in the list, drag it down using the fill handle, and watch the list fill automatically.

One of Excel's most popular time-saving features is its ability to automatically increment not only numbers, but certain text as well. Excel has a couple of built-in lists, such as days of the week and months of the year. Currently when you use the fill handle, you type the first item, then use the fill handle to fill cells with the next item in the list, and so forth. You can easily create our own custom list for commonly used items.

The most flexible way to create a custom list is to enter the list contents into a range of cells. For example, say you have a list of 100 employee names. Enter each name, starting with cell A1 and ending with cell A100, and sort the list, if needed. Then select Tools Options..., and on the Custom Lists page tab, click Import List from the Cells box. Using the mouse pointer, left-click in cell A1 and drag all the way down to A100. Then click the Import button. From this point on, the custom list will be available to all workbooks on the same computer.

Once you create a custom list, you can turn the list upside down. To do this, return to the column next to the custom list and place the last entry from the list in the top cell. In the cell beneath it, place the second-to-last entry. Select both cells and double-click the fill handle. The list you produced should be reversed. You can sort the original list by selecting Data Sort Options, nominate the list, and then sort it.