Create a Custom List in Excel to transform your data management experience. This feature not only streamlines repetitive tasks and ensures uniformity across your documents but also tailors data analysis to your specific needs. By personalizing your workflow with custom lists, you can enhance productivity, maintain consistency, and visualize data more effectively. Embrace the power of custom lists to make your Excel spreadsheets more efficient and aligned with your unique business or personal objectives.
This Tutorial Covers:
- The best way to make Excel Custom List
- With your own Custom List, you can create your own Sorting Criteria
- How to delete a custom list
- Examples of situations where it can be used
1. The best way to make Excel Custom List
Excel comes with certain pre-fed custom lists by default that you may use to save time.
Use the fill handle to fill in the remaining days, for instance, if you type “Sun” in one cell and “Mon” in the one next to it. If you drag to extend the choices, it will repeat and give you the day’s name once more.
The pre-built custom lists for Excel are listed below. As you can see, the names of the days and months are mostly used since they are constant and will not change.
Now, let’s say you want to make a custom list in Excel for a list of departments you frequently use. In this way, you won’t have to dig through old papers the next time you need the names of all the departments in one spot. Simply type the first two in the list and drag them into place.
The steps to make your own Custom List in Excel are listed below:
Step 1: On the File tab, click.
Step 2: Select “Options”. This will bring up the dialog box for “Excel Options.”
Step 3: In the left-hand menu, select Advanced. Click the “Edit Custom Lists” button under the General section (you may have to scroll down to get to this option).
Step 4: Import the list by choosing the range of cells that contain it in the Custom Lists dialog box. As an alternative, you can type the name by hand into the List Entries field (separated by comma or each name in a new line). Select Add. You will see that your list has been added to the Custom Lists as soon as you click Add.
As soon as you have the list in Excel’s backend, you may utilize it with Autofill in the same way that you would use numbers or months (as shown below).
While it’s amazing that you can easily drag and drop these custom lists names into Excel, there is something even more awesome that you can do with custom lists (that is what the next part is about).
Pro tip: You can also make a named range and utilize it to make the custom list. To do this, click OK after entering the named range’s name in the “Import list from cells” area. The advantage of this is that if you modify or broaden the named range, the custom list will also be instantly modified.
2. With your own Custom List, you can create your own Sorting Criteria:
One amazing feature of custom lists is that you can design your own sorting criteria for them. As an illustration, let’s say you wish to sort a dataset using the criteria High, Medium, and Low.
This is impossible for you to accomplish!
The alphabetical order would be messed up if you sorted alphabetically (it will give you High, Low, and Medium and not High, Medium, and Low).
Custom Lists truly shine in this situation.
You can make your own list of components and use it to arrange the information. The High values will be at the High, followed by the Medium values and the Low values.
Using the instructions provided in the previous section, “The best way to make Excel Custom List” the first step is to create a custom list (High, Medium, Low).
Following the creation of the custom list excels, you can sort using it by following the instructions below
Step 1: Choosing the full collection (including the headers).
Step 2: On the “Data” tab, click. Select the “Sort” icon from the “Sort and Filter” group.
The “Sort” dialog box will then be displayed.
Step 3: Make the following selections in the “Sort” dialog box:
- Columnar sorting Priority
- Sort based on cell values
- Custom Lists, in order. Select the desired sorting criteria after the dialog box has opened, and then click OK.
Utilizing the list, you made and utilized as sorting criteria, the aforementioned procedures would immediately sort the data (High, Medium, Low in this example).
Note: Remember that you don’t always have to make the excel custom sort before using it for sorting. You can follow the instructions above, and when Step 4’s dialog box appears, you can immediately build a list there.
3. How to delete custom list?
You can also easily delete your own custom list in Excel.
The steps to delete your own Custom List in Excel are listed below
Step 1: On the File tab, click.
Step 2: Select “Options”. This will bring up the dialog box for “Excel Options.”
Step 3: In the left-hand menu, select Advanced. Click the “Edit Custom Lists” button under the General section (you may have to scroll down to get to this option).
Step 4: Select the list you want to delete from the Custom Lists box, and then click Delete. After that, click OK.
After that, the “Custom Lists” looks like the below:
4. Examples of situations where it can be used
Here are some scenarios in which making and using custom example lists can help you save time:
- You can instead make a custom list and utilize that if you have a list that you need to manually enter (or copy and paste from another source). You might use product names, regional or country names, or department names from your company as examples.
- You can make a list of the names of your students if you’re a teacher. Thus, you won’t have to bother about manually entering student names or pasting them from another sheet when grading them the following time. This guarantees that there are fewer opportunities for mistakes.
- when you need to sort data using parameters that Excel does not already support. Making a custom list in Excel allows you to use your own sorting criteria, as was discussed in the part above.
This concludes our discussion of creating custom lists in Excel.
Application of Create a Custom List in Excel
- Personalized Sorting Orders: Create a custom list to sort data according to your specific preferences or needs, such as project stages, priority levels, or unique categorizations.
- Repetitive Data Entry: Utilize custom lists to streamline repetitive data entry tasks. For example, quickly fill in months, days, product names, or department names by dragging the fill handle.
- Consistent Data Categorization: Ensure consistency in data categorization across multiple spreadsheets or projects by using a standardized custom list for common entries like department names or status codes.
- Enhanced Data Analysis: Apply custom lists to sort and analyze data in a way that aligns with business logic or project requirements, enhancing the relevance and clarity of data insights.
- Project Management: Manage project tasks by creating custom lists that reflect the specific phases or milestones of your projects, allowing for easy tracking and organization.
- Inventory and Order Processing: Streamline inventory management or order processing by creating custom lists for product categories, types, or processing stages, improving efficiency and workflow.
For ready-to-use Dashboard Templates:
