Tag Archives: Custom Lists

Discover the Power of Excel's Custom Lists

Discover the Power of Excel’s Custom Lists

By Marie McCooey

Are you aware of Excel’s little-known sorting feature called Custom Lists?

Custom Lists allow you to expand your sorting options from standard alphabetical sorting to sorting by the days of the week and months of the year.

But that is not all.

You’re not restricted to sorting by days of the week or months.

You can create your own Custom Lists to sort or fill in any field, e.g. company locations, sales representatives, products, etc.

Custom lists are a great time-saver and can reduce data entry errors.

For example, the following Employee list needs to be sorted in chronological order by Anniversary Month.

Instead of adding a helper column with the number that corresponds to each month you can use the Month Custom List option to sort by the Anniversary Month column.

Sort by Anniversary Month

Sort by the Anniversary Month

  1. Select the list, excluding the header row.
  2. Click the Data tab, then Sort.
  3. Select the Sort by field, in this example, Anniversary Month and select Custom List for the Order.

Excel includes 4 standard Custom List options;

  • Full and abbreviated Month names
  • Full and abbreviated Days of the week
    In this example, select the full Month names and click OK.

Select Month Names

The list is sorted for you in chronological order by the Anniversary months.

List Sorted Chronologically by Month

 

Create Your Own Custom Lists

Do you frequently type the same entries in your worksheets?

Custom Lists can easily help you enter items you type repeatedly in worksheets.

You have two choices to create a Custom List:

  • Typing the entries
    or
  • Selecting existing values from a worksheet

Add Custom List Entries by Typing

  1. Click the File tab, then click Options.
  2. Click the Advanced tab.
  3. Scroll down to display the General section.
    Create Custom List by Typing
  4. Click the Edit Custom Lists… button.
    The Custom Lists dialog box displays the standard entries included with Excel.

Standard Custom Lists

5. To add your own entries, type each entry in the List Entries list box (in the desired sort order) and press Enter after each entry.

Note: You do not need to enter commas between each entry, Excel adds the commas for you.

Type Custom List Entries

6. When the list is complete, click the Add button.

The new list displays at the end of the standard Custom Lists and can now be used for sorting your data.

Typed Custom List

Create a Custom List with Existing Worksheet Values

If the Custom List values are listed on a worksheet and in the correct sort order, you can easily import the values instead of typing them in.

  1. Select the worksheet values to use for sorting.
    In this case, we’ll use the Company’s shipping options.
  2. Click the File tab, then click Options.
  3. Click the Advanced tab.
  4. Scroll down to display the General section.
  5. Click the Edit Custom Lists button in the General section.
    The Custom Lists dialog box displays the selected range in the Import list from cells field.
    Note: Excel applies absolute cell referencing to ensure the Custom List cell references are fixed.Imported Custom List
  6. Click Import to import the new list.
    The imported list displays at the end of the Custom Lists and is ready to use immediately.

Now that you have created your own Custom Lists, you can use them for sorting and filling data effortlessly.

Filling in Data with a Custom List

Instead of typing your company’s regions or locations over and over again, create a Regions Custom List and use AutoFill to complete the list for you.

For example, your company has offices in several regions and you’d like to avoid typing the regions in each new worksheet.

  1. Create a Regions Custom List.Custom Region List
  2. Enter one of the regions in the first cell and drag the AutoFill handle through the blank cells.

Note: Custom Lists can be entered both horizontally and vertically on a worksheet.
In this example, we’ll enter the Regions horizontally.

Region Custom List with Selection Tool

The Custom List regions display automatically as you drag the mouse across the cell range.

Insert Custom Region List

Note: You can type any item from the Custom List; it doesn’t have to be the first list item.
The list starts with the entry you entered and continues from there.

Use a Custom List to Create Your Own Sorting Criteria in Excel

Let’s use the Company Regions Custom List we’ve created, to sort sales transactions in the following example.

  1. Select the range to sort, including all columns and column headers.

Custom Sort by Regions selection

2. Right-click the selection, select Sort, then Custom Sort.

You’ll see the Sort window displayed.

3. Select Region as the Sort By field and Custom List as the Order field.

Custom Sort by Regions with Sort options

The Custom Lists window displays the available custom lists.

Custom Regions list

4. Select the Region Custom List.

The Region selection displays in the List entries field. Click OK.

The data is now re-sorted by Regions.

Resorted Custom List

Deleting a Custom List

If you no longer use a Custom List you added, you can easily remove it.

Note: Standard Custom Lists, e.g. Month and Days of the Week cannot be deleted, but any Custom Lists you added can be removed.

  1. Click the File tab, Options, then the Advanced tab.
  2. Scroll down to display the General section.
  3. Click the Edit Custom Lists button in the General section.
    The Custom Lists dialog box displays the standard lists and any lists you created.
  4. Select a Custom List you created and click Delete.

HELPFUL TIPS

  • Custom Lists can only contain text or a combination of text and numbers entries.
  • Once a Custom List is created, it is available for all workbooks.