Excel’s AutoFill feature saves time by extending common sequences like months or weekdays. You might need to repeat a specific list of names, codes, or locations that Excel does not recognize. A custom AutoFill list lets you define your own repeating sequence. This article explains how to create and manage these lists for any data you use regularly.
Key Takeaways: Creating and Using Custom AutoFill Lists
- File > Options > Advanced > Edit Custom Lists: Opens the dialog to create a new list by typing entries or selecting a cell range.
- Drag the fill handle after typing one list item: Excel automatically fills cells with your entire custom sequence.
- Delete a custom list from the same dialog: Removes the selected list from Excel’s memory without affecting spreadsheet data.
What a Custom AutoFill List Does
Excel includes built-in AutoFill lists for days, months, and numeric patterns. A custom list is a user-defined series of text or numbers. Once created, you only need to type the first item in a cell. Dragging the fill handle completes the sequence, repeating it as far as you drag. This works for any ordered data, such as department names, project stages, or regional office codes.
Lists are stored in Excel’s application settings, not within a single workbook. A list created in one file is available in all other workbooks on the same computer. The feature requires no special add-ins. You only need permission to edit Excel’s options. The main limitation is that lists cannot contain formulas. They must be static text or numbers.
Steps to Create a New Custom List
You can create a list by typing items directly or importing them from a worksheet range. Use the first method for short, new lists. Use the import method when your sequence already exists in a spreadsheet.
Method 1: Type a New List Directly
- Open Excel Options
Click File > Options to open the Excel Options dialog box. - Navigate to Advanced Settings
In the left pane, click Advanced. Scroll down to the General section. - Open the Custom Lists Dialog
Click the Edit Custom Lists button near the bottom of the General section. - Enter Your List Items
In the Custom Lists dialog, ensure NEW LIST is selected in the left box. Click in the List entries box on the right. Type each item, pressing Enter after each one to move to the next line. - Add the List
Click the Add button. Your new list will appear in the Custom lists box on the left. Click OK twice to close all dialogs.
Method 2: Import a List from Cells
- Select Your Data Range
In your worksheet, select the cells containing the sequence you want to use. The cells must be in a single column or row. - Open the Custom Lists Dialog
Go to File > Options > Advanced. Click Edit Custom Lists in the General section. - Import the Range
In the Custom Lists dialog, the cursor will be in the Import list from cells field. If not, click in that field. The selected range address should appear. Click the Import button. - Verify and Save
The imported items appear in the List entries box and the list is added to the Custom lists box. Click OK twice to save and exit.
Common Mistakes and Limitations
Custom lists are powerful but have specific rules. Avoid these issues to use them effectively.
AutoFill Does Not Work After Creating a List
You must type an item exactly as it appears in the list, including capitalization and spaces. Excel matches the cell content to the first item in your custom sequence. If you type a partial match or a different word, AutoFill will not recognize it. Check your list entries in File > Options > Advanced > Edit Custom Lists.
List Items Appear Out of Order
The fill direction depends on how you drag the fill handle. Dragging down or to the right fills items in the order you defined. Dragging up or to the left may fill in reverse order. Always verify the fill direction. The sequence will repeat if you drag beyond the number of items in your list.
Custom List Not Available on Another Computer
Custom lists are stored locally in the Windows registry for your user profile. They do not travel with the workbook file. To use the same list on a different machine, you must recreate it on that computer using the steps above. There is no built-in method to export or import list files.
Creating Lists vs. Using Fill Series: Key Differences
| Item | Custom AutoFill List | Fill Series Command |
|---|---|---|
| Primary use | Repeating a defined text sequence | Creating a numeric or date pattern |
| Data type | Static text or numbers | Numbers, dates, growth trends |
| Setup required | One-time creation in Excel Options | Configure each time via Home > Fill > Series |
| Storage | Saved in Excel settings for all workbooks | No permanent storage; pattern is temporary |
| Fill method | Drag fill handle after typing first item | Select range first, then use dialog box |
You can now create custom sequences for any repeating data. Start by making a list of your most-used project codes or team names. For advanced use, create a list with numbered items like Phase-1, Phase-2 to combine text and numbers. Remember that pressing Ctrl while dragging the fill handle will copy the cell instead of filling the sequence.