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.
Enter your email address below to download the sample workbook.
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.
Thanks
A big thanks to Shane Devenshire for kindly sharing the data, for which I used to create the Olympic Dashboards.
Fix Excel Chart Axis with a Ghost Series
Mark
hello,
thanks for your description.
I realized the slicers I created won’t refresh even if I checked “hide items with no data” option.
In fact “(blank)” field is still in, even if I replaced all the blank cells with data.
I read somewhere to stop Excel from showing deleted items in a Slicer, I should select the Slicer and then click Slicer Tools > Options > Slicer > Slicer Settings. I should then be able to uncheck Show items deleted from the data source and click OK.
My problem is I cannot find that option in my Slicers settings !! I find only 3 options: 1 hide items with no data; 2 Visually indicate items with no data; 3 Show items with no data last.
Another strange thing is that if I go to PivotTable Options >Data ,
1 “Save source data with file” option is unchecked and greyed out;
2 “Retain items deleted from the data source” is set on Automatic and the field is greyed out.
I tried to create new worksheet, new pivot tables and slicers and everything is the same.
I use Office365 proPlus.
Any suggestions? I spent a lot of hours without finding any solution 🙁
Mynda Treacy
Hi Mark,
It sounds like you may have added your data to the data model/Power Pivot? Please post your question and Excel file on our forum where we can help you further.
Mynda
Mark
Right!
I created multipled pivots table from multiple tables. That’s why I selected the field “add this data to the data model”.
I made a test.
I just created new table with few rows and columns. Then I created new pivot table without selecting “add this data to the data model”. In this way I verified I was able to select “none” in “Retain items deleted from the data source section”.
Then I created a slicer. Even in this case I was able to check “show items deleted from the data source”.
In my case, how can I refresh all the slicers?
thanks
Mynda Treacy
Hi Mark,
Please post your question and file on our forum. It’s not clear what you mean by ‘refresh all the slicers’? They should all be in sync if they are all connected to the same PivotTables.
Mynda
Catalin Bombea
Hi Mark,
Can we see your file? If possible, please remove sensitive information from the file and upload it on our forum (create a new topic after sign-up).
It might be a Power Pivot, not just a regular pivot table.
Mark
Hello,
How can You create the bar on the top (overview, Team view, sport view)?
Mynda Treacy
Hi Mark,
These are just shapes with hyperlinks.
Mynda
jp
Hi,
When forwarding a excel document to other people then the custom list will not appear anymore.
Is there a solution to that?
Thank you
Mynda Treacy
Hi JP,
No, unfortunately you need to create the custom list on each PC. That’s why I tend to use the other option.
Mynda
Dave
Plan B didn’t work for me. My slicer insists on using the dd-mmm format even though the source column has mmm-yy format and still gets the order wrong. Yes I refreshed the pivot table. Thankfully the pivot table and pivot chart have the dates in the correct order despite the stupidity of the slicer. What you didn’t make clear was how the data in column “Period” was entered. Did you use the same data from the first column just changed the format or did you enter the data in the third column as mmm-dd. By the way, my slicer also has tons of other dates (not highlighted) which are not connected to any data when I click on them.
Mynda Treacy
Hi Dave,
Did you insert a new Slicer using the new mmm-yy column?
I explain how I created the extra column in the post above starting with this sentence: “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….”
Also, if you download the Excel file you can see how I added the column.
Greyed out buttons in your Slicer represent data that is not present in the PivotTable at the current filter level. You can turn this off in the Slicer settings (right-click the Slicer).
If you have no filters applied, but still have greyed out buttons then they represent data you used to have in your PivotTable source data that is no longer be there. To remove that data, right-click the PivotTable > Options > Data tab > set ‘Number of items to retain per field’ to ‘None’.
Let me know if you still have problems.
Mynda
yahel
i don’t have the option of “use custom list when sorting”.
why is that ? what to do ?
Mynda Treacy
Hi Yahel,
If you check the box ‘Load to Data Model’ when creating your PivotTable then your data is actually stored in Power Pivot and your PivotTables are Power Pivot PivotTables. Slicers for Power Pivot do not have the ability to sort using a custom list.
For Power Pivot you need to add a numeric column to the dimension table containing your Slicer items and use the ‘Sort by’ tool in Power Pivot to sort the Slicer items column by your numeric column. If you get stuck, please post your question and sample Excel file on our Forum where we can help you further.
Mynda
Alex
This is a great article, Mynda! Do you know if there is any way to work with slicers and alphanumeric labels? I am building a dashboard of several different numbered promotions and need both the number and promotion name. The slicer obviously tries to sort as if the numbers are text (i.e. 1, 10, 2, etc.), which makes using them difficult. Any idea of a workaround? I’m not finding anything on the internet otherwise. I’m also using Excel 2010 btw.
Catalin Bombea
Hi Alex,
Have you tried to build a custom list with the correct order of your values?
Mixed type values will indeed be sorted alphabetically, in this case you better put promotion name before the numbers, it will sort better.
Alex
Thanks, Catalin! I considered a custom list but know those have to be created on each computer viewing the file, and I don’t understand VBA well enough to auto-apply them. Unfortunately, I will be emailing this file to multiple parties, so it seems like this would not be practical. I appreciate your suggestions, though!
Catalin Bombea
It’s a simple code:
Sub AddCustomList()
Dim n As Byte, ArrList As Variant
ArrList = Array("983711", "TM 2516", "980261", "TM5660", "78011", "983712", "TM2517", "980263", "TM5661", "78012", "EF810028", "6060", "XSR", "985221", "TM2452", "15AL1630", "HD21A", "H25")
On Error Resume Next
n = Application.GetCustomListNum(ArrList)
On Error GoTo 0
If n = 0 Then
Application.AddCustomList ArrList
n = Application.CustomListCount
End If
End Sub
Veronica
Finally I have my slicers in order!
Love the sneaky workaround.
Thanks
Mynda Treacy
Wonderful! Glad you found it useful 🙂
Tim
Mynda, Is the Custom Sort Slicer Setting not applicable to Powerpivot Pivot Tables?
Mynda Treacy
In Power Pivot we have other ways to sort Slicers, namely ‘Sort by’.
Patrick
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
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
Hi Mynda
that worked fine, thanks for helping!
Have a nice day.
Jay
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
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
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
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
Hi,
Need enter manual value in slicer.
Help on it.
Mynda Treacy
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Thanks! I found it!
Will Klatt
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
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.
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
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
You are a gem ! Works like magic
Mynda Treacy
Thanks, Vilas 🙂
Cathy
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
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
Great post. It worked perfectly. Thanks for sharing.
Mynda Treacy
You’re welcome, Jef 🙂
MF
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
Well spotted MF. Thanks for letting me know. It’s all fixed now.
Cheers
Mynda
GJ Case
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
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
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
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
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
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
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
hmmm. odd. what browser are you using? Can you try another browser? Chrome, Firefox or Internet Explorer.
Maxime Manuel
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
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
Horizontal slicers worked!
Thank you for the tutorial. Have a wonderful day.
Mynda Treacy
You’re welcome, Maxime 🙂 You have a wonderful day too!