What Is Custom List In Excel?
A custom list in Excel is a feature that allows users to create their unique order of items for sorting purposes. It is particularly useful when dealing with data that follows a specific sequence or hierarchy not already built into Excel’s default sorting options.
Consider the below example. To create a custom list in Excel, we can follow the below steps:
First, open Excel and navigate to the File tab. Next, select Options. In the Options menu, choose Advanced and click on Edit Custom Lists.
Next, click on New List and then select Add. Enter the desired details in the List Entries box, pressing Enter after each entry. Next, click OK.
Then, click on the OK option to confirm the changes. Return to the Excel file and enter only the first name from the custom list. Drag from the first entry to automatically populate the remaining names vertically.
Table of contents
Key Takeaways
- Custom lists are managed through the Excel Options dialog box under the Advanced tab, where users can input their preferred ordering.
- The lists can also be imported and exported across different workbooks or shared among colleagues, making it convenient for collaborative projects.
- Remember, the custom list function empowers Excel users with more flexibility and control over how their data is organized and presented for analysis.
- Utilizing a custom list in Excel can greatly assist in collecting data and creating comprehensive reports.
- Furthermore, it is worth mentioning that entries within a list can be easily modified or deleted according to the user’s preference.
How To Create A Custom List In Excel?
Creating a custom list in Excel allows users to define their sequence or order that they can easily apply to data entries.
- Firstly, open Excel and navigate to the File tab, then select Options and subsequently choose Advanced. Scroll down until we find the General section, and click Edit Custom Lists. In the resulting dialog box, click on the box labeled List Entries and input our desired custom list values in either ascending or descending order, separating each entry with a comma.
- After entering our list values, click the Add button to save them in Excel’s memory.
- Once we want to apply this custom list to data cells, select the range of cells and navigate again to the File tab. From there, choose Options, then proceed with selecting Advanced, followed by clicking on the checkbox for Enable fill handle and cell drag-and-drop.
- Finally, press the OK button for confirmation. By implementing these straightforward steps carefully from start to finish, users can create and utilize customized lists effectively for data organization in Excel.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Example #1 – Custom List Created Manually In Excel
Please follow the steps below to create a custom list in Excel:
Step 1: To begin with, open Excel and select the File tab. From there, select Options.
Step 2: Next, in the Options menu, choose Advanced and then, click Edit Custom Lists.
Step 3: Then, click New List and select Add.
Step 4: Now, enter the desired details in the List Entries box, pressing Enter after each entry. Then, click OK.
Step 5: Next, click on the OK option to confirm the changes.
Step 6: Now, return to the Excel file and enter only the first name from the custom list.
Step 7: Next, drag from the first entry to populate the remaining names automatically in vertical order.
Following these steps will automatically update the details without manual input in the Excel file.
It is worth noting that the days of the week do not need to be created manually in the Excel custom list, even if the first day of the week is included.
Now, drag the first entry cell to populate the weekdays, and the other weekdays will be auto-populated. The user cannot delete this pre-existing list in Excel.
Similarly, the months of the year are already available in Excel, eliminating the need to create a list for the days of the month manually.
To populate the month’s name, enter it in a cell and drag that cell. We can see the month’s name is automatically populated.
Any custom lists created by the user will be visible in the Excel custom lists. If desired, the user can click Delete to remove manually created lists.
Remember, we can create the custom list horizontally or vertically.
Example #2 – Create A List In Different Sheet
In addition to that, we can generate lists on a separate sheet.
Step 1: First, we must input the necessary details into the newly created sheet.
Step 2: Next, we should navigate to the new list. Then, click on the Add button and the Arrow button.
Step 3: Then, the custom list in Excel will then be populated. At this point, we must select the recently entered details. The dollar and semicolon signs will be automatically generated, eliminating the need for manual input.
Step 4: Next, proceed to click on Import.
Step 5: Then, click OK twice.
Step 6: Now, move in horizontal order. Once again, we must enter the first name from the entry list.
Step 7: Finally, drag the cell from the initial entry.
Alternatively, we can view this list on a different sheet.
Meanwhile, it is worth noting that the user is not required to input a comma manually. Instead, the custom lists dialog will automatically generate the comma.
Important Things To Note
- To ensure efficiency and avoid redundancy, it is advisable to refrain from creating existing lists, such as the days of the week or months in a year.
- It is important to note that the first entry should be made during the initial preparation of the list.
- To maintain clarity and conciseness, avoiding including lengthy sentences in the list is recommended.
Frequently Asked Questions
• Firstly, the custom list feature is only available for Windows versions of Excel. It means that we cannot use it on Mac operating systems.
• Additionally, a custom list can only contain up to 255 values, as Excel limits the list size.
• Moreover, after we create a custom list in Excel, we cannot edit or update directly within the program. Instead, any changes must be made by importing a new set of values into the custom list feature.
• We can create personalized lists that align with specific business needs. This enables users to customize sorting and filtering options for their data, improving efficiency and accuracy in organizing large datasets.
• Moreover, custom lists save time by eliminating the need for manual input of repetitive information. By defining unique lists such as employee names or product categories, professionals can quickly populate cells with pre-defined options, reducing errors associated with typing inconsistencies.
Now, for example, we can create a customized list in Excel. We can follow the step-by-step instructions provided below:
To start with, open Excel and select the File tab in the top left corner. From the drop-down menu, select Options to access the Excel Options window. Within the Excel Options window, choose the Advanced tab. Scroll down and select Editing options, and click on the button labeled Edit Custom Lists.
Next, click the New List button in the Custom Lists window to create a new custom list. Select the Add button to add the desired details to our custom list.
Then, enter each entry in the List Entries box, pressing Enter after each entry to move to the next line. Once we have entered all the desired details, click the OK button to save our custom list.
Confirm the changes by clicking on the OK button in the Excel Options window.
Yes, in Excel, we can sort data based on the order of a custom list.
Custom lists are useful when we want to sort data by a specific sequence that is not alphabetical or numerical. To begin, we must create your custom list by going to File > Options > Advanced > Edit Custom Lists.
Here, we can add values according to our desired sequence. Once we have created our custom list, select the range of cells we want to sort and navigate to Data > Sort.
Now, in the Sort dialog box, choose the column we want to sort by, and under Order, select Custom List.
From there, click the button next to Custom List and choose our created list from the options provided. Click OK, and Excel will arrange our data based on the order specified in our custom list. This feature efficiently sorts non-alphabetical or non-numerical data sets according to customized preferences in Excel’s professional environment.
Download Template
This article must help understand Custom List in Excel formulas and examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to Custom List In Excel. We learn to create a sequence using excel custom list feature with examples and downloadable template. You can learn more from the following articles –
Leave a Reply