How to Create a Custom List in Microsoft Excel
Pinterest Stumbleupon Whatsapp
Advertisement

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 How to Use Excel's Flash Fill and Auto Fill to Automate Data Entry How to Use Excel's Flash Fill and Auto Fill to Automate Data Entry When you regularly create Excel spreadsheets and populate them with data, you have to know about Auto Fill and Flash Fill. Let us show you what these features do and how they can help you. Read More .

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.

how to create custom lists in excel

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 Automatically Format Data in Excel Spreadsheets With Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More 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.

  1. To create your own custom list, click the File tab.
    how to create custom lists in excel
  2. Then, click Options on the backstage screen.
    how to create custom lists in excel
  3. On the Excel Options dialog box, click Advanced. Then, scroll down to the General section on the right and click Edit Custom Lists.
    how to create custom lists in excel
  4. 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.

  1. Type some list entries into the List entries box, one item per line, and click Add.
    how to create custom lists in excel
  2. 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.

  1. Click the cell selector button on the Import list from cells box.
    how to create custom lists in excel
  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 button on the right side of the Import list from cells box.
    how to create custom lists in excel
  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.
    how to create custom lists in excel
  4. 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.

  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 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.
    how to create custom lists in excel
  2. 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.
    how to create custom lists in excel 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.
  3. 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 Excel Printing Tips: 7 Steps for Perfect Printing Excel Printing Tips: 7 Steps for Perfect Printing Are you having problems printing from Excel? Follow this guide, and your spreadsheets will be on the page in no time. Read More .

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.

  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, Technical Support. Then, you can use AutoFill to quickly create headings for columns or rows.
  2. 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.
  3. 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 5 Excel Document Settings You Must Never Forget to Check 5 Excel Document Settings You Must Never Forget to Check When you pick an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More .

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

Leave a Reply

Your email address will not be published. Required fields are marked *