Slicers are an amazing new feature introduced in Excel 2010. I used them to build these 3 interactive dashboards on Olympic data from 1896 to 2012:
By the way, I teach you how to build dashboards like this in my Excel Dashboard course.
While Slicers are a great addition to Excel, they unfortunately have some shortcomings when it comes to dates and the sort order.
Let’s take the example below where I have summarised the Units Sold by day in a PivotChart, and added a Slicer to allow the user to filter which days they want to see. Notice the order of the Slicer dates.
A quick right-click to the Slicer > Slicer Settings reveals we have 3 options for the sort order:
Despite selecting ‘Ascending’, Excel seems to ignore the fact that my Slicer contains dates and just sorts the list as though it’s text! It’s annoying to say the least.
However, notice the last option in the Slicer Settings dialog box above is ‘Use Custom Lists when sorting’. One option is to set up a new custom list for the dates and use that.
Inserting a Slicer
Before we go on, here are some brief instructions on how to insert a Slicer (only available in Excel 2010 onwards):
- Insert a PivotTable (Tip: you can use Slicers to Filter Excel 2013 Tables too!)
- Select any cell in the PivotTable
- Insert Tab of the Ribbon > Slicers
- Choose the field you want as a Slicer from the Insert Slicers dialog box (note: you can select multiple items from this list):
In Excel 2013 you can also right-click the field in the PivotTable/PivotChart field list and select ‘Add as Slicer’:
Sort by Custom List
Ok, let's look at how we can use a Custom Lists to control the Slicer sort order. Thankfully they're easy to set up.
Excel 2010 and 2013: File tab > Options > Advanced > Scroll almost to the bottom and in the General section choose: Edit Custom Lists:
Excel 2007: Windows button > Excel Options > Popular > under the 'Top Options for Working with Excel' choose ‘Edit Custom Lists:
In the Custom Lists dialog box you can either:
- Type in your list with commas to separate each item, then click the ‘Add’ button, or
- Import a list from cells in your workbook by clicking on the RefEdit icon and selecting the cells containing your list.
Once you add your custom list you need to refresh the PivotTable to get your Slicer to update the sort order.
Downsides to Custom List Sorting
Sorting using Custom Lists is great however there are some limitations:
- The list doesn’t automatically grow as you add new dates. So you either add more dates up front to accommodate future growth, or update it when required. By the way, there’s a limit of 2000 items in your custom list.
- The Custom List must be added to any computer which will be opening and working on the file.
- A custom list can only contain text or text mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers and format them as text.
Plan B – Sneaky Workaround
Column A of my PivotTable source data contains dates, which are then grouped into days and months in my PivotTable, as you can see in column E below:
As we know, when we add a Slicer based on a grouped PivotTable date field it doesn’t sort correctly.
To get around this we simply add another column to our Source data which contains the same dates from column A, except this time we format them with a Custom Number format mmm-dd.
I’ve called my new column ‘Period’ as you can see below:
To format with a custom number format select the column of dates > CTRL+1 to open the Format Cells dialog box > Choose Custom from the Category list > enter your format in the ‘Type’ field:
Tip: make sure your dates are numbers formatted as mmm-dd and not text, otherwise they won’t sort correctly.
If your source data is in an Excel Table you can just refresh your PivotTable to add this colomn to your PivotTable source, which will also update the Field List with your new column.
Note: If your PivotTable source is a regular cell range e.g. A4:B124, then you will need to update it to A4:C124 so that the new column (C) can be added as a Slicer.
Now you can delete the original date Slicer (that isn't sorted properly), and insert a new one using the new Period field without even bringing it into your PivotTable/PivotChart. I call it the Sneaky Workaround because it's not actually part of your PivotTable/Chart, it's only used for the Slicer:
Now our Slicer dates are sorted correctly.
This same approach will work for years and months too. Click here for detailed instructions.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
A big thanks to Shane Devenshire for kindly sharing the data, for which I used to create the Olympic Dashboards.