How to Make Custom Lists to Fill Excel 2013 Worksheets Automatically

Configuring a customized list saves time by enabling quick filling of commonly used text values. As an example, if you repeatedly enter the same product names in Excel spreadsheets, a customized list of products lets you enter the first product and automate the rest, instead of manually typing each value. This feature only works on text values, however, so purely numeric data must be formatted as text to work as a list.

A custom list also adds an additional sorting method for your data. For example, sorting by a rating variable that contains “low,” “medium” and “high” results in an illogical order with medium being at the end of the list. By creating a customized list, you can specify the order, so the sorting works as you planned.

In this example, say you have a list of values “one” through “ten.”

1. Select the values you want included in the list and click the File tab. If you don’t already have the values in a spreadsheet, don’t worry about typing them yet; you’ll have the opportunity to later. If any values in the list contain purely numbers, you need to type them manually later anyway.

2. Click Options on the left menu.

3. Select the Advanced tab and click the Edit Custom Lists button in the General section, near the bottom of the list of options.

4. Make sure New List is selected in the Custom Lists panel (it should be). Notice that there are already commonly used lists for months and days preconfigured.

5. Click Import to automatically add the previously selected values to the Custom Lists. You can also manually enter the range containing the value, if you didn’t previously select the data.

Alternatively, if you didn’t previously select values or you need to enter numbers, enter the list of values and press Enter between entries, so each value appears on its own line. Then, press Add.

6. Click OK in this window and the original Options window to save your list and exit.

7. Try your new list. Enter the first value in your list (or whatever value you wish to begin with) and drag the fill handle over the number of cells you want filled with the list.

If the list is shorter than the number of cells selected, the list starts over.

Note that if your list contains numbers, you need to format the target cells as text before using the list to propagate the values. To change the format, simply highlight the cells and select Text from the drop-down menu in the Home tab’s Number group.

Doing so enables you to use the numeric list you previously configured: