Fill Lists Made to Order
Excel is quite capable of generating a new series on its own when the initial entry in the series (or first two entries, when using an increment besides one) contains a numerical component that it can increase or decrease. However, to have the program generate a series containing no numerical elements (such as the days of the week: Monday, Tuesday, Wednesday, and so on), Excel needs to know beforehand all the items in the series and their order.
To do this, you create a custom list. When creating a custom list, you can have Excel generate it from a cell range in a worksheet that already contains the items, or you can enter the items manually. I suggest that you have Excel create a new list from existing entries in the spreadsheet whenever possible. That way, you have a chance to proof the entries in the worksheet before you turn them into a custom list.
To create a custom list from existing worksheet entries, follow these steps:
- Enter the entries for the custom list in their proper sequence down a column or across a row of the active worksheet.
- Select the cell range containing the entries for the custom list.
- Choose Tools → Options to open the Options dialog box and then select the Custom Lists tab.
When you open the Custom Lists tab after selecting the cell range with these entries, this cell range (in absolute values) appears in the Import List from Cells text box. If this range is not correct or you forgot to select it before opening the Options dialog box, click in this text box and then select the range in the worksheet. (Excel automatically collapses the Options dialog box as you drag through the cells.) - Click the Import button.
As soon as you click Import, Excel adds the list to the Custom Lists pane on the left and displays all its entries in the List Entries pane on the right. You can then edit the individual entries in the List Entries box if necessary. (This is where you would type the entries for a new list that you create from scratch before clicking the Add button.) - Click OK to close the Options dialog box.
After creating a custom list, you can then generate the list across any row or up or down any column simply by entering the initial entry in a cell and then dragging the Fill handle in one of the four directions.
