Categories
Excel

Sort and Fill Data with Custom Lists

“Custom lists” is a useful feature that allows you to sort and fill data according to your own criteria. You can create lists that are relevant to your industry, organization, or personal preferences. For example, you can create custom lists of product names, department names, or specific project phases. Custom lists are not limited to a single workbook or worksheet. Once created, you can use them across different workbooks or worksheets, allowing for consistent data entry and autofill functionality in multiple files.

  1. Create a Custom List
  2. Fill a Custom List
  3. Sort a Custom List
  4. Reverse a List (without sorting it)

Create a Custom List

By adding a custom list to Excel, you can type the first item in the list, drag it down using the fill handle, and watch the list fill automatically.

One of Excel’s most popular time-saving features is its ability to automatically increment not only numbers but certain text as well. Excel has a couple of built-in lists, such as days of the week and months of the year. Currently, when you use the fill handle, you type the first item, then use the fill handle to fill cells with the next item in the list, and so forth. You can easily create our own custom list for commonly used items.

To create a custom list, follow these steps:

  1. Select the File tab and click on Options.
  2. In the Excel Options dialog box, click on Advanced in the left pane.
  3. Scroll down to the General section and click on Edit Custom Lists.
  4. In the Custom Lists dialog box, select NEW LIST in the Custom lists box.
  5. In the List entries dialog box, you can:
    • either enter the values for your list in the List entries box, separated by commas or line breaks (we entered Roman numerals 1 to 10 in ascending order to use for sorting and filling).
    • or select a range of cells that contains the values for your list in the worksheet and click the Import button.
  6. Click on Add to add the list to the Custom lists box.
  7. Click on OK to close the Custom Lists dialog box and then click on OK again to close the Excel Options dialog box.

We created a custom list that contains values of Roman numerals I, II, III, IV, V, VI, VII, VIII, IX, and X that represent 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10 respectively. You can now use this custom list to sort and fill data in your worksheet.

Fill data using a custom list

By creating custom lists, you can quickly autofill data based on those lists, saving you time and effort. Instead of manually entering repetitive data, you can simply type the first value and drag the fill handle to populate the remaining values from the custom list.

Video Tutorial: Creating a Custom List and Filling it on the Sheet with Fill Handle

To fill data using a custom list, enter the first value of your list in a cell and then drag the fill handle (the small square at the bottom right corner of the cell) across or down the cells that you want to fill. Excel will automatically fill the cells with the values from your custom list in order.

Sort data using a custom list

To sort data using a custom list:

  1. Select the data range and click on Sort in the Data tab.
  2. In the Sort dialog box, choose the column that you want to sort by
  3. Choose Cell Values from Sort On menu.
  4. Select Custom List from the Order drop-down menu.
  5. Then select your custom list from the list of options and click on OK.

For details, visit How to sort months and weekdays using custom lists.

Reverse a List

Video: Reverse Lists

Once you create a custom list, you can turn the list upside down. To do this, return to the column next to the custom list and place the last entry from the list in the top cell. In the cell beneath it, place the second-to-last entry. Select both cells and double-click the fill handle. The list you produced should be reversed.

Sort and Filter Data

  1. Quickly Sort Data by Single or Multiple Columns
  2. Sort Data with Conditional Formatting Criteria
  3. Sort Weekdays and Months
  4. Sort and Fill Data with Custom Lists
  5. Random Sort using RAND() Function
  6. Filter (AutoFilter) Data
  7. Excel Advanced Filter