How to Custom Sort Excel Data? 2 Easy Steps
(Note: This guide on how to custom sort Excel data is suitable for Excel 2016, 2019, 2021 and Microsoft 365 versions)
In this article, let us see how to use Excel custom lists to sort information in a list or table in a specific order.
You’ll learn:
Custom Sort Excel – An Overview
One of the first things most of us learn to do in Excel is sort a list of data. We can sort text or numeric values in Ascending or Descending order and sort by one column or multiple columns. We can even sort by cell colour and font colour. However, did you know that Excel offers more freedom to sort using any custom order specified by the user?
Let us say, for example, you are facing this peculiar situation: You have a list of managers and their respective sales figures. Now, you are asked to sort this data in the order of their departments, instead of the usual alphabetical or numerical order.
You can easily do this with the help of Excel custom lists. We’ll see how, in the next sections.
Related:
How to Sort a Pivot Table in Excel? 6 Best Methods
3 Best Methods to Find Duplicates in Excel
How to Combine Two Columns in Excel? 2 Easy Methods
Step 1 – Create a Custom List
In this example, we want to sort the managers and their sales figures in the order specified in Column D. Currently they are sorted A-Z by manager name.
- From the File tab, click Options.
- Go to the Advanced page.
- Scroll down to the General section.
- Click Edit Custom Lists.
In the Custom Lists dialog box, we can see any custom lists we’ve created and custom lists that are available in Excel by default.
- Select NEW LIST from the Custom lists area.
We can populate a custom list in two ways: import the list from cells or type the list entries manually. Importing the list from cells is more efficient if you have a long list.
- Click the up arrow next to Import list from cells to minimize the Custom Lists dialog box.
- Select the cells to import.
- Click the down arrow to maximize the Custom Lists dialog box.
- Click Import.
- Click OK.
- Click OK again.
Step 2 – Use Excel Custom Sort
Now that we have created a custom list, we can use it to custom sort Excel data in that order.
- From the Data tab, in the Sort & Filter group, click Sort.
- In the Sort by drop-down list, select Manager.
- In the Sort On drop-down list, select Cell Values.
- In the Order drop-down list, select Custom List.
- Select the Custom List which you just created.
- Click OK.
- Click OK again to confirm the sort.
The list is now sorted according to the order specified in the Custom List.
Suggested Reads:
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide
How to Use the NPER Excel Formula: 2 Easy Examples
Closing Thoughts
In this short guide, we saw how to use Excel custom lists to custom sort Excel data. We hope you find this guide helpful. You can read more about custom lists in the following articles.
The Training Lady – How to store a custom list in Excel
Productivity Portfolio – Learn the Benefits of an Excel Custom List
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.