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
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.
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.
Related Topics
- Inserting and using Slicers
- Sorting Excel Date Slicers
- Slicers for Excel Tables (Excel 2013 onwards)
montoyesco says
Hi. I have a question?
In the timeline slicer, how to select two different months that are not consecutive?
Mynda Treacy says
You can’t select non-contiguous months in a timeline Slicer. Only in regular Slicers.
Mynda
indzara says
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 says
Hi Indzara,
I’m sorry I don’t know the solution to making your TEXT formulas compatible in all languages.
Mynda
Joshua Louk says
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 says
Thanks, Joshua. Glad you found it useful.
Mynda
Mark Hallett says
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 says
Glad we could help, Mark 🙂
Roger Govier says
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 says
Fantastic! Thanks, Roger 🙂
Donwload Roger’s .xlsm file here.
Barry Mitchell says
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 says
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 says
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 says
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 says
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