Do you frequently have to fill in the same sets of data into spreadsheets? Just create a custom list in Excel. The next time you need one of your lists, let AutoFill do the heavy lifting.
Once you have set up this Excel feature, you can easily fill a range of cells with your own lists, like department names, clients, age ranges, and any other list you might need.
Let’s look at how AutoFill works and how you can create your own custom lists in Excel.
How to AutoFill in Excel
Excel includes several built-in lists, including months and days of the week. Let’s look at a simple example to see how AutoFill works.
Type Sun (for Sunday) into cell B2. Then, put the cursor over the lower-right corner of cell B2 until it turns into a plus sign and drag right to cell H2.
When you release the mouse button, the AutoFill feature automatically fills the cells with the abbreviations for the days of the week. You can also do this with the full names of the days of the week and the short and full names of the months of the year. AutoFill also works for numbers, dates, and times.
How does Excel know what to put in the cells? The days of the week is a predefined list in Excel. You can enter any of the items in this list in a cell and drag across to automatically fill in the rest of the items. If you drag across more than six more cells for the days of the week, Excel wraps around to the beginning of the list.
How to Create a Custom List in Excel
You can use the custom list feature to create and store lists you use often, such as a list of department names, employee names, clothing sizes, states, and just about any other list you can think of. These lists can be used as headings on columns or rows or to populate dropdown lists to make data entry quicker and more accurate.
- To create your own custom list, click the File tab.
- Then, click Options on the backstage screen.
- On the Excel Options dialog box, click Advanced. Then, scroll down to the General section on the right and click Edit Custom Lists.
- In the Custom lists box, you’ll see the predefined lists of days of the week and months of the year.
There are three ways you can create your own custom list.
1. Enter Your List Directly
The first way to create your own custom list is to enter it directly on the Custom Lists dialog box.
- Type some list entries into the List entries box, one item per line, and click Add.
- This is the easiest way if you have a short list not included on any of the worksheets in your workbook.
2. Import Cells From a Worksheet
The second way to create a custom list is to import them from cells in one of your worksheets.
- Click the cell selector button on the Import list from cells box.
- The Custom Lists dialog box shrinks down to just the Import list from cells box. Select the worksheet containing the list you want to import. Then, select the range of cells containing the items in the list and click button on the right side of the Import list from cells box.
- The Custom Lists dialog box expands again. You may notice that the title of the dialog box changes to Options. It’s still the same dialog box, though. Click Import to add the list items from the worksheet to the List entries box.
- This method makes it easy to add a custom list that you already have in cells in your workbook.
3. Import a List From a Named Cell Range
The third way to create a custom list is to import a list from a named range of cells.
- Before opening the Custom Lists dialog box, enter each item in your list in a separate cell either in one column or one row on a worksheet in your workbook. Select the items, enter a name for the range of selected cells in the Name Box, and press Enter.
- Then, on the Custom Lists dialog box, enter an equal sign (=) followed by the name you assigned to the range of cells containing your list (no space between the equals sign and the text) into the Import list from cells box. For example, we named our cell range Food, so we entered =Food. Click Import.
Note: When you import a custom list from a named cell range on a worksheet, the list on the Custom Lists dialog box is not linked to the original list on the worksheet. If you change the list on the worksheet, the custom list on the Custom Lists dialog box will not change, and vice versa.
- If you’ve created lists in named cell ranges in your workbook, this method makes it easy to add them as custom lists that will be available in any new or existing workbook.
3 Examples of Custom Excel Lists
There are many uses for custom lists in Excel, and we can’t cover all of them here. But, here are a few examples of how you can use custom lists to make the creation of spreadsheets and data entry easier and more efficient in Excel.
- If you create spreadsheets with data for departments in your company, you can create a custom list containing the department names. For example, Accounting, HR, Marketing, Development, Technical Support. Then, you can use AutoFill to quickly create headings for columns or rows.
- Maybe you keep track of clothing inventory. So, you might want lists for sizes (S, M, L, XL, and XXL), styles (crew neck, V-neck, mock neck), sleeve length (short sleeve, long sleeve, three-quarter sleeve, sleeveless), and colors (Black, White, Gray, Blue, Red, Pink, Purple, Green, Yellow). From these lists, you can quickly create consistent dropdown lists containing these options.
- Are you a teacher? You can create a custom list of all your student names and easily insert that list of names into a row or column in a spreadsheet for keeping track of information like grades or attendance.
Create Your Own Custom Lists
The custom lists feature in Excel allows you to easily and quickly customize your spreadsheets to fit your needs. Once you add a custom list to Excel, it’s available in all new and existing spreadsheets.
What kind of custom lists do you create? Which method of adding a custom list do you prefer? Share your thoughts and experiences with us in the comments below.
Image Credit: albertyurolaits/Depositphotos