How do I create a custom list in Excel Import range?

Microsoft Excel has some nice features that prevent extra typing. One familiar feature is using an autofill series for continuous cells. In addition, the spreadsheet program has some built-in lists like months, days of the week, or sequential numbers. In this tutorial, Ill show how to create a custom list in Excel that provides similar functionality.

Custom Lists vs. Dependent Lists

One question you may want to answer is which type of list do you need. When you think about Excels built-in lists, there are some common denominators. They tend to be used frequently and are known entities. Therefore, you can use them in any workbook. In contrast, there are Excel Dependent Lists, which are conditional. The values you see on that list type are dependent on another value.

For example, if you do a lot of reporting by departments, you might want to create a custom list that includes all of them. However, if you needed to do a regional sales commission report, you might make a dependent list that links individual sales reps to a specific region.

List Direction Doesnt Matter

Probably the first Microsoft Excel feature I valued was allowing the spreadsheet to prefill cells for me. I could type January, grab the fill handle and extend the range to add in the rest of the year. I didnt have to type February, March, and so on. I could do the same thing with days of the week and even omit the weekends.

What was equally appealing was I could go horizontal or vertical. This made building a standard report easier. I didnt have to type these cell values or get locked in by spreadsheet orientation, even though I could transpose columns if needed. I also could use these custom lists in any worksheet.

How to Create a Custom List in Excel

1. Type in your values. I chose to use Column A. Alternatively, you could use a row.
2. Highlight the list items. These entries will become the basis for your list.

1. Click theFilemenu at the top.
2. From the left pane, selectOptions. This will be toward the bottom.

1. Under Excel Options, clickAdvanced.
2. Scroll down to theGeneralsection.

1. Look for theEdit Custom Listsbutton.
2. TheCustoms Listsdialog box will open.
3. You should see your highlighted data range by theImportbutton.

1. Click theImportbutton. Your highlighted values will show under theList Entriessection.
2. ClickOK.
3. This will open theExcel Optionsdialog again.
4. ClickOKagain on theExcel Optionspanel. Your list has been added.

Now that youve got your list defined, you can start using it. The process is similar to other auto drag options.

How to Use Your Custom List

1. Type your first value, such as Boston, into a cell.
2. Click the cell so the border shows.
3. Move your mouse over the lower right corner till the + appears.
4. Left-click and drag over the cells you want to be filled. You can go down or across. As you drag the fill handle, you should see labels showing the list items.

Once you get the hang of creating Excel custom lists, youll start thinking of additional areas where you can use them. Perhaps, youre the commission of a fantasy sports league and need to enter transactions around the same sports teams each week. Or, youre getting a jump on your holiday gift list and need to define friends and family. Regardless of the purpose, Excel custom lists can make data entry faster and consistent.