• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Sorting Excel Date Slicers

You are here: Home / Excel PivotTables / Sorting Excel Date Slicers
Sorting Excel Date Slicers
July 29, 2014 by Mynda Treacy

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:

Olympic Dashboard

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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.

Slicer Example

A quick right-click to the Slicer > Slicer Settings reveals we have 3 options for the sort order:

Slicer Settings

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):

  1. Insert a PivotTable (Tip: you can use Slicers to Filter Excel 2013 Tables too!)
  2. Select any cell in the PivotTable
  3. Insert Tab of the Ribbon > Slicers
  4. Choose the field you want as a Slicer from the Insert Slicers dialog box (note: you can select multiple items from this list):

Insert a Slicer

In Excel 2013 you can also right-click the field in the PivotTable/PivotChart field list and select ‘Add as Slicer’:

Insert a Slicer Excel 2013

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 Options

Excel 2007: Windows button > Excel Options > Popular > under the 'Top Options for Working with Excel' choose ‘Edit Custom Lists:

Excel 2007 Options

In the Custom Lists dialog box you can either:

  1. Type in your list with commas to separate each item, then click the ‘Add’ button, or
  2. Import a list from cells in your workbook by clicking on the RefEdit icon and selecting the cells containing your list.

Excel Custom Lists

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:

  1. 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.
  2. The Custom List must be added to any computer which will be opening and working on the file.
  3. 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:

Excel PivotTable Source Data

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:

PivotTable Slicer Sort Column

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:

Excel Format Cells

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:

Insert a Slicer

Now our Slicer dates are sorted correctly.

Sort Slicer dates by day

This same approach will work for years and months too. Click here for detailed instructions.

Sort Slicer dates by month

Thanks

A big thanks to Shane Devenshire for kindly sharing the data, for which I used to create the Olympic Dashboards.

Want More?

Click here to learn how to use Slicers and build interactive dashboards like my Olympics one above.  
Sorting Excel Date Slicers

More Charts Posts

burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.
using jitter to avoid over plotting

Using Jitter to Avoid Over Plotting in Power BI

Plotting data that has one variable where values are similar, can result in points that are plotted over each other. Use jitter to avoid this overplotting.
Excel custom chart labels

Excel Custom Chart Labels

Create dynamic Excel custom chart labels with this category axis hijack trick
Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

Charting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess

4 Charts – Same Data. Which do you think is best?

4 Charts – Same Data. Which do you think is best?

More Slicers Posts

Symbols in Excel Slicers

Symbols in Excel Slicers is a fun way to make your reports more interesting. Here are some tricks to getting wingdings and symbols to display in Slicers.
Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts

Slicer Controlled Interactive Excel Charts can open up a huge range of possibilities and the best part is you can use Slicers to control regular charts too.
slicer selection in formula

Use Excel Slicer Selection in Formulas

How to use Excel Slicer selection in formulas to control charts and tables. Download the workbook and follow along.
Excel Slicer Trick

Excel Slicer Trick

In this Excel Slicer Trick I show you how to use a Slicer to display a drilled down view of your data in a PivotChart. Download the file and follow along.
Excel Slicers for Fiscal Years

Excel Slicers for Fiscal Years

Inseting Excel Slicers for Fiscal Years is easy but requires a bit of DIY date classification.
Excel Slicer rolling periods

Excel Slicers for Rolling Periods

Creating Excel Slicers for rolling periods is easy with this IF formula. Download the workbook and watch the video.
Single Slicer for Year and Month

Create a Single Excel Slicer for Year and Month

How to create a single Excel Slicer for Year and Month fields. Includes written instructions, video and workbook download.
Excel Slicers

I’ll Have a Slicer That!

How to insert and use Excel Slicers. Includes video and step by step written instructions. Use Excel Slicers to create interactive Charts and PivotTables.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: charts, slicers
Previous Post:Fix Excel Chart Axis with a Ghost Series
Next Post:Excel Formula to Spread Income or Costs Over MonthsExcel Formula to Spread Income or Costs Over Months

Reader Interactions

Comments

  1. Mark

    March 16, 2020 at 4:28 am

    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 🙁

    Reply
    • Mynda Treacy

      March 16, 2020 at 9:06 am

      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

      Reply
      • Mark

        March 16, 2020 at 7:00 pm

        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

        Reply
        • Mynda Treacy

          March 16, 2020 at 7:12 pm

          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

          Reply
    • Catalin Bombea

      March 16, 2020 at 1:59 pm

      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.

      Reply
  2. Mark

    March 1, 2020 at 12:42 am

    Hello,
    How can You create the bar on the top (overview, Team view, sport view)?

    Reply
    • Mynda Treacy

      March 1, 2020 at 1:41 pm

      Hi Mark,

      These are just shapes with hyperlinks.

      Mynda

      Reply
  3. jp

    October 11, 2018 at 6:52 pm

    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

    Reply
    • Mynda Treacy

      October 11, 2018 at 9:36 pm

      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

      Reply
  4. Dave

    May 4, 2018 at 9:03 am

    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.

    Reply
    • Mynda Treacy

      May 4, 2018 at 10:26 am

      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

      Reply
  5. yahel

    February 16, 2018 at 1:20 am

    i don’t have the option of “use custom list when sorting”.
    why is that ? what to do ?

    Reply
    • Mynda Treacy

      February 16, 2018 at 11:16 am

      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

      Reply
  6. Alex

    January 11, 2018 at 10:17 am

    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.

    Reply
    • Catalin Bombea

      January 11, 2018 at 1:49 pm

      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.

      Reply
      • Alex

        January 11, 2018 at 3:36 pm

        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!

        Reply
        • Catalin Bombea

          January 11, 2018 at 4:20 pm

          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

          Reply
  7. Veronica

    March 30, 2017 at 1:41 pm

    Finally I have my slicers in order!
    Love the sneaky workaround.
    Thanks

    Reply
    • Mynda Treacy

      March 30, 2017 at 1:59 pm

      Wonderful! Glad you found it useful 🙂

      Reply
  8. Tim

    January 29, 2017 at 2:18 am

    Mynda, Is the Custom Sort Slicer Setting not applicable to Powerpivot Pivot Tables?

    Reply
    • Mynda Treacy

      January 29, 2017 at 9:22 am

      In Power Pivot we have other ways to sort Slicers, namely ‘Sort by’.

      Reply
  9. Patrick

    December 16, 2016 at 1:25 am

    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?

    Reply
    • Mynda Treacy

      December 16, 2016 at 7:37 am

      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

      Reply
      • Patrick

        December 16, 2016 at 5:40 pm

        Hi Mynda

        that worked fine, thanks for helping!

        Have a nice day.

        Reply
  10. Jay

    October 20, 2016 at 7:35 pm

    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

    Reply
    • Mynda Treacy

      October 21, 2016 at 10:09 am

      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

      Reply
  11. John N

    August 10, 2016 at 2:27 am

    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.

    Reply
    • Mynda Treacy

      August 10, 2016 at 10:10 am

      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

      Reply
  12. ajit K

    May 26, 2016 at 6:54 pm

    Hi,

    Need enter manual value in slicer.

    Help on it.

    Reply
    • Mynda Treacy

      May 26, 2016 at 8:25 pm

      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

      Reply
      • ajit K

        May 27, 2016 at 3:42 pm

        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.

        Reply
        • Mynda Treacy

          May 27, 2016 at 5:25 pm

          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.

          Reply
  13. swapnil

    February 27, 2016 at 10:04 pm

    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

    Reply
    • Catalin Bombea

      February 27, 2016 at 10:15 pm

      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

      Reply
  14. Tim Hoogenboom

    February 26, 2016 at 6:39 am

    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?

    Reply
    • Mynda Treacy

      February 26, 2016 at 8:36 am

      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

      Reply
  15. Angie Johnson

    December 12, 2015 at 1:35 am

    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

    Reply
    • Mynda Treacy

      December 12, 2015 at 8:31 am

      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

      Reply
      • Laure-Emmanuelle Peyret

        April 15, 2016 at 1:14 am

        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

        Reply
        • Mynda Treacy

          April 15, 2016 at 11:07 am

          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

          Reply
          • Bill

            October 31, 2016 at 1:39 am

            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

            October 31, 2016 at 12:28 pm

            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

  16. Wanda Hagen

    October 28, 2015 at 1:19 am

    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!

    Reply
    • Mynda Treacy

      October 28, 2015 at 10:57 am

      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

      Reply
      • Wanda Hagen

        October 28, 2015 at 10:37 pm

        Thanks! I found it!

        Reply
  17. Will Klatt

    June 27, 2015 at 4:05 am

    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.

    Reply
    • Mynda Treacy

      June 27, 2015 at 2:51 pm

      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

      Reply
  18. Eric F.

    June 1, 2015 at 11:28 pm

    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.

    Reply
    • Mynda Treacy

      June 2, 2015 at 8:59 am

      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

      Reply
  19. vilas

    May 15, 2015 at 1:41 pm

    You are a gem ! Works like magic

    Reply
    • Mynda Treacy

      May 15, 2015 at 1:48 pm

      Thanks, Vilas 🙂

      Reply
  20. Cathy

    August 4, 2014 at 12:07 pm

    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?

    Reply
    • Mynda Treacy

      August 4, 2014 at 8:03 pm

      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

      Reply
  21. Jef

    July 30, 2014 at 3:50 pm

    Great post. It worked perfectly. Thanks for sharing.

    Reply
    • Mynda Treacy

      July 30, 2014 at 6:26 pm

      You’re welcome, Jef 🙂

      Reply
  22. MF

    July 30, 2014 at 3:45 pm

    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.”

    Reply
    • Mynda Treacy

      July 30, 2014 at 6:27 pm

      Well spotted MF. Thanks for letting me know. It’s all fixed now.

      Cheers

      Mynda

      Reply
  23. GJ Case

    July 29, 2014 at 11:56 pm

    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.

    Reply
    • Mynda Treacy

      July 30, 2014 at 8:32 am

      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

      Reply
  24. GJ Case

    July 29, 2014 at 11:26 pm

    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

    Reply
    • Mynda Treacy

      July 30, 2014 at 8:31 am

      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

      Reply
  25. Maxime Manuel

    July 29, 2014 at 11:18 pm

    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.

    Reply
    • Philip Treacy

      July 30, 2014 at 10:42 am

      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

      Reply
      • Maxime Manuel

        July 30, 2014 at 10:10 pm

        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.

        Reply
        • Philip Treacy

          July 31, 2014 at 2:23 pm

          hmmm. odd. what browser are you using? Can you try another browser? Chrome, Firefox or Internet Explorer.

          Reply
  26. Maxime Manuel

    July 29, 2014 at 11:13 pm

    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!

    Reply
    • Mynda Treacy

      July 30, 2014 at 8:37 am

      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

      Reply
      • Maxime Manuel

        July 30, 2014 at 10:08 pm

        Horizontal slicers worked!
        Thank you for the tutorial. Have a wonderful day.

        Reply
        • Mynda Treacy

          July 31, 2014 at 8:11 am

          You’re welcome, Maxime 🙂 You have a wonderful day too!

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.