How To Customize The Order Of Your Excel Slicer Buttons
Slicers Are Great, But Have Limitations
Slicers are a great way to provide interactivity to your Excel spreadsheets. They allow you to ditch the need for drop-down lists and instead give your users the ease of simply selecting a button to filter/change the displayed data.
Unfortunately, Microsoft has left these handy little buttons very simple in their functionality which has hindered some of us creative folks from pushing them past their limits. One of these limitations is manipulating the order in which the Slicer buttons are organized.
When you are creating Slicer buttons you typically want to keep your most used buttons on the top row. This is because most people are going to scan through the buttons similar to how they read a book and you don’t want to waste your user’s time by placing the most popular buttons somewhere in the middle of the sea of buttons.
Slicers only allow you to organize your buttons in alphabetical order (ascending or descending). Since you can’t always depend on your most popular buttons starting with the letter “A” this could pose a problem.
Real World Example With Quarters and Months
Let’s look at an example situation where we want to create Slicer buttons that give our users the ability to filter on a particular month or quarter. In this scenario, the quartes are going to be the more popular way at looking at the data as the team that uses this file only reports results every 3 months.
With quarters being classified as the “popular” buttons, we will definitely want to make these show up on the top row. While you can force them to the top row sorting Z to A, they read backwards which is most likely not what you will be wanting.
One solution you could implement is to number your buttons, however this could potentially mess up other parts of your spreadsheet (ie lookup formulas) or cause confusion (ie “05. Jan” -> Isn’t January the first month?). So this might not be a viable solution for your situation.
My Solution: Use A Custom List
In my proposed solution, I will be using Custom Lists to tell the slicers the specific order I would like the buttons to be in.
You may not realize this, but you use Custom Lists all the time! Have you ever dragged a cell with the value “January” across to list out all the months? The reason this work, is because Excel comes with a pre-built in list that lists out all the months when certain values are dragged.
Below are the 4 lists that exist by default with Excel:
So, let’s try and create a Custom List based on how we want the buttons to read. In the blue table below, I have listed out the order of how I want the buttons to be laid out reading left to right, moving downward. You can see that I would like each quarter going across in the top row and then the 3 months that make up that quarter underneath.
Now that we have some cells with the precise order we want, let’s go ahead and carry out the following steps to build our Custom List:
- Select the File tab
- Click Options
- Pick the Advanced section in the left-side pane
- Scroll all the way down to the bottom of the options list
- Click the Edit Custom Lists button
Once you are viewing the Custom Lists dialog window, you have a couple of options to create your list. You can either manually type out your list in the text field or you can use the Arrow button to select cells with the list values in them. In this example, I have already listed out the values on my spreadsheet, so there is no point in re-typing them manually. So let’s take the following steps:
- Select the Arrow button and highlight the cell range listing the values you would like to add to your Custom List
- Click the Import button and you should now see your values populate the List Entries listbox
- Click the OK button to confirm your new Custom List
Now that we have created our custom list, we can right-click on the Slicer object and sort from A to Z to get our desired result!
Things To Note Afterwards
Custom Lists are an Application-Level setting! This means if you open another file and drag values residing in a Custom List, Excel is going to use the next list value and not just duplicate the original value. So don’t be confused when this happens! That’s why I recommend deleting the Custom List unless it is a list you plan on using in your spreadsheets a lot.
If you re-sort, you could lose your order! If you happen to take my advice and immediately delete the Custom List you used to reorder your buttons, be aware that if you try to sort the buttons in the future, the slicer buttons will revert back to an alphabetical order.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![How To Prevent Copy/Paste Merged Cell Errors [Solved!]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/12/PreventCopyMergedCellErrors.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.