Slicers are a fantastic tool to get a filtered view of your data and visuals.
But by default, they get sorted alphabetically or follow the calendar year if your parameter contains months.
What if you wanted them sorted in a custom order? Maybe you want to show fiscal years, a Sunday to Monday work week or just any specific order your whimsical boss tells you to?
No need to panic, you can sort slicers any which way you want, just follow these nifty tricks!
Watch the Video
Enter your email address below to download the example files.
Exploring the Sample Data
Here we have a sample dataset that shows the Sales, of 6 products on any particular date between July 2022 and June 2023, segregated by Segments and Country.
With this data, we can easily calculate the monthly, quarterly, and yearly sales during the financial year, or answer questions like “What are the total sales from France?” or “How much Sales does the Government Segment bring?” And so on.
The Default Slicer Sorting Dilemma
Suppose we want to look at monthly sales during the financial year starting from July 1, 2022, to June 30, 2023.
Let’s follow the below steps:
- Insert a pivot table with years and months in rows and sales in columns
- Insert a pivot chart to visualize the data
- Insert a slicer for this pivot table
While the pivot table and the chart sort correctly from July to June, by default, the slicer sorts from January to December.
Why does this happen?
The reason why slicers sort from January to December is that we can only add one field at a time in a slicer. While the pivot table incorporates both years and months to get the right sorting order, the slicer can only pick months because our data set includes grouped dates in mm-dd-yyyy format.
Why is this a problem?
Fiscal Month Custom Sorting
Fiscal year is the standard accounting and taxation period used to calculate various decision-making performance metrics, such as profitability and MoM, QoQ, or YoY changes, for any business. Therefore, most businesses prefer having their data and visuals, including slicers, sorted according to the fiscal year.
However, in most countries fiscal year and calendar year have different start and end dates. For example, Australia follows a July to June fiscal year, The US follows an October to September fiscal year and India follows an April to March fiscal year.
Therefore, our slicers need to be sorted accordingly. We can achieve this using several methods, the simplest of which is the Custom Lists method.
Custom Lists method
To add a custom list, follow the below steps:
- Click on the File tab
- Go to Options
- Select Advanced tab
- Scroll to the General heading towards the bottom
- Click on the Edit Custom Lists button
Here you can add your own custom sorting order.
There are two methods of doing it:
- Manually type your sorting order, by comma separating each value
- Importing your list from a cell range in your Excel file
Once you have added the custom list, press ok and refresh your Pivot Tables. The slicer should sort in the required custom order.
While the fiscal year is just one application of custom-sorted slicers, the same procedure can be used for varying work weeks. For example, a week starting from Sunday to Monday, instead of the standard Monday to Sunday format.
Limitations of the Custom Lists method
Although this is a simple method, it has several limitations:
- The list doesn't automatically grow as you add more data: While months are known beforehand you might need to use a different sorting parameter, such as the country of sale. Every time you have a new country added to the dataset, you will have to add it to the custom list.
- Limited items are allowed: You can only add up to 2,000 items to your custom list
- Non-Transferable: Every time you transfer the file to a new computer, you will have to add the custom list to that computer for the correct sorting order.
- Format limitations: Custom lists can only contain text or alphanumeric data. If you want a custom list containing only numbers you will need to first create the list of numbers and then format it as text.
Sort Single Slicer for Month-Year
As we pointed out above, slicers can only have one field at a time. And with all the limitations of the Custom Lists method, it is not always the best option.
If you want to avoid the Custom Lists method, and you still want to have grouped dates in your dataset, you will need to have two slicers in place – one for the year and one for the month.
Even so, you will have to look at individual years if your fiscal year is split across two years. Which is not only cumbersome, it doesn’t give the insights for the entire year.
But hold on! There can be a sneaky workaround for incorporating both year and month in the same slicer!
Here are the steps to follow:
- Create a helper column to your dataset, call it Period
- Copy the grouped dates to this column
- Select the Period column and Press CTRL+1 to open the Format Cells dialog box
- Choose Custom from the Category list
- Enter the mmm-yy format in the ‘Type’ field
This will format the period column dates to show months and years in a single column. You can use this new column in the slicers!
Now, why is this method sneaky, you ask? It's because your pivot table and chart don't get affected. They still use the date column but your slicers are sorted in the Fiscal Year sorting order!
If you want your data to show months and days instead of months and years, you can format it as mmm-dd to get the desired results too.
Limitations of Sneaky Workaround
While this is a much simpler method compared to Custom Lists, it too has its limitations. When you work with larger data sets, the helper column causes your file to become very heavy. This could lead to longer file opening time or longer load time for visuals.
To avoid such issues, you can use the more advanced Data Model Method to sort your slicers, as explained below. Using this method, you can sort in almost any order you want. We will cover 2 such examples – Fiscal Year Sorting & Segment Sorting.
Sort Date Slicers - Data Model (Date Table)
As explained before, most businesses want the sorting to be in Fiscal Year order. You can achieve it by using the data model method. Here are the steps:
- Go to Insert tab
- Insert Pivot Table but before pressing ok on the dialog box, checkmark the "Add this to the Data Model" option
- Press ok
- Now go to the Data tab
- Select the Manage Data Option
A new window will open showing the data view of your data.
From here, follow the below steps:
- Go to the Design tab
- Click on Date Table
This will create a new table called the "Calendar" table. It automatically analyses the smallest and the largest dates in your data set and creates a relevant table. This table automatically contains the MMM-YYYY column.
However, if you use this in your Pivot Tables, it will sort alphabetically. Therefore, you need to add a column that concatenates the year and month fields in a YYYY-MM format. This way it will automatically sort in the correct order. To do that, click on the first cell of the add column and in the formula bar type the below formula.
This will give you the desired format for your slicers. You can also rename this column as Year-Month.
Tip: If you ever need to add dates to the calendar table, you can do so via the Design tab > Date Table > Update Range:
Next, you need to connect this calendar table to your data table. For that follow the below steps:
- Got to the Home tab in the Data Model
- Click on Diagram View to see both data and calendar tables
- Drag the date from the Calendar table to the data table to create a connection between the two tables. Note: Don’t make the reverse connection. The Calendar table should filter the Data table, not the other way round. The relationship should have the arrow on the connector line pointing towards the data table.
- Also, to avoid using the date field from the data table in your PivotTables, right-click on it and click on "Hide from Client tools."
- Save the Data Model and close it.
Now create your pivot table taking the Date Hierarchy from the Calendar table, and Sales from the Data Table.
Insert a Slicer using the Year-Month field and it will be sorted in the Fiscal Year order because the data starts in July 2022.
Advantages of the Data Model Method:
While it takes time to get used to this method it has several advantages:
- It works best with large datasets.
- You don't need to recreate it on every computer you open the file.
Segment Sorting: Data Model
Suppose your boss wants you to sort the sales Segment-wise, not fiscal year-wise and you don't want to use the Custom Lists method because you have too many segments to work with. You can use the Data model Method for this also.
For example, let’s say we want the Segment order to be: Government, Enterprise, Channel Partners, Midmarket, and Small Business respectively.
Here are the steps to sort the slicers in the desired order:
- Create a new table in the Excel file with 2 columns Segment & Segment Sort Order
- Populate the table with unique Segments and the sort order you want.
Next, we need to add this Segment table to the data model. We can follow the same steps as explained in the previous example.
Once the segment table is in the Data model, we need to set the sort order:
- Select the Segment column and on the Home tab, click on the Sort by button:
- In the dialog box, Sort Column should be Segment and Sort By column should be Segment Sort Order.
- Go to the Diagram view:
- Drag Segment field from the Segment table and drop it on the Segment field in the Data table, to create the connection. (Don’t make the reverse connection. Segment table should filter the Data table)
- Hide Segment in the Data table from client tools.
- Save the Data Model and close it.
- Add Segment to rows in your pivot table from the Segment table and Sales from the Data table.
- Insert a slicer for Segments and it will be sorted in your desired order!
Limitations of the Data Model Method:
While it makes sorting much easier, it has its own limitations:
- It is a little complicated to set up.
- Table connections need to be correct to make it work.
- At times you might need to use DAX formulas which are not so straightforward.
Which is Best?
Overall, all three methods are very useful when custom sorting slicers, but you need to decide which is the best method for you. Guidelines:
- Use the Custom Lists method when you have a small dataset, your sort-by list doesn't expand, and you don't need to view the file to different computers.
- Use the Sneaky Workaround method when you have a small dataset and you just want a single date slicer for year and month. This method travels with the file, so there’s no extra set up for different computers.
- Use the Data Model method when you have a large dataset, your sort-by list doesn’t expand, and you need to transfer the file to different computers.
You can use the Data Model method even if your sort-by list expands, by using Power Query to create an automatically updating sort-by list.
More Power Pivot: This has been a glimpse into the additional capabilities Power Pivot offers over regular PivotTables, including far more flexibility and capacity to work with large datasets spread across multiple tables.
The bonus of learning Power Pivot is it’s available in both Excel and Power BI, so the skills are transferrable. You can learn more about Power Pivot formulas in my Power Pivot and DAX course.
Slicer Formatting Hacks: Now that you’ve got your Slicers sorting the way you want, it’s time to learn some formatting hacks in the video below to make them small so they don’t take up so much space.