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.
Please Share
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.
Thanks
A big thanks to Shane Devenshire for kindly sharing the data, for which I used to create the Olympic Dashboards.
Veronica says
Finally I have my slicers in order!
Love the sneaky workaround.
Thanks
Mynda Treacy says
Wonderful! Glad you found it useful 🙂
Tim says
Mynda, Is the Custom Sort Slicer Setting not applicable to Powerpivot Pivot Tables?
Mynda Treacy says
In Power Pivot we have other ways to sort Slicers, namely ‘Sort by’.
Patrick says
Hello
very nice post, thank you for sharing.
I’m using Excel 2013 and slicers. The data source is in percent but the slicer is showing the numeric values. So even though it says 29,1% in the column in the pivot table, the slicer shows 0,291. Some slicer values have up to a dozen decimals and it does not look very good. It seems formating the slicer is not possible, using the ctrl + 1 command. Any workaround?
Mynda Treacy says
Hi Patrick,
You’ll need to add a column to your source data that converts the percentages to text. You’ll then use that column for your Slicer, but the actual percent column in your PivotTable.
If you get stuck please post your question on our Excel Forum and include a sample Excel workbook so we can show you.
Mynda
Patrick says
Hi Mynda
that worked fine, thanks for helping!
Have a nice day.
Jay says
Hi, I was wondering if you could help, when I group my days in the pivot table, it shifts the dates by 1 days. i.e. if I have 1st Jan,1st Jan, 2nd Jan, 2nd Jan, 2nd Jan, 3rd Jan and then I go to group it – it changes the dates in pivot table to 2nd Jan, 3rd Jan – 1st jan disappears. Any ideas?
Thank you
Mynda Treacy says
Hi Jay,
I’ve not heard of that before. Can you please post your question on our Excel forum where you can upload a sample file so we can take a look.
Thanks,
Mynda
John N says
A nice solution IF the person designing the pivot table will be the only one using it, and only using it on one computer. The fact that the custom list needs to be set up on any computer using the table makes this unusable for my purposes. I typically distribute these kinds of tables to a number of faculty members.Would love to see Microsoft address this with an intuitive, drag-and-drop solution.
Mynda Treacy says
Hi John,
I guess the ‘nice solution’ you are referring to is the Custom Lists. I agree, these aren’t ideal, that’s why I recommend Plan B in my post above.
Mynda
ajit K says
Hi,
Need enter manual value in slicer.
Help on it.
Mynda Treacy says
Hi Ajit,
The only way to get an item appear in your Slicer is to have it in your PivotTable source data. You could always create a dummy PivotTable for the purpose of your Slicer.
Mynda
ajit K says
I have created Pivot table and I want to use slicer in my baseboard.
But in that slice more than 800000 entry. It is very difficult to find single value.
Please help me.
thanks
Ajit K.
8483842013.
Mynda Treacy says
Sounds like you’re using Slicers for the wrong type of data. If you have 800,000 unique items then you probably should be grouping them somehow so you can make sense of the data.
swapnil says
HOW TO MAKE SCLICER FOR DAY WISE FOR eg-
day 1
day2
day-3
for every day i have different price so i want to make upto 50 days, day wise price sclicer.
kindly help me in this
Catalin Bombea says
Hi,
You have to add a new column to the data table, and use a simple formula like this: =DAY(Table1[Date])
Refresh the pivot table, and you will be able to add this field to a slicer.
Cheers,
Catalin
Tim Hoogenboom says
I report sales to various Cities. Alphabetical sorts are fine, but my Pivot Chart is sorted by volume sold to the city and I’d like the slicer to be sorted in the same order, ie The city with the highest volume, which is the first bar on the chart, should be on top. As different slicer date rages are selected, the sort order changes. I don’t see a way to use the sneaky workaround because of this. Do you?
Mynda Treacy says
Hi Tim,
Nope, there’s no way to dynamically sort Slicers. The best you can do is use a custom list to sort the Slicer the way you want but that won’t update upon changes in the items selected.
Kind regards,
Mynda
Angie Johnson says
Hi Mynda, When I open up my slicer settings I do not have the “Use Custom Lists when sorting” option. I’m working in Excel 2013. Is there an add-on to have that option? Right now I only have “Data source order” above the ascending and descending options. I appreciate your assistance with this. Thank you, Angie
Mynda Treacy says
Hi Angie,
That’s odd. There’s no add-on but you might have an old version of Excel 2013? You could try upgrading to the latest build for 2013.
Mynda
Laure-Emmanuelle Peyret says
Hello,
Indeed, actually I have the same issue with Excel 2016. I don’t have the “Use Custom Lists when sorting” tick box you mention. Only Sort A to Z, Z to A and “Data source order” which I haven’t figured out yet… I’ll keep digging but if anyone has any ideas?
Thanks!
Laure-Emmanuelle
Mynda Treacy says
Hi Laure-Emmanuelle,
I have the check box to ‘use custom lists when sorting’ in my version of Excel 2016. Are you using a Mac?
Mynda
Bill says
I have the same problem and I am using Windows 10, Excel 2016. The checkbox for custom lists is not there. Just the option buttons. Interestingly enough, when I open Mynda’s file the option box is there, but not on my own
Mynda Treacy says
Hi Bill,
That’s odd. If you right-click the PivotTable > Options > Totals & Filters tab, do you have a ‘Use Custom Lists when sorting’ checkbox available under the ‘Sorting’ heading?
Mynda
Wanda Hagen says
Great article, and I can’t wait to try this with some of my own data!
I’m a great fan of the Olympics, and I’m intrigued by the dashboard you show. Is there a place to get a copy of it? I would love to be able to access it as I watch the 2016 Rio Olympics!
Mynda Treacy says
Hi Wanda,
Thanks for your kind words. I’m delighted you like my dashboard. You can get a copy of it in my Excel Dashboard Course:
https://www.myonlinetraininghub.com/excel-dashboard-course
Alternatively you can ask Shane Devenshire for a copy of the raw data.
Kind regards,
Mynda
Wanda Hagen says
Thanks! I found it!
Will Klatt says
I think the real problem is that your dates are not actually dates; they are text. Apply the formula DATEVALUE() to your dates to convert them to a number which can be formatted as a short or long date. Then the dates will sort properly since they are in numerical order.
Mynda Treacy says
Hi Will,
The dates are real dates but the minute you group those dates in the PivotTable using the Group tool the PivotTable considers them text (and you cannot avoid this), so you must use one of the workarounds described above.
Mynda
Eric F. says
I know this is an older article, I used the workaround feature which works fine if you have only 1 entry for that specific date , if you have multiple dates and then try to group them it goes back to the original format, is there a workaround for this when you multiple entries for the same date ? Thanks in advance.
Mynda Treacy says
Hi Eric,
The solution is to add a column to your source data with the grouping in a text format and then use this field in your PivotTable report. Here is a tutorial that explains it in more detail:
https://www.myonlinetraininghub.com/create-a-single-excel-slicer-for-year-and-month
Kind regards,
Mynda
vilas says
You are a gem ! Works like magic
Mynda Treacy says
Thanks, Vilas 🙂
Cathy says
Thanks for this. What about sorting dates without slicers? I have data from July 2013 to July 2014 in the columns. And I want Excel to sort the columns in Financial Year order, July to June then July to June again?
Mynda Treacy says
Hi Cathy,
I’d probably be inclined to use the Custom List for two reasons:
1. since it’s something you would need to use on a regular basis the effort required to install it on all computers that would use it wouldn’t go to waste.
2. With the ‘Sneaky Workaround’ option you’d have to create a column of numeric values. e.g.
July would be 201401
August would be 201402
September would be 201403
And so on, just so you could sort the months in the order you want. This column could be hidden in your PivotTable report but since it’s not intuitive it may lead to confusion.
Kind regards,
Mynda
Jef says
Great post. It worked perfectly. Thanks for sharing.
Mynda Treacy says
You’re welcome, Jef 🙂
MF says
Hi Mynda,
Thanks for the tips.
I believe maybe there is a typo… should be “mmm-yy” be “mmm-dd”?
“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-yy.”
Mynda Treacy says
Well spotted MF. Thanks for letting me know. It’s all fixed now.
Cheers
Mynda
GJ Case says
Another note:
When I added the newly formatted date field, and added that slicer to the pivotchart, in order to get the pivotchart to update, I had to delete the Period slicer, otherwise it would not add the new data to the chart, even though it would add the dates to the slicer.
Mynda Treacy says
Hi Glenn,
I suspect the two Slicers were in conflict, so that would make sense that you have to delete one of them.
Cheers,
Mynda
GJ Case says
Mynda:
I found this to be an interesting article. However, for me, one big drawback is the fact that the custom list has to be updated if you add data.
So here’s a suggestion: Rather than add the Period sneaky workaround, why not just define a custom date format of yyyy-mm-dd and use that in the data table and for the slicer? This will sort properly, and it will update if you add more data to the table and refresh the Pivottables. The only downside is that there may be some confusion as to the date format, especially with international audiences, but if the custom Date header is something like “Date: YYYY-MM-DD” that would address that concern. (As a note, I find the format in your “Period” slicer to be confusing; I initially read that as month-year rather than as month-day, so the issue already exists to some extent…)
Thanks for the stimulating discussion.
—Glenn
Mynda Treacy says
Hi Glenn,
I agree with the drawback for the custom list which I why I proposed the Sneaky Workaround.
If you use Date column (A) in your PivotTable/Chart and group those dates, and then use that same field for your Slicer, you end up with the dates not sorted correctly.
This is why I suggested the ‘Sneaky Workaround’; that is to add an extra column of dates (C), which are formatted as you wish (mmm-dd or yyyy-mm-dd etc.), and then you use this column for your Slicer. I believe this is the same as your proposal?
I agree you could change the heading of column C to make the date format clearer, or you can simply give the Slicer a different name in the settings.
Kind regards,
Mynda
Maxime Manuel says
Just a page report: I noticed that the last section saying “Share This” is not activated when I am clicking it. For example if I click Pinterest it does not pop-up.
Maybe I am the one who is not using it well. That was just a notice. You can delete this comment when you are done reading it.
Good job Mynda and Phil.
Philip Treacy says
Hi Maxime,
Not sure what is happening for you. When I try the Pinterest in the ‘Share This’ section, it works for me. I tried all the social sharing buttons and they all work for me.
Have you got a pop-up blocker turned on? Each of the social sharing buttons tries to open a pop-up window.
Regards
Phil
Maxime Manuel says
It is not working Phil. I do not know what was wrong with it yesterday. Maybe something on my end.
Thank you for your reply.
Philip Treacy says
hmmm. odd. what browser are you using? Can you try another browser? Chrome, Firefox or Internet Explorer.
Maxime Manuel says
I just have the Excel 2013 installed and Slicers was the first thing that I know I would learn. This article is very helpful. Many thanks to MyOnlineTrainingHub.com.
Mynda or Phil, I would like another article showing how to have the Slicers listed horizontally instead of vertically.
Many thanks!
Mynda Treacy says
Hi Maxime,
Glad you liked Slicers too. I realised as I was writing this that I hadn’t written about them before so I will plan some more tutorials 🙂
In the meantime you can make Slicers horizontal by changing the number of columns in them. Go to the Slicer Tools: Options tab of the ribbon, which is available when the Slicer is selected, and in the Buttons group you can increase the number of columns. Then drag the pull handles of the Slicer to make it horizontal.
Kind regards,
Mynda
Maxime Manuel says
Horizontal slicers worked!
Thank you for the tutorial. Have a wonderful day.
Mynda Treacy says
You’re welcome, Maxime 🙂 You have a wonderful day too!