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