In Excel, sorting data can help you organize information quickly, but sometimes you may want to sort data in a specific order that doesn’t follow the standard alphabetical or numerical sequence. Excel allows you to create a custom sort list, which is particularly useful for sorting months of the year, days of the week, or any other list with a unique order.
This article will guide you through the steps to create and use a custom sort list in Excel.
What is a Custom Sort List in Excel?
A custom sort list allows you to sort data in any order you choose, based on a specific sequence that doesn’t fit traditional alphabetical or numerical order. For example:
- Sorting months of the year in chronological order (January, February, etc.).
- Sorting days of the week (Monday, Tuesday, etc.).
- Sorting any other set of data, such as product categories or regions, based on your custom preferences.
Why Use a Custom Sort List in Excel?
Custom sort lists are useful for:
- Sorting data in a non-standard order.
- Maintaining consistency in the way data is presented.
- Organizing items by a particular sequence, such as prioritizing tasks or categorizing products by specific attributes.
Step-by-Step Guide to Create a Custom Sort List in Excel
Step 1: Open the Excel Workbook
- Launch Excel and open the workbook that contains the data you want to sort.
Step 2: Access the Sort Options
- Select the Data tab from the ribbon at the top of Excel.
- Click on the Sort button in the Sort & Filter group. This opens the Sort dialog box.
Step 3: Open the Custom Lists Dialog
- In the Sort dialog box, click on the Order dropdown list.
- From the dropdown, select Custom List…. This will open the Custom Lists window.
Step 4: Create a New Custom List
- Type the List Items:
- In the Custom Lists window, you’ll see a section called List entries.
- To create a new custom list, type the values you want to sort by into the List entries box. Enter each item in the list on a new line (e.g., January, February, March, etc.).
- Alternatively, if the list items are already in a column in your Excel sheet, you can import them by clicking on the Import button, which will allow you to select the range of data.
- Click Add:
- Once your custom list is entered, click the Add button to save the list. Your new custom list will now appear in the Custom lists box.
Step 5: Use Your Custom Sort List
- After creating the custom list, click OK to close the Custom Lists window and return to the Sort dialog box.
- Now, you can use your new custom list to sort your data.
Step 6: Sort Data Using the Custom List
- In the Sort dialog box, ensure that you have selected the correct column to sort (e.g., Column A).
- Under the Order section, select your custom list from the dropdown menu.
- Click OK, and Excel will sort your data according to the custom order you created.
Example 1: Sorting Months of the Year
Imagine you have a list of months in Excel that is out of order, and you want to sort them in the correct chronological order.
- Select the data (e.g., the months in column A).
- Go to Data > Sort.
- In the Sort dialog box, under Order, click Custom List.
- In the Custom Lists window, type the months in chronological order, separated by a comma (January, February, March, …, December), or import from a list if it is already on your sheet.
- Click OK to apply the sort. Your months will now be in the correct order.
Example 2: Sorting Days of the Week
If you have a list of days that isn’t sorted properly (e.g., Monday, Wednesday, Tuesday), you can easily sort them with a custom list.
- Select your data (e.g., the days of the week in column B).
- Go to Data > Sort.
- Click Custom List in the Sort dialog box.
- In the Custom Lists window, enter the days of the week in the order you want: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
- Click Add, then OK to apply the sort.
Editing an Existing Custom List
If you want to modify an existing custom list, follow these steps:
- Open the Custom Lists window from the Sort dialog box.
- Select the custom list you want to modify and click Edit.
- Make the necessary changes (add or remove items) and click OK to save.
Deleting a Custom List
If you no longer need a custom list, you can delete it:
- Open the Custom Lists window again.
- Select the custom list you want to delete.
- Click the Delete button and confirm your action by clicking OK.
Tips for Using Custom Sort Lists
- Multiple Custom Sorts: You can apply custom sorts to multiple columns. For example, you could first sort by month and then by region within each month.
- Sort by Cell Colors or Fonts: In addition to custom lists, Excel also allows sorting by cell color, font color, or cell icon. This can be useful for visual data organization.
- Dynamic Sorting: If your data changes frequently, using a custom sort can help keep the data organized automatically, especially when new entries are added.
Conclusion
Creating a custom sort list in Excel is an effective way to organize your data in a specific sequence. Whether you’re sorting months, days, or any other unique data set, Excel provides an easy-to-use feature to create and apply custom orderings.
- Create custom lists to sort your data in any order, tailored to your specific needs.
- Sort data using your new custom list in just a few clicks.
- Modify and delete custom lists as needed to maintain an organized workbook.
By mastering custom sort lists, you can efficiently manage data in Excel, making it more accessible and easier to analyze.