In this tutorial we’re going to look at how we can create a single Excel Slicer for Year and Month, as opposed to the default of having the Year and Month in separate Slicers.
Let’s rewind a tad and look at how we got here in the first place. Below is an extract of my data; it’s a list of trading volumes spanning 13 months from April 2014 to April 2015.
Note: my dates are dd-mm-yy.
When I created the PivotTable I grouped my dates by Years and Months (right-click > Group):
This adds a new field to the Field list for the Years and the Date column now displays the Months in the PivotTable:
Download the Workbook
Enter your email address below to download the sample workbook.
Watch the Video Tutorial
Slicers for Years and Months
Here’s the rule, you can only have one field per Slicer. In other words, you can’t combine fields from the Field List into one Slicer. So as it stands if we wanted to add Slicers for Years and Months then we could, but it would look like this with two separate Slicers:
You might have noticed there are two things that aren’t ideal with the one Slicer per field method:
- The Years are separate to the Months which means the user has to click two Slicers to select the period they want and if they’re not careful and only select the month, for example April, they’ll get April values for 2014 and 2015 added together, and it’s highly unlikely they’ll actually want that.
- You might have noticed the Less Than and Greater Than buttons in the Slicer. They are automatically inserted when you group your dates, and there is no way to remove them. The best you can do is make the Slicer smaller so you can’t see them, but then you have a scroll bar in the Slicer (see below) and you know some nosey parker is going to scroll down and then get confused:
The Solution
Since Excel won’t allow you to combine fields into one Slicer the solution is to DIY in the source data. It’s easy enough; just add a new column (C) and insert a formula that only displays the Year-Month from the date column:
Now you can refresh your PivotTable and go ahead and add a Slicer for the new Field (tip: you don't have to put that field in your PivotTable, you can use it just for the Slicer):
Things to note:
The formula is a TEXT function that converts the date into text and formats it as yyyy-mm. If you just formatted the date as yyyy-mm with a custom number format (as opposed to converting it to text as well), then the PivotTable will ignore the formatting for the purpose of the Slicer and simply display a button for every unique data in the source data (but formatted as yyyy-mm so you can’t see the dd portion of the date):
- I intentionally put the year then the month number, as opposed to month name, or month then year, so that the Slicer sorted the dates in numerical order (even though it’s text). If you format the date as yyyy-mmm e.g. 2015-Apr then the Slicer will have the dates sorted alphabetically like this:
If you prefer your dates like this then the solution to sorting them correctly is to use a Custom List to fix the sort order.
Timelines (new in Excel 2013)
Now, if you’ve got Excel 2013 you might be thinking there’s another alternative, and you’d be right, kind of (I’ll rant about them in a moment). Timelines are a new type of Slicer in Excel 2013 specifically for dates.
You’ll find them on the Insert tab beside the Slicer button, or in the contextual PivotTable Tools: Analyze tab:
Or if you right click a date field in the Field List:
Timelines enable you to toggle between Years, Quarters, Months or Days:
They have a nice scroll bar and clicky-draggy thingy (technical term) to select which periods you want and it even tells you which periods you’ve selected.
And if your data spans entire years they’re almost brilliant, but… I have a list in order of annoyance:
- They’re stupid. I mean they lack intelligence to only present dates that are actually in your data. My data spans April 2014 to April 2015 but the Timeline shows me Jan 2014 to Dec 2015 and it lets me select those dates and end up with an empty PivotTable:
- In their default format they take up 7 rows! Sure you can turn off the header, scrollbar, time level and selection label and they trim down to 3.5 rows, but with that you lose functionality like the ability to toggle between Years, Quarters etc.:
- Like Slicers, you can format them to make the font smaller etc. but (unbelievably) this makes a miniscule difference to their height! Here’s one with 8pt font and it’s still the best part of 7 rows high and if I try to make it any smaller it just cuts off the scroll bar/header:
- In their default format they take up 7 rows! Sure you can turn off the header, scrollbar, time level and selection label and they trim down to 3.5 rows, but with that you lose functionality like the ability to toggle between Years, Quarters etc.:
For those reasons alone I can only see them being useful if your data spans whole years and you have plenty of room for these spreadsheet real estate hogs. Fingers crossed they’re better in Excel 2016.
For now let's stick with Slicers until Timelines are given some more intelligence and formatting flexibility.
Related Topics
- Inserting and using Slicers
- Sorting Excel Date Slicers
- Slicers for Excel Tables (Excel 2013 onwards)
Excel Worksheet Protection
moo
I am glad it is helpful
Lailah Grant
Excellent needed this quick refence
Mynda Treacy
Glad it will be helpful 🙂
Irene
Hello,
I took your advice and created a yyyy-mm Slicer but now that I have created a dashboard that spans in years beginning with 1/1/2020; what about in 2021 when I want to select 1.5 or 2 years of data, so you recommend also adding a “Year” Slicer in addition to the yyyy-mm so that the 2 years won’t need to be selected by dragging all of them? (Also, adding a quarters slicer). I don’t like repeating the year twice but not sure if I could just use the yyyy-mm for coming years.
Mynda Treacy
Hi Irene, I think if you add a year slicer as well as the month slicer it may get confusing for your users. Maybe a quarters slicer would be better if you have a lot of periods. Mynda
Irene
Ok, Thank you Mynda!
Alicia
Is there a way to set up timelines and slicers to automatically select certain dates or a specific time frame? I have a spreadsheet that I update monthly as each months’ financials close. The dates are coming from the calendar table within the spreadsheet’s data model.
On one sheet, I manually update the timeline to include the new month (with the “start date” always being January – the beginning of the year).
On another sheet, the timeline is for “the last 12 months”, so I manually update both the start and end month. For example, for the March spreadsheet, the time frame is April 2019-March 2020, and for the April spreadsheet, the time frame is March 2019-April 2020.
On the last sheet, I have a slicer for the dates instead of a timeline, and I am simply selecting the new month only (a single selection, no time frame).
I still want for the end users to be able to select different months/time frames if they need to. I would just love if the timelines/slicers could automatically default according to the above-stated parameters upon refresh. Is there any way to automate this sort of thing via measures or VBA?
Thank you!
Mynda Treacy
Hi Alicia,
You’d need to use VBA to automatically select the periods in the Slicers/Filters because Slicer selections aren’t removed upon refresh.
Mynda
CYang
Hi Mynda,
Is there a way, once a timeline slicer is inserted, to select a time frame but also show a grand total in the same pivot table? For example, I have a spreadsheet that houses all information for 2018. I’d like to use the timeline slicer to view only November, let’s say, but I’d still like to also see the total for 2018 so that I can quickly compare. Thanks!
Mynda Treacy
Hi, the only way to do this is with a custom measure in Power Pivot. It’s not something you can do in a regular PivotTable. The alternative is to build a separate unfiltered PivotTable beside the filtered one, and hide the columns that you don’t need so it appears as one report. Be careful the row labels are always aligned though.
Mynda
nezar
I use pivot tables with “normal” slicers and with timeline slicers.
If you add two normal data slicers they interact with each other in that if you make a selection in one slicer it filters the possible attributes in the second slicer.
If you use a timeline slicer and select a given period, this does not filter the normal slicers to show only valid attributes for that period and vice versa.
I want to be able to select a time period and for the slicers to only show attributes which have a value in that period.
Mynda Treacy
Hi Nezar,
This is a known bug. Hopefully one day they’ll fix it so filters in the timelines appear in the Slicers and vice versa.
Mynda
Carlos
Mynda,
I have no idea how your added column became a part of the pivot table. Can you please explain step by step how to do that? When I added another column, the data entered does NOT become a part of the pivot table and therefore, I cannot add it to my slicer.
Mynda Treacy
Hi Carlos,
I used an Excel Table for my PivotTable source data so that when I add a new column it is automatically picked up by the PivotTable upon refresh. If you aren’t using Excel Tables as the source of your PivotTable data then you need to edit the range the PivotTable is referencing so that it includes the new column.
To edit the range, click on the PivotTable > PivotTable Analyze tab > Change Data source.
Mynda
Carlos
Thanks Mynda,
I soon realized the Excel Table idea after watching a few more videos. I have often wondered how to add a calculated column to a Pivot Table and thought that’s what you did. I found YouTube videos on that too but it has limitations and did not solve my date format issue. I thought it would be just as easy to update my data range and realize that’s also your suggestion. Typically, I have all of my data in a “Data” worksheet and create my Pivot Tables from there. I guess I just had a brain fart yesterday. LOL
Thanks for your help and your excellent video!
Mynda Treacy
Glad you got it figured out 🙂
montoyesco
Hi. I have a question?
In the timeline slicer, how to select two different months that are not consecutive?
Mynda Treacy
You can’t select non-contiguous months in a timeline Slicer. Only in regular Slicers.
Mynda
indzara
Very detailed. Thank you.
I used to use the TEXT function for such things and I received feedback from some users about some errors. When I looked into it, it appeared that the TEXT function used to convert to date related items (weekday, etc.) didn’t work in some languages. For example, in Chile and Brazil, the language setting didn’t allow computation of the TEXT function the way it works in English. I have tried to work around it in some cases. However, for slicers I am still looking.
If you have any suggestions other than TEXT function without VBA, please let me know. Thanks.
Mynda Treacy
Hi Indzara,
I’m sorry I don’t know the solution to making your TEXT formulas compatible in all languages.
Mynda
Joshua Louk
The explanation on how to use data splicers for combining a date into month and year was very helpful and to the point. Exactly what I was looking for. Showed understanding of excel above that of most users posting on YouTube.
Mynda Treacy
Thanks, Joshua. Glad you found it useful.
Mynda
Mark Hallett
Nicely done! When will I learn to check here first when I am not sure. I spent too many hours pulling my hair out trying to figure this out. In 5 minutes watching the video, I was jumping for joy. Thanks, Mynda!
Mynda Treacy
Glad we could help, Mark 🙂
Roger Govier
Hi Mynda
Another great post.
Oh how I agree with you about how Microsoft have not quite got Timelines absolutely right – yet!!
And date groupings can be a pain with the extraneous items you get, but I am sure that these things will be addressed in time.
In the meanwhile, there is a little workaround to get your Text Months in correct order, provided you can accept a little VBA.
I added two columns to your source data
Column F called Month, and column G called Year.
Year has the simple formula =YEAR([@Date])
Column F, has the formula
=TEXT(MONTH([@Date]),”00″)&” “&TEXT([@Date],”mmm”)
This produces the rather ugly format of 04 Apr, 05 May etc.
Now comes the trickery, with a little bit of VBA, you can hide the first 2 characters, use Month as your Slicer and you just see Apr, May etc., but in the correct chronological order.
The following code, applies the formula to the column, then formats the cells so that the month number doesn’t show,
Sub FormatData()
Dim cell As Range
Range(“F2”).Select
Range(“F2”, Selection.End(xlDown)).Select
Selection.Formula = “=TEXT(MONTH([@Date]),””00″”)&”” “”&TEXT([@Date],””mmm””)”
For Each cell In Selection
cell.Value = Format(Left(cell.Value, 2), “;;;”) & Mid(cell.Value, 4, 4)
Next
End Sub
Now, adding the slicer for Year (and I chose a vertical layout), and then Month, the two slicers take up less “real Estate” than your solution, and with a single click you can select one year or the other. without having to select a range of Periods.
Not a solution for those whose companies do not allow the use of VBA, but for some, a possible workaround until Microsoft give us “all the goodies” we want.
Kind regards
Roger
Mynda Treacy
Fantastic! Thanks, Roger 🙂
Donwload Roger’s .xlsm file here.
Barry Mitchell
Hi greatly appreciate all your learning tips. There is one top I would totally would like to know.
Let’s say I have pivot data on one worksheet, and now I add another pivot data on the same worksheet from different source worksheet. Can one slicer control both
Mynda Treacy
Hi Barry,
Thanks for your kind words.
Slicers can only control PivotTable that share the same PivotCache. So, if your PivotTables have different data sources this will never happen. However, if you have Power Pivot you can connect the two sources and then use a single Slicer.
Mynda
Tina
Hello Mynda,
This a great post, thank you for sharing a solution to the ugly buttons! I was wondering if you can perhaps help with another problem I have with pivot tables. When I group dates into weeks (7 days) it shows the format as dd/mm/yyyy – dd/mm/yyyy . I tried to change the number format, cell format- nothing works! This makes the axis labels so long but I need to create a pivot charts by week. Any ideas?
Thank you in advance!
Kind regards,
Tina
Mynda Treacy
Thanks Tina, I’m glad you found it useful.
In regards to changing the formatting of the PivotTable grouped dates, the answer is you can’t 🙁
I also asked this and have reported it as an issue. The only thing you can do is create your own grouping in the source data and use that field instead of using the Group tool. Similarly to how I created the year/month Slicer.
Mynda
Tina
Thanks Mynda! That does indeed sound like a bug that Microsoft should fix. At least I know now that I didn’t miss anything. I will try the workaround you suggested 🙂
Tina