How to Create a Custom List in Microsoft Excel

Do you frequently have to fill in the same sets of data into your Microsoft Excel spreadsheets? You can easily create a custom list in Excel. Then the next time you need it, let AutoFill do the heavy lifting.

Unlock the FREE "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Read our privacy policy

Once you set up this Excel feature, you can quickly fill a range of cells with your own list items, like department names, clients, age ranges, and any other list you might need.

Let’s look at how you can create your own custom lists in Excel and how to use them with AutoFill.

How to Create a Custom List in Excel

You can use the custom list feature to create and store lists you use often. These lists can be used as headings for columns and rows or to populate dropdown lists for faster, more accurate data entry.

Create a custom list on Windows

1. Click the File tab.
2. Select Options on the left.
3. In the Excel Options dialog box, click Advanced.
4. 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.

Create a custom list on Mac

1. Click Excel > Preferences from the menu bar.
2. Select Custom Lists.

Like on Windows, you’ll see built-in lists like abbreviated days of the week and months of year.

There are three ways you can create your own custom list in Excel on both Windows and Mac: enter your list directly, import worksheet cells, or import from a named cell range.

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. This is the easiest way if you have a short list not included on any of the worksheets in your workbook.

1. Make sure that New List is selected in the Custom lists box.
2. Type your list items into the List entries box, one item per line, and click Add.

You’ll then see that list display in the Custom lists box.

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. This method makes it simple to add a custom list that you already have in cells in your workbook.

1. Click the cell selector button on the Import list from cells box.
2. 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 the button on the right side of the Import list from cells box.
3. 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.

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. This method makes it easy to add them as custom lists available in any new or existing workbook.

1. Before opening the Custom Lists dialog box, enter each item in your list in a separate cell either in one column or one row in a spreadsheet.
2. Select the cells, enter a name for the range of selected cells in the Name Box, and press Enter.
3. Open the Custom Lists dialog box, enter an equal sign (=) followed by the name you assigned to the range of cells into the Import list from cells box. For example, we named our cell range Sides, so we entered =Sides.
4. 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 in the worksheet. So if you change the list on the worksheet, the custom list will not change and vice versa.

How to Use AutoFill in Excel

As you’ve seen when creating your own, Excel includes several built-in lists including days of the week. Let’s look at a simple example to see how AutoFill works.

Type Sunday into a cell and then put the cursor over the lower-right corner of the cell until it turns into a plus sign. Drag to the right and you’ll see that the cells will fill with the subsequent days of the week. Release when you finish with the cells you want to fill.

How does Excel know what to put in the cells? Since the days of the week is a predefined list, you can enter any of the list items in a cell and drag across or down to automatically fill in the rest. If you drag across more than six more cells for the days of the week, for example, Excel starts back at the beginning of the list.

After you create your own custom list, you can do the same thing to automatically fill adjacent cells with your list items.

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 just a few examples of how you can use custom lists to make data entry easier and more efficient in Excel.

1. 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, and Technical Support.
2. If you’re a teacher, you can create a custom list of all your student names. Then easily insert that list into a row or column for keeping track of information like grades or attendance.
3. If you track clothing inventory, you might want lists for sizes (S, M, L, XL, and XXL), styles (crew neck, V-neck, mock neck), and colors (Black, White, Gray, Blue, Red,). From these lists, you can quickly create consistent dropdown lists containing these options.

Create Your Own Custom Lists

The custom lists feature in Microsoft 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.

For more help with your next new workbook, take a look at these Excel document settings you should check.

Image Credit: albertyurolaits/Depositphotos

Read the full article: How to Create a Custom List in Microsoft Excel

Read Full Article