How to Create Custom AutoFill Lists in Excel 2016
Just as you can use Excel 2016’s AutoFill to fill out a series with increments different from one unit, you can also get it to fill out custom lists of your own design.
For example, suppose that you often have to enter a standard series of city locations as the column or row headings in new spreadsheets that you build. Instead of copying the list of cities from one workbook to another, you can create a custom list containing all the cities in the order in which they normally appear in your spreadsheets.
After you create a custom list in Excel, you can then enter all or part of the entries in the series simply by entering the first item in a cell and then using the Fill handle to extend out the series either down a column or across a row.
To create a custom series, you can either enter the list of entries in the custom series in successive cells of a worksheet before you open the Custom Lists dialog box, or you can type the sequence of entries for the custom series in the List Entries list box located on the right side of the Custom Lists tab in this dialog box, as shown.
If you already have the data series for your custom list entered in a range of cells somewhere in a worksheet, follow these steps to create the custom list:
Click the cell with the first entry in the custom series and then drag the mouse or Touch pointer through the range until all the cells with entries are selected.
The expanded cell cursor should now include all the cells with entries for the custom list.
Select File→Options→Advanced (Alt+FTA) and then scroll down and click the Edit Custom Lists button located in the General section.
The Custom Lists dialog box opens with its Custom Lists tab, where you now should check the accuracy of the cell range listed in the Import List from Cells text box. (The range in this box lists the first cell and last cell in the current selected range separated by a colon — you can ignore the dollar signs following each part of the cell address.) To check that the cell range listed in the Import List from Cells text box includes all the entries for the custom list, click the Collapse Dialog Box button, located to the right of the Import List from Cells text box. When you click this button, Excel collapses the Custom Lists dialog box down to the Import List from Cells text box and puts a marquee (the so-called marching ants) around the cell range.
If this marquee includes all the entries for your custom list, you can expand the Custom Lists dialog box by clicking the Expand Dialog box button (which replaces the Collapse Dialog Box button) and proceed to Step 3. If this marquee doesn’t include all the entries, click the cell with the first entry and then drag through until all the other cells are enclosed in the marquee. Then, click the Expand Dialog box button and go to Step 3.
Click the Import button to add the entries in the selected cell range to the List Entries box on the right and to the Custom Lists box on the left side of the Custom Lists tab.
As soon as you click the Import button, Excel adds the data entries in the selected cell range to both the List Entries and the Custom Lists boxes.
Select the OK button twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box.
If you don’t have the entries for your custom list entered anywhere in the worksheet, you have to follow the second and third steps listed previously and then take these three additional steps instead:
Click the List Entries box and then type each of the entries for the custom list in the order in which they are to be entered in successive cells of a worksheet.
Press the Enter key after typing each entry for the custom list so that each entry appears on its own line in the List Entries box, or separate each entry with a comma.
Click the Add button to add the entries that you’ve typed into the List Entries box on the right to the Custom Lists box, located on the left side of the Custom Lists tab.
Note that when Excel adds the custom list that you just typed to the Custom Lists box, it automatically adds commas between each entry in the list — even if you pressed the Enter key after making each entry. It also automatically separates each entry on a separate line in the List Entries box — even if you separated them with commas instead of carriage returns.
Click the OK button twice to close both the Custom Lists box and Excel Options dialog box.
After you’ve created a custom list by using one of these two methods, you can fill in the entire data series by entering the first entry of the list in a cell and then dragging the Fill handle to fill in the rest of the entries. If you ever decide that you no longer need a custom list that you’ve created, you can delete it by clicking the list in the Custom Lists box in the Custom Lists dialog box and then clicking the Delete button. Excel then displays an alert box indicating that the list will be permanently deleted when you click OK. Note that you can’t delete any of the built-in lists that appear in this list box when you first open the Custom Lists dialog box.
Keep in mind that you can also fill in any part of the series by simply entering any one of the entries in the custom list and then dragging the Fill handle in the appropriate direction (down and to the right to enter succeeding entries in the list or up and to the left to enter preceding entries).