This article will show you how to use custom sort in Excel slicer. Have you ever spent hours surfing through data, trying to find the information you need? Well, custom sorting in Slicers can save you the hassle! This fantastic feature allows you to sort your data in any order you desire, making it easier to identify relevant information quickly.
Here, in the dataset above the Slicer named Vendor, we have used custom sorting for the slicer.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Custom Sort Slicer in Excel (3 Suitable Examples)
Example 1: Custom Sort Slicer with Vendor Names
We will be using a custom sort for the Slicer below. In the Slicer, the vendor Emily will be put in the third position from the top. Before starting the sorting procedure, delete the existing Slicer of the Pivot Table. To start custom sorting, make sure to follow the procedure below accordingly.
- Go to Options from File.
- Click on Advanced and then Click on Edit Custom Lists.
- Now select cells B5 to B14.
- Click on Import.
- When the list appears on Custom Lists, Click on it.
- Manually place Emily in third place from the top.
- Hit OK two times.
- Now insert a slicer of Vendor and the Custom sorted Slicer will appear as below.
Example 2: Custom Sorting of Product in Slicer
In the next example of custom sorting for the slicer. We will be sorting the Slicer mentioned below of Product where the product will be sorted by putting Bananas at first followed by Oranges, Cherries, and Apples. Follow the steps below for a better understanding of the procedure.
- Start with deleting the Product Slicer if it already exists.
- Go to Options from File.
- Click on Advanced and then Click on Edit Custom Lists.
- Now select cell C5 to C14.
- Click on Import.
- When the list appears on Custom Lists, Click on it.
- Manually place Bananas at first followed by Oranges, Cherries, and Apples in the List Entries.
- Hit OK two times.
- Now from Insert Tab, add a Slicer named Product. And the Manually sorted Slicer will appear below.
Example 3: Sorting Months Slicer Chronologically in Excel
In this example, we will learn how to sort Month Slicer chronologically as shown in the figure below. This will be done by custom sorting as mentioned in the two previous examples.
- Start with deleting the Slicer Slicer if it exists already.
- Go to Options from File.
- Click on Advanced and then Click on Edit Custom Lists.
- From Custom Lists, Select the months as shown below.
- Hit OK two times.
- Now from Insert Tab, add a Slicer named Month . And the Chronologically sorted Slicer Month will appear as below.
Reasons Why Slicer Custom Sort May Not Work in Excel
- The data may not be in the right format. For example, if you store data as text instead of numbers, custom sorting may not work.
- The sorting order may not be set correctly. For example, if you want to sort data in ascending order but the sorting order is set to descending, the custom sorting may not work.
- The slicer settings may not be set correctly. For example, if the “Sort by” option is not set to “Custom List”, custom sorting may not work.
- You may be using an older version of Excel that doesn’t support the custom sorting feature.
- Due to a corrupted or damaged workbook. This can happen due to various reasons, such as a system crash or virus attack.
- Due to corrupted or outdated slicer cache. In this case, you may need to clear the slicer cache or rebuild the slicer.
Things to Remember
Before starting, make sure to delete the existing slicer of the dataset. Here, the automatic sorting of buttons is impossible in Microsoft Excel as it lacks a dynamic feature for this function. So after sorting the names manually in the Custom Lists, add a new Slicer and the sorted names will appear there.
Conclusion
In a nutshell, custom sorting of slicers in Excel is a helpful tool that allows users to sort their data in a personalized manner. By following the easy-to-follow steps described in this article, anyone can sort their data quickly and easily, giving them the insights they need to make more informed decisions.